Friday, January 22, 2021

Tablespace details 

-- list all tablespaces with their associated files, the 
-- tablespace's allocated space, free space, and the 
-- next free extent:
 
clear breaks
SET linesize 130
SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
 
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;
 
 
 
-- list datafiles, tablespace names, and size in MB:
 
col file_name format a50
col tablespace_name format a10
 
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
 
 
 
-- list tablespaces, size, free space, and percent free
-- query originally developed by Michael Lehmann 
 
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
 
 
- No comments:

Helpers




Database Scripts Library Index [ID 131704.1]
Modified 03-NOV-2011     Type REFERENCE     Status ARCHIVED

Database Scripts Last updated on October 23, 2009
This document has been archived and will no longer be updated.
AdvancedQueueing ContentMgmt.Spatial ContentMgmt.Text ContentMgmt.UltraSearch
ContentMgmt.XDB ContentMgmt.interMedia DBA.Admin DBA.Architecture
DBA.DBCreationAndConfiguration DBA.Monitoring DBA.SQL DBA.Storage
DBWarehouse.MaterialView DBWarehouse.ParallelExecution DBWarehouse.Partitioning Distributed.General
Distributed.Replication Distributed.Streams Globalization Globalization.DST
HighAvailability.BR HighAvailability.Corruption HighAvailability.DataGuard HighAvailability.RMAN
Install.Installer Install.UnixGeneric JVM Manageability.MemoryMgmt
Manageability.Utilities.ExportImport Manageability.Utilities.SqlLoader OLAP.ExpressServer Performance.Database
Performance.Locking Performance.SqlTuning Scalability.OPS Scalability.RAC
Security.DBSecurity Storage.Exadata
AdvancedQueueing - Advanced Queuing
[Top]
ContentMgmt.Spatial - Oracle Intermedia Spatial and Spatial Data Option
[Top]
ContentMgmt.Text - Oracle Text (formerly interMedia Text)
[Top]
ContentMgmt.UltraSearch - Oracle Ultra Search
[Top]
ContentMgmt.XDB - XML Database
[Top]
ContentMgmt.interMedia - Oracle interMedia (Image, Video, Sound etc)
[Top]
DBA.Admin - General DBA Activities (Copying DB etc...)
[Top]
DBA.Architecture - Oracle Server Architecture (processes and SGA)
[Top]
DBA.DBCreationAndConfiguration - Database Create and Configuration
[Top]
DBA.Monitoring - Database Monitoring
[Top]
DBA.SQL - SQL Scripts, Examples and Reference Information
[Top]
DBA.Storage - Space Management and Object Storage
[Top]
DBWarehouse.MaterialView - Materialized Views - Distributed & Local Summary
[Top]
DBWarehouse.ParallelExecution - Parallel Execution
[Top]
DBWarehouse.Partitioning - Partitioning
[Top]
Distributed.General - Distributed Database Issues
[Top]
Distributed.Replication - Master Replication
- No comments:

Thursday, March 28, 2019

DLL of a user in oracle 

-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/script_creation/user_ddl.sql
-- Author       : Tim Hall
-- Description  : Displays the DDL for a specific user.
-- Call Syntax  : @user_ddl (username)
-- Last Modified: 07/08/2018
-- -----------------------------------------------------------------------------------

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
 
variable v_username VARCHAR2(30);

exec :v_username := upper('&1');

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from   dba_users u
where  u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from   dba_ts_quotas tq
where  tq.username = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp
where  sp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rp.default_role = 'YES'
and    rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
/

set linesize 80 pagesize 14 feedback on trimspool on verify on
- No comments:
Subscribe to: Posts (Atom)

How to Trouble shoot Logfile_sync wait event

   First  Identify and break down LGWR wait events. Query wait events for LGWR. In this instance LGWR sid is 3 (and usually it is). select s...