| Table of Contents |
This chapter is a collection of useful SQL scripts that can be run from Oracle’s SQL*Plus to interrogate your Oracle database.
alllocks.sql
This script interrogates all of the dba_lock tables to quickly identify locking contention.
rem alllocks.sql - shows all locks in the database. rem written by Don Burleson set linesize 132 set pagesize 60 spool /tmp/alllocks column owner format a10; column name format a15; column mode_held format a10; column mode_requested format a10; column type format a15; column lock_id1 format a10; column lock_id2 format a10; prompt Note that $ORACLE_HOME/rdbma/admin/catblock.sql prompt must be run before this script functions . . . prompt Querying dba_waiters . . . select waiting_session, holding_session, lock_type, mode_held, mode_requested, lock_id1, lock_id2 from sys.dba_waiters; prompt Querying dba_blockers . . . select holding_session from sys.dba_blockers; prompt Querying dba_dml_locks . . . select session_id, owner, name, mode_held, mode_requested from sys.dba_dml_locks; prompt Querying dba_ddl_locks . . . select session_id, owner, name, type, mode_held, mode_requested from sys.dba_ddl_locks; prompt Querying dba_locks . . . select session_id, lock_type, mode_held, mode_requested, lock_id1, lock_id2 from sys.dba_locks;
Here is the output from alllocks.sql:
SQL> @alllocks
Note that $ORACLE_HOME/rdbma/admin/catblock.sql
must be run before this script functions . . .
Querying dba_waiters . . .
no rows selected
Querying dba_blockers . . .
no rows selected
Querying dba_dml_locks . . .
SESSION_ID OWNER NAME MODE_HELD MODE_REQUE
----------- ------ -------------- -------------- ----------
19 RPT RPT_EXCEPTIONS Row-X (SX) None
Querying dba_ddl_locks . . .
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUE
---------- ----- --------------- --------------- --------- ----------
13 RPT SHP_PRE_INS_UPD Table/Procedure Null None
_PROC
13 SYS STANDARD Body Null None
14 SYS STANDARD Body Null None
13 SYS DBMS_STANDARD Table/Procedure Null None
14 SYS DBMS_STANDARD Table/Procedure Null None
13 SYS DBMS_STANDARD Body Null None
14 SYS DBMS_STANDARD Body Null None
13 SYS STANDARD Table/Procedure Null None
14 SYS STANDARD Table/Procedure Null None
9 rows selected.
Querying dba_locks . . .
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
---------- --------------- ---------- ---------- ---------- ----------
2 Media Recovery Share None 32 0
2 Media Recovery Share None 31 0
2 Media Recovery Share None 30 0
2 Media Recovery Share None 29 0
2 Media Recovery Share None 28 0
2 Media Recovery Share None 27 0
2 Media Recovery Share None 26 0
2 Media Recovery Share None 25 0
2 Media Recovery Share None 24 0
2 Media Recovery Share None 23 0
2 Media Recovery Share None 22 0
2 Media Recovery Share None 21 0
2 Media Recovery Share None 20 0
2 Media Recovery Share None 19 0
2 Media Recovery Share None 18 0
2 Media Recovery Share None 17 0
2 Media Recovery Share None 16 0
2 Media Recovery Share None 15 0
2 Media Recovery Share None 14 0
2 Media Recovery Share None 13 0
2 Media Recovery Share None 12 0
2 Media Recovery Share None 11 0
2 Media Recovery Share None 10 0
2 Media Recovery Share None 9 0
2 Media Recovery Share None 8 0
2 Media Recovery Share None 7 0
2 Media Recovery Share None 6 0
2 Media Recovery Share None 5 0
2 Media Recovery Share None 4 0
2 Media Recovery Share None 3 0
2 Media Recovery Share None 2 0
2 Media Recovery Share None 1 0
3 Redo Thread Exclusive None 1 0
14 PS Null None 0 0
14 PS Null None 0 1
19 DML Row-X (SX) None 1457 0
36 rows selected.
audit.sql
This script, audit.sql, is used to check an Oracle instance for unauthorized role privileges. In many shops, this type of script is run on a weekly basis to ensure that nobody has unauthorized privileges.
rem audit.sql a script to check for bad privileges
set linesize 80;
set pagesize 999;
set verify off;
set heading off;
PROMPT *********************************************************************
select 'AUDIT OF &&1 ', to_char(sysdate,'mm-dd-yy hh:mm') from dual;
PROMPT *********************************************************************
PROMPT
set heading on;
PROMPT *********************************************************************
PROMPT Searching &&1 for any system privileges
PROMPT that are granted WITH ADMIN OPTION...
PROMPT *********************************************************************
column c1 heading "Grantee";
column c2 heading "Privilege";
column c3 heading "Admin?";
select substr(grantee,1,20) c1,
substr(privilege,1,30) c2,
substr(admin_option,1,5) c3
from sys.dba_sys_privs
where admin_option = 'YES'
and grantee not in ('DB_USER','DB_ADMIN','DBA','OPS$ORACLE','SYSTEM','SYS')
order by grantee;
PROMPT *********************************************************************
PROMPT Searching &&1 for any end-users with system privileges...
PROMPT *********************************************************************
column c1 heading "Grantee";
column c2 heading "Privilege";
column c3 heading "Admin?";
select substr(grantee,1,20) c1,
substr(privilege,1,30) c2,
substr(admin_option,1,5) c3
from sys.dba_sys_privs
where
grantee not in ('DBA','SYSTEM','SYS','COMMON','OPS$ORACLE',
'RESOURCE','CONNECT','OPS$TPSOPR','IMP_FULL_DATABASE',
'DB_USER','DB_ADMIN','DB_OWNER','EXP_FULL_DATABASE',
'OPS$ADMOPR')
order by grantee;
PROMPT *********************************************************************
PROMPT Searching &&1 for any non-DBA roles
PROMPT that are granted WITH ADMIN OPTION...
PROMPT *********************************************************************
column c1 heading "Role";
column c2 heading "Privilege";
column c3 heading "Admin?";
select substr(role,1,20) c1,
substr(privilege,1,30) c2,
substr(admin_option,1,5) c3
from sys.role_sys_privs
where admin_option = 'YES'
and role not in ('DBA','DB_ADMIN')
order by role;
select substr(grantee,1,20) c1,
substr(granted_role,1,22) c2,
substr(admin_option,1,3) c3,
substr(default_role,1,12) c4
from sys.dba_role_privs
where admin_option = 'YES'
and granted_role not in ('RESOURCE','DB_USER','DB_OWNER','CONNECT')
and grantee not in
('DB_ADMIN','DBA','SYS','SYSTEM','ORACLE','OPS$ORACLE','OPS$ADMOPR')
order by granted_role;
PROMPT *********************************************************************
PROMPT Searching &&1 for any table privileges
PROMPT that can be granted to others...
PROMPT *********************************************************************
column c1 heading "Grantee";
column c2 heading "Owner";
column c3 heading "Table";
column c4 heading "Grantor";
column c5 heading "Privilege";
column c6 heading "Grantable?";
select substr(grantee,1,12) c1,
substr(owner,1,12) c2,
substr(table_name,1,15) c3,
substr(grantor,1,12) c4,
substr(privilege,1,9) c5,
substr(grantable,1,3) c6
from sys.dba_tab_privs
where grantable = 'YES'
and owner not in ('SYS','SYSTEM','GL','APPLSYS','BOM','ENG',
'PO','AP','PER','WIP','LOGGER')
order by table_name;
PROMPT *********************************************************************
PROMPT Searching &&1 for DBA and RESOURCE Roles (To other than ops$oracle)...
PROMPT *********************************************************************
column c1 heading "Grantee";
column c2 heading "Role";
column c3 heading "Admin?";
column c4 heading "Default Role";
select substr(grantee,1,20) c1,
substr(granted_role,1,22) c2,
substr(admin_option,1,3) c3,
substr(default_role,1,12) c4
from sys.dba_role_privs
where granted_role in ('RESOURCE','DB_OWNER','DBA','DB_ADMIN')
and grantee not in ('SYS','SYSTEM','OPS$ORACLE')
order by granted_role;
--select to_char(sysdate,'hhmmss') from dual;
create table temp1 as
select distinct username from dba_users
where substr(username,1,4) = 'OPS$';
create table temp2 as
select distinct grantee from dba_role_privs
where granted_role not in ('DB_USER');
PROMPT *********************************************************************
PROMPT Searching &&1 for any users
PROMPT that have no meaningful roles (orphan users)...
PROMPT *********************************************************************
select substr(username,5,20) username from temp1
where username not in
(select grantee from temp2);
drop table temp1;
drop table temp2;
PROMPT *********************************************************************
PROMPT Searching &&1 for all tables granted to PUBLIC . . .
PROMPT *********************************************************************
column c0 format a10 heading "Owner";
column c1 heading "Table";
column c2 format a10 heading "Grantor";
select distinct owner c0,
table_name c1,
grantor c2
from sys.dba_tab_privs a
where grantee = 'PUBLIC'
and
owner not in ('OPS$ORACLE','SYS','SYSTEM')
and not exists
(select * from dba_sequences b
where a.table_name = b.sequence_name)
and privilege not in ('EXECUTE')
order by owner, table_name
;
PROMPT *********************************************************************
PROMPT Searching &&1 for all objects owned by ops$ users . . .
PROMPT *********************************************************************
select substr(owner,1,10),
substr(object_type,1,20),
substr(object_name,1,40)
from dba_objects
where owner like 'OPS$%'
and owner not in ('OPS$ORACLE')
and object_type not in ('SYNONYM')
;
spool off;
Here is the listing from this script:
SQL> @audit my_sid ********************************************************************* AUDIT OF my_sid 03-08-96 08:03 ********************************************************************* ********************************************************************* Searching my_sid for any system privileges that are granted WITH ADMIN OPTION... ********************************************************************* no rows selected ********************************************************************* Searching my_sid for any end-users with system privileges... ********************************************************************* Grantee Privilege Adm -------------------- ------------------------------ -------------- ORACLE ALTER USER NO ORACLE CREATE USER NO ORACLE RESTRICTED SESSION NO ORACLEDEV CREATE DATABASE LINK NO ORACLEDEV CREATE PROCEDURE NO ORACLEDEV CREATE PUBLIC DATABASE LINK NO ORACLEDEV CREATE PUBLIC SYNONYM NO ORACLEDEV CREATE SEQUENCE NO ORACLEDEV CREATE TABLE NO ORACLEDEV CREATE TRIGGER NO ORACLEDEV CREATE VIEW NO ORACLEDEV DROP PUBLIC SYNONYM NO ORACLEDEV FORCE TRANSACTION NO ORACLEDEV SELECT ANY TABLE NO ORACLEUSR CREATE SESSION NO ORADBA UNLIMITED TABLESPACE NO PATROL CREATE PROCEDURE NO PATROL CREATE SEQUENCE NO PATROL CREATE SESSION NO PATROL CREATE SYNONYM NO PATROL CREATE TABLE NO PATROL CREATE VIEW NO PATROL SELECT ANY TABLE NO 23 rows selected. ********************************************************************* Searching my_sid for any non-DBA roles that are granted WITH ADMIN OPTION... ********************************************************************* no rows selected no rows selected ********************************************************************* Searching my_sid for any table privileges that can be granted to others... ********************************************************************* no rows selected ********************************************************************* Searching my_sid for DBA and RESOURCE Roles (To other than ops$oracle)... ********************************************************************* Grantee Role Adm Def -------------------- ---------------------- -- --- ORADBA DBA NO YES ORADBA RESOURCE NO YES Table created. Table created. ********************************************************************* Searching my_sid for any users that have no meaningful roles (orphan users)... ********************************************************************* no rows selected Table dropped. Table dropped. ********************************************************************* Searching my_sid for all tables granted to PUBLIC . . . ********************************************************************* Owner Table Grantor ---------- ------------------------------ ---------- ORACLE ERR4 ORACLE ORACLE ORGSEG ORACLE ORACLE PERSEG ORACLE ORACLE PLAN_TABLE ORACLE ********************************************************************* Searching my_sid for all objects owned by ops$ users . . . ********************************************************************* no rows selected ***********************************************************
allprivs.sql
This script accepts the user ID as a parameter, and then lists all of the privileges that the user possesses in Oracle. It includes all table, system, and role privileges.
rem allprivs.sql <userid> -Lists all table, system and role privs. (ops$
rem is NOT added)
set pause off;
--set echo off;
--set termout off;
set linesize 75;
set pagesize 999;
set newpage 0;
--set feedback off;
--set heading off;
set verify off;
select 'grant '||privilege||' on '||table_name||' to &&1;'
from dba_tab_privs
where grantee = upper('&&1')
and table_name not like 'MENU_%'
;
select 'grant '||privilege||' to &&1;'
from dba_sys_privs
where grantee = upper('&&1')
;
select 'grant '||granted_role||' to &&1;'
from dba_role_privs
where grantee = upper('&&1')
;
spool off;
Here is the listing from this script:
@allprivs oradba grant UNLIMITED TABLESPACE to oradba; grant CONNECT to oradba; grant DBA to oradba; grant ORACLEDEV to oradba; grant RESOURCE to oradba;
bitmap.sql
This script identifies low cardinality indexes for 7.3 bitmapped indexes.
rem Written by Don Burleson
prompt Be patient. This can take awhile . . .
set pause off;
set echo off;
set termout off;
set linesize 300;
set pagesize 999;
set newpage 0;
set feedback off;
set heading off;
set verify off;
rem First create the syntax to determine the cardinality . . .
spool idx1.sql;
select 'set termout off;' from dual;
select 'spool idx2.lst;' from dual;
select 'column card format 9,999,999;' from dual;
select 'select distinct count(distinct '
||a.column_name
||') card, '
||''' is the cardinality of '
||'Index '
||a.index_name
||' on column '
||a.column_name
||' of table '
||a.table_owner
||'.'
||a.table_name
||''' from '
||index_owner||'.'||a.table_name
||';'
from dba_ind_columns a, dba_indexes b
where
a.index_name = b.index_name
and
tablespace_name not in ('SYS','SYSTEM')
;
select 'spool off;' from dual;
spool off;
set termout on;
@idx1
!sort idx2.lst
Here is the listing from bitmap.sql:
3 is the cardinality of Index GEO_LOC_PK on column GEO_LOC_TY_CD of
table RPT.GEM_LCC
4 is the cardinality of Index REGION_IDX on column REHION of table
RPT.CUSTOMER
7 is the cardinality of Index GM_LCK on column GEO_LC_TCD of table
RPT.GEM_LCC
8 is the cardinality of Index USR_IDX on column USR_CD of table
RPT.CUSTOMER
50 is the cardinality of Index STATE_IDX on column STATE_ABBR of
table RPT.CUSTOMER
3117 is the cardinality of Index ZIP_IDX on column ZIP_CD of table
RPT.GEM_LCC
71,513 is the cardinality of Index GEO_LOC_PK on column GEO_LOC_CD of
table RPT.GEM_LCC
83,459 is the cardinality of Index GEO_KEY_PK on column GEO_LOC_TY_CD of
table RPT.GEM_LCC
coalesce.sql
This script modifies all tables that have (pctincrease = 0) to (pctincrease = 1).
rem coalesce.sql - changes all tablespaces with PCTINCREASE not equal to rem one.
rem written by Don Burleson
set linesize 132;
set pagesize 999;
set feedback off;
set verify off;
set heading off;
set termout off;
spool coalesce;
select
'alter tablespace '
||tablespace_name||
' storage ( pctincrease 1 );'
from dba_tablespaces
where
tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
and
pct_increase = 0;
spool off;
set feedback on;
set verify on;
set heading on;
set termout on;
@coalesce.lst
dbusers.sql
This is a large report that lists all users in the Oracle database. For each user, it lists all of the user’s privileges. This is a useful report to run weekly, dumping the output into a file where it can be easily searched.
remdbusers.sql - Lists all users, role privileges, default and temp
remtablespaces
set pause off;
set linesize 78;
set pagesize 56;
set newpage 0;
column c1 heading "User" format a20;
column c2 heading "Privilege";
column c3 heading "Default TS" format a10;
column c4 heading "Temp TS" format a10;
ttitle "dbname Database|User Profiles";
select substr(dba_users.username,1,20) c1,
dba_role_privs.granted_role c2,
substr(dba_users.default_tablespace,1,15) c3,
substr(dba_users.temporary_tablespace,1,15) c4
from sys.dba_role_privs, sys.dba_users
where sys.dba_role_privs.grantee = sys.dba_users.username
order by 1,2;
Here is the listing from this script:
SQL> @dbusers
SQL>
Fri Mar 8 page 1
dbname Database
User Profiles
User Privilege Default TS Temp TS
-------------------- ------------------------------ ---------- ----------
EMANS ORACLEUSR TEMP TEMP
GENERAL ORACLEUSR USERS TEMP
KRAMER ORACLEUSR TEMP TEMP
L114339 ORACLEUSR TEMP TEMP
L118358 ORACLEUSR TEMP TEMP
L119069 ORACLEDEV TEMP TEMP
L119069 ORACLEUSR TEMP TEMP
L122739 ORACLEUSR TEMP TEMP
L123577 CONNECT USERS TEMP
L123577 ORACLEUSR USERS TEMP
L125033 ORACLEUSR TEMP TEMP
L570974 ORACLEUSR TEMP TEMP
L575696 ORACLEUSR TEMP TEMP
L575706 ORACLEUSR TEMP TEMP
L579080 ORACLEUSR TEMP TEMP
L589886 ORACLEUSR TEMP TEMP
L609306 ORACLEUSR TEMP TEMP
dbroles.sql
This script produces a report of all users who have system privileges within Oracle. This script is a useful security check to detect anyone who may have unwanted privileges.
rem dbroles.sql - Lists all roles that are defined to the database
set heading off;
set pause off;
set echo on;
set termout on;
set linesize 75;
set showmode on;
set feedback on;
set newpage 1;
set verify off;
set pagesize 9999;
select distinct 'grant db_owner',
' to ',
substr(grantee,1,20),
';'
from dba_role_privs where
granted_role = 'RESOURCE';
select distinct 'grant unlimited tablespace',
' to ',
substr(grantee,1,20),
';'
from dba_role_privs where
granted_role = 'RESOURCE';
select distinct 'revoke resource',
' from ',
substr(grantee,1,20),
';'
from dba_role_privs where
granted_role = 'RESOURCE';
spool dbadmin.sql
select distinct 'grant db_admin',
' to ',
substr(grantee,1,20),
';'
from dba_role_privs where
granted_role = 'DBA';
select distinct 'revoke dba',
' from ',
substr(grantee,1,20),
';'
from dba_role_privs where
granted_role = 'DBA' and
grantee not in ('SYS','SYSTEM');
select distinct 'grant unlimited tablespace',
' to ',
substr(grantee,1,20),
';'
from dba_role_privs where
granted_role = 'DBA';
Here is the listing from this script:
@dbroles
Fri Mar 8 page 1
dbname Database
User Profiles
grant db_owner to ORADBA ;
grant db_owner to SYS ;
grant unlimited tablespace to ORADBA ;
grant unlimited tablespace to SYS ;
revoke resource from ORADBA ;
revoke resource from SYS ;
grant db_admin to ORADBA ;
grant db_admin to SYS ;
grant db_admin to SYSTEM ;
revoke dba from ORADBA ;
grant unlimited tablespace to ORADBA ;
grant unlimited tablespace to SYS ;
grant unlimited tablespace to SYSTEM ;
files.sql
This script produces a report of all files that are defined to Oracle. For each file the tablespace name is given along with the size of the data file.
rem files.sql - shows all datafiles by tablespace and size
set pause off;
set linesize 60;
set pagesize 60;
set newpage 0;
column c1 heading "Tablespace";
column c2 heading "File name";
column c3 heading "Size (MB)";
ttitle "dbname Database|Data files by Tablespace";
select substr(tablespace_name,1,10) c1,
substr(file_name,1,30) c2,
round(sum(bytes)/1048576,2) c3
from sys.dba_data_files
group by tablespace_name,file_name
order by tablespace_name;
Here is the listing from this script:
Fri Mar 8 page 1
dbname Database
Data files by Tablespace
Tablespace File name Size (MB)
---------- -------------------------------- ----------
INDX /Datadisks/d02/ORACLE/my_sid/ind 200
MPTV /Datadisks/d01/ORACLE/my_sid/mpt 500
PATROL /Datadisks/d02/ORACLE/my_sid/pat 10
ROLB /Datadisks/d03/ORACLE/my_sid/rol 40
SYSTEM /Datadisks/d09/ORACLE/my_sid/sys 15
TEMP /Datadisks/d02/ORACLE/my_sid/tem 80
TEMP /Datadisks/d02/ORACLE/my_sid/tem 180
TOOLS /Datadisks/d03/ORACLE/my_sid/too 15
USERS /Datadisks/d02/ORACLE/my_sid/use 10
getprivs.sql
This script accepts a user ID as input and produces a report showing all system and role privileges that are granted to this user.
rem getprivs.sql <user id> - Lists all privileges assigned to the
rem specified user
set pause off;
--set echo off;
--set termout off;
set linesize 75;
set pagesize 999;
set newpage 0;
set feedback off;
set heading off;
set verify off;
select 'grant '||privilege||' to '||grantee||';'
from dba_sys_privs
where grantee = upper('&&1')
;
select 'grant '||granted_role||' to '||grantee||';'
from dba_role_privs
where grantee = upper('&&1')
;
select
'grant '||privilege||' on '||owner||'.'||table_name
||' to '||grantee||';'
from dba_tab_privs
where grantee = upper('&&1')
order by owner
;
select
'grant '||privilege||' ('||column_name
||') on '||owner||'.'||table_name
||' to '||grantee||';'
from dba_col_privs
where grantee = upper('&&1')
;
Here is the listing from this script:
Fri Mar 8 page 1
dbname Database
Data files by Tablespace
grant UNLIMITED TABLESPACE to ORADBA;
grant CONNECT to ORADBA;
grant DBA to ORADBA;
grant ORACLEDEV to ORADBA;
grant RESOURCE to ORADBA;
getrole.sql
This report accepts a user ID and produces a report that shows all role privileges that have been granted to this user.
rem getrole.sql <user id> - Lists all roles assigned to the user
rem keyed script
set pause off;
--set echo off;
--set termout off;
set linesize 75;
set pagesize 999;
set newpage 0;
set feedback off;
set heading off;
set verify off;
select '&&1 has system privilege: '||privilege
from dba_sys_privs
where grantee = upper('&&1')
;
select '&&1 has role: '||granted_role
from dba_role_privs
where grantee = upper('&&1')
;
Here is the listing from this script:
SQL> @getrole oradba oradba has role: CONNECT oradba has role: DBA oradba has role: ORACLEDEV oradba has role: RESOURCE
index.sql
This report tells all relevant information about an index.
rem index.sql - Shows the details for indexes
set pagesize 999;
set linesize 100;
column c1 heading 'Index' format a19;
column c3 heading 'S' format a1;
column c4 heading 'Level' format 999;
column c5 heading 'Leaf Blks' format 999,999;
column c6 heading 'dist. Keys' format 99,999,999;
column c7 heading 'Bks/Key' format 99,999;
column c8 heading 'Clust Ftr' format 9,999,999;
column c9 heading 'Lf/Key' format 99,999;
spool index.lst;
select
owner||'.'||index_name c1,
substr(status,1,1) c3,
blevel c4,
leaf_blocks c5,
distinct_keys c6,
avg_data_blocks_per_key c7,
clustering_factor c8,
avg_leaf_blocks_per_key c9
from dba_indexes
where
owner not in ('SYS','SYSTEM')
order by blevel desc, leaf_blocks desc;
Here is the output of index.sql:
Leaf dist. Bks/ Clust Lf/
Index S Level Blks Keys Key Ftr Key
------------------ - ----- ------ --------- ------ --------- ------
RPT.LOB_SHPMT_PK V 2 25,816 3,511,938 1 455,343 1
RPT.SHP_EK_CUST_INV V 2 23,977 2,544,132 1 1,764,915 1
RPT.SHP_FK_GLO_DEST V 2 23,944 22,186 112 2,493,095 1
RPT.LSH_FK_SHP V 2 22,650 1,661,576 1 339,031 1
RPT.SHP_FK_ORL_ORIG V 2 21,449 404 806 325,675 53
RPT.LSA_FK_LSH V 2 21,181 2,347,812 1 996,641 1
RPT.LSH_FK_LOB V 2 19,989 187 4,796 896,870 106
RPT.SHPMT_PK V 2 19,716 3,098,063 1 1,674,264 1
RPT.SHP_FK_CAR V 2 18,513 689 390 268,859 26
RPT.SHP_EK_ROLE_TY_ V 2 17,847 10 24,613 246,134 1,784
RPT.SHP_FK_SPT V 2 16,442 4 46,872 187,489 4,110
RPT.INV_EK_INV_NUM V 2 16,407 2,014,268 1 518,206 1
RPT.SHP_FK_ORL_DEST V 2 15,863 385 692 266,656 41
RPT.SHP_FK_SRC V 2 15,827 10 17,469 174,694 1,582
RPT.INV_LINE_ITEM_P V 2 14,731 2,362,216 1 102,226 1
idxexts.sql
This report produces a report of all indexes that have more than five extents. This report is useful for determining which indexes may benefit from being dropped and re-created, since index fragmentation can impede performance.
rem idxexts.sql - Lists all indexes where extents are > 5
set pause off;
set echo off;
set linesize 150;
set pagesize 60;
column c1 heading "Tablespace";
column c2 heading "Owner";
column c3 heading "Index";
column c4 heading "Size (KB)";
column c5 heading "Alloc. Ext";
column c6 heading "Max Ext";
column c7 heading "Init Ext (KB)";
column c8 heading "Next Ext (KB)";
column c9 heading "Pct Inc";
column c10 heading "Pct Free";
break on c1 skip 2 on c2 skip 2
ttitle "dbname Database|Fragmented Indexes";
select substr(ds.tablespace_name,1,10) c1,
substr(di.owner||'.'||di.table_name,1,30) c2,
substr(di.index_name,1,20) c3,
ds.bytes/1024 c4,
ds.extents c5,
di.max_extents c6,
di.initial_extent/1024 c7,
di.next_extent/1024 c8,
di.pct_increase c9,
di.pct_free c10
from sys.dba_segments ds,
sys.dba_indexes di
where ds.tablespace_name = di.tablespace_name
and ds.owner = di.owner
and ds.segment_name = di.index_name
and ds.extents > 3
order by 1,2;
Here is the listing from this script:
SQL> @idxexts
Fri Mar 8 page 1
dbname Database
Fragmented Indexes
INDX ORACLE.DETSALE DETSALE_PK 9420 5 249 8616 200 0 10
DETSALE_UK 10600 22 249 6400 200 0 10
DETSALE_INDEX4 5100 4 249 4500 200 0 20
keep.sql
This is a very useful script for pinning packages in the SGA. Pinning packages in the SGA improves performance for frequently used packages. The syntax is spooled to the exec.sql file, where the script executes after the file has been created. You may want to review the contents of exec.sql, and only pin those packages that are frequently called by the application.
rem keep.sql - pins all packages in the SGA
SET PAGESIZE 999
SET HEAD OFF
SET FEEDBACK OFF
SPOOL keep_exec.sql
SELECT 'execute dbms_shared_pool.keep('''||name||''');' sql_stmt
FROM v$db_object_cache
WHERE type='PACKAGE'
/
SPOOL OFF
Here is the listing from this script:
@exec package kept package kept
linkv1.sql
This script produces a list of all public and private database links that are using SQL*Net version 1 connect strings. This is a useful report to run when you convert to SQL*Net version 2, and it can easily be modified to create the drop-and-create syntax for new version 2 database links.
rem linkv1.sql - find a SQL*Net v 1 database links
column c2 heading "Owner";
column c3 heading "User";
column c5 heading "Connect";
set pagesize 60;
ttitle "SQL*Net v 1|Installations"
select
substr(db_link,1,15) c1,
substr(owner,1,10) c2,
substr(username,1,19) c3,
substr(host,1,15) c5
from dba_db_links
where
host like 't:%'
order by db_link;
Here is the listing from this script:
@linkv1 DB LINK OWNER USER CONNECT -------- ------- --------- ----------- mdb_link ORADBA OPS$JONES t:mdb:mdb01 lis_link ORAPRM1 MFISHER t:lis:lis01
objects.sql
This script lists all trigger and stored procedure names in the database showing the creator of the object.
rem objects.sql - Lists all triggers and procedures in the database.
rem
set pause off;
set echo off;
set termout on;
set linesize 80;
set pagesize 999;
column object_name format a10;
column object_type format a12;
column object_name format a30;
break on owner skip 0;
ttitle "Objects in Database";
select owner,
object_type,
object_name
from dba_objects
where
--owner not in ('SYS','SYSTEM')
--and
object_type in ('PACKAGE','TRIGGER','PROCEDURE')
order by 1,2,3;
Here is the listing from this script:
SQL> @objects
Fri Mar 8 page 1
Objects in Database
ORACLE PROCEDURE MERGE_MF
PROCEDURE REMOVE_LINK
TRIGGER ORG_SEGMENT
PATROL PACKAGE P$COLL
PACKAGE P$EXCLUSION
PACKAGE P$MONITOR
PACKAGE P$PREF
PACKAGE P$RECOVERY
SYS PACKAGE DBMS_ALERT
PACKAGE DBMS_DDL
PACKAGE DBMS_DEFER_IMPORT_INTERNAL
PACKAGE DBMS_DEFER_SYS
paramete.sql
This script shows all of the Oracle initialization parameters as defined in the init.ora and config.ora files. Identical to the SQL*DBA SHOW PARAMETERS command, this report is useful because it does not require access to SQL*DBA.
rem paramete.sql - Lists values of the v$parameter table.
set pagesize 999;
column c1 heading "Name";
column c3 heading "Value"
column c4 heading "Default?"
select substr(name,1,35) c1,
substr(value,1,30) c3,
isdefault c4
from v$parameter
order by substr(name,1,20)
;
Here is the listing from this script:
SQL> @paramete
Fri Mar 8 page 1
Objects in Database
Name Value Default?
----------------------------------- ---------------------------- --------
async_read TRUE TRUE
async_write TRUE TRUE
audit_file_dest /opt/oracle/admin/my_sid/audit FALSE
audit_trail TRUE FALSE
background_core_dump full TRUE
background_dump_dest /opt/oracle/admin/my_sid/bdump FALSE
blank_trimming FALSE TRUE
cache_size_threshold 40 FALSE
ccf_io_size 134217728 TRUE
checkpoint_process FALSE TRUE
cleanup_rollback_entries 20 TRUE
close_cached_open_cursors FALSE TRUE
commit_point_strength 1 TRUE
compatible TRUE
compatible_no_recovery TRUE
control_files /Datadisks/d09/ORACLE/my_sid/con FALSE
core_dump_dest /opt/oracle/admin/my_sid/cdump FALSE
cursor_space_for_time FALSE TRUE
db_block_buffers 400 FALSE
db_block_checkpoint_batch 8 TRUE
db_block_lru_extended_statistics 0 TRUE
db_block_lru_statistics FALSE TRUE
db_block_size 4096 FALSE
db_domain WORLD TRUE
db_file_multiblock_read_count 16 FALSE
db_file_simultaneous_writes 4 TRUE
db_files 30 TRUE
db_name my_sid FALSE
db_writers 1 TRUE
dblink_encrypt_login FALSE TRUE
discrete_transactions_enabled FALSE TRUE
distributed_lock_timeout 60 TRUE
distributed_recovery_connection_hol 200 TRUE
distributed_transactions 19 FALSE
dml_locks 100 FALSE
enqueue_resources 177 FALSE
event TRUE
fixed_date TRUE
gc_db_locks 400 FALSE
gc_files_to_locks TRUE
gc_lck_procs 1 TRUE
gc_rollback_locks 20 TRUE
gc_rollback_segments 20 TRUE
gc_save_rollback_locks 20 TRUE
gc_segments 10 TRUE
gc_tablespaces 5 TRUE
global_names FALSE TRUE
ifile /opt/oracle/admin/my_sid/pfile/c FALSE
instance_number 0 TRUE
job_queue_interval 60 TRUE
job_queue_keep_connections FALSE TRUE
job_queue_processes 0 TRUE
license_max_sessions 0 TRUE
license_max_users 0 TRUE
license_sessions_warning 0 TRUE
log_archive_buffer_size 64 TRUE
log_archive_buffers 4 TRUE
log_archive_dest /opt/oracle/admin/my_sid/arch/mp FALSE
log_archive_format %t_%s.dbf TRUE
log_archive_start TRUE FALSE
log_buffer 32768 TRUE
log_checkpoint_interval 10000 FALSE
log_checkpoint_timeout 0 TRUE
log_checkpoints_to_alert FALSE TRUE
log_files 255 TRUE
log_simultaneous_copies 2 FALSE
log_small_entry_max_size 800 TRUE
max_commit_propagation_delay 90000 TRUE
max_dump_file_size 500 TRUE
max_enabled_roles 20 TRUE
max_rollback_segments 30 TRUE
max_transaction_branches 8 TRUE
mts_dispatchers TRUE
mts_listener_address (address=(protocol=ipc)(key=%s TRUE
mts_max_dispatchers 0 FALSE
mts_max_servers 0 FALSE
mts_servers 0 FALSE
mts_service my_sid FALSE
nls_currency TRUE
nls_date_format TRUE
nls_date_language TRUE
nls_iso_currency TRUE
nls_language AMERICAN TRUE
nls_numeric_characters TRUE
nls_sort TRUE
nls_territory AMERICA TRUE
open_cursors 100 FALSE
open_links 4 TRUE
optimizer_comp_weight 0 TRUE
optimizer_mode CHOOSE FALSE
os_authent_prefix TRUE
os_roles FALSE TRUE
parallel_default_max_scans 0 TRUE
parallel_default_max_instances 0 TRUE
parallel_default_scansize 100 TRUE
parallel_max_servers 5 TRUE
parallel_min_servers 0 TRUE
parallel_server_idle_time 5 TRUE
post_wait_device /devices/pseudo/pw@0:pw TRUE
pre_page_sga FALSE TRUE
processes 60 FALSE
recovery_parallelism 0 TRUE
reduce_alarm FALSE TRUE
remote_login_passwordfile NONE FALSE
remote_os_authent FALSE TRUE
remote_os_roles FALSE TRUE
resource_limit FALSE TRUE
rollback_segments rolb1, rolb2, rolb3, rolb4 FALSE
row_cache_cursors 10 TRUE
row_locking default TRUE
sequence_cache_entries 10 TRUE
sequence_cache_hash_buckets 7 TRUE
serializable FALSE TRUE
session_cached_cursors 0 TRUE
sessions 71 FALSE
shadow_core_dump full TRUE
shared_pool_reserved_size 0 TRUE
shared_pool_reserved_min_alloc 5000 TRUE
shared_pool_size 6000000 FALSE
single_process FALSE TRUE
snapshot_refresh_interval 60 TRUE
snapshot_refresh_keep_connections FALSE TRUE
snapshot_refresh_processes 0 TRUE
sort_area_retained_size 65536 FALSE
sort_area_size 65536 TRUE
sort_mts_buffer_for_fetch_size 0 TRUE
sort_read_fac 5 TRUE
sort_spacemap_size 512 TRUE
spin_count 2000 TRUE
sql92_security FALSE TRUE
sql_trace FALSE TRUE
temporary_table_locks 71 FALSE
thread 0 TRUE
timed_statistics FALSE TRUE
transactions 78 FALSE
transactions_per_rollback_segment 34 FALSE
use_ism TRUE TRUE
use_post_wait_driver FALSE TRUE
use_readv FALSE TRUE
user_dump_dest /opt/oracle/admin/my_sid/udump FALSE
140 rows selected.
profile.sql
This script shows all sign-on profiles that have been defined to Oracle. For each profile, the resource and the resource limit is shown.
rem profile.sql - Lists all profiles in the database.
rem sqlx script
set echo off;
set pagesize 60;
ttitle "dbname Database|Profiles";
break on c0 skip 2;
column c0 heading "Profile";
column c1 heading "Resource";
column c2 heading "Limit";
select
substr(profile,1,10) c0,
substr(resource_name,1,30) c1,
substr(limit,1,10) c2
from dba_profiles
group by
substr(profile,1,10),
substr(resource_name,1,30),
substr(limit,1,10)
order by 1;
Here is the listing from this script:
SQL> @profile
Fri Mar 8 page 1
dbname Database
Profiles
Profile Resource Limit
------- ------------------------- ---------
DEFAULT COMPOSITE_LIMIT UNLIMITED
CONNECT_TIME UNLIMITED
CPU_PER_CALL UNLIMITED
CPU_PER_SESSION UNLIMITED
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
PRIVATE_SGA UNLIMITED
SESSIONS_PER_USER UNLIMITED
ADHOC COMPOSITE_LIMIT DEFAULT
CONNECT_TIME 4800
CPU_PER_CALL UNLIMITED
CPU_PER_SESSION UNLIMITED
IDLE_TIME 120
LOGICAL_READS_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
PRIVATE_SGA 51200
SESSIONS_PER_USER 5
quotas.sql
This script lists all quotas defined to Oracle. A quota is assigned on a tablespace-by-tablespace basis to each user, and is used to prevent a single user from consuming an entire shared tablespace. For example, user FRED could be prevented from using more then 500 K in the TEMP tablespace with the command: ALTER USER FRED QUOTA 500K ON TEMP.
rem quotas.sql - Lists all quotas.
set pause off;
set echo off;
set linesize 79;
set pagesize 56;
set newpage 0;
column c1 heading "Table|Space";
column c2 heading "User Name";
column c3 heading "Bytes|Used (KB)";
column c4 heading "Bytes|Allowed (KB)";
ttitle "dbname Database|Quotas";
select substr(tablespace_name,1,10) c1,
substr(username,1,15) c2,
(bytes/1024) c3,
(max_bytes/1024) c4
from sys.dba_ts_quotas
order by tablespace_name, username;
Here is the listing from this script:
SQL> @quotas
Fri Mar 8 page 1
dbname Database
Quotas
Table Bytes Bytes
Space User Name Used (KB) Allowed (KB)
--------- ----------- ---------- ------------
INDX ORACLE 115976 -.00097656
MPTV ORACLE 259688 -.00097656
TEMP PATROL 0 -.00097656
USERS ORACLE 0 -.00097656
USERS PATROL 1092 -.00097656
rbsegs.sql
This script shows all of Oracle’s rollback segments and their status. This report can be very useful for determining if a long-running SQL statement is using all rollback segment resources. This can result in the SNAPSHOT TOO OLD or ROLLBACK SEGMENT FAILED TO EXTEND Oracle error messages.
rem rbsegs.sql - shows size and status of rollback segments
select substr(segment_name,1,10) RBS,
substr(tablespace_name,1,10) tablespace,
bytes/1048576 Meg,
extents
from dba_segments where segment_type = 'ROLLBACK';
select 'offline rollback segments' from dual;
select segment_name, status
from dba_rollback_segs
where
segment_name not like ('SYS%');
Here is the listing from this script:
SQL> @rbsegs RBS TABLESPACE MEG EXTENTS ---------- ---------- ---------- ---------- SYSTEM SYSTEM .17578125 3 ROLB1 ROLB 7.32421875 3 ROLB2 ROLB 7.32421875 3 ROLB3 ROLB 7.32421875 3 ROLB4 ROLB 3.046875 3 'OFFLINEROLLBACKSEGMENTS' ------------------------ offline rollback segments SEGMENT_NAME STATUS ------------------------------ ---------------- ROLB1 ONLINE ROLB2 ONLINE ROLB3 ONLINE ROLB4 ONLINE
rbslock.sql
This is a very useful script for seeing who is assigned to a rollback segment. If a rollback segment is extending dramatically, the DBA will want to find out which user is running the task, and this script identifies the user.
remrbslock.sql - shows all users assigned to a rollback segment.
set pause off;
set echo off;
set termout off;
set linesize 45;
set pagesize 60;
set newpage 0;
column c1 heading "RBS" format a10
column c2 heading "User" format a15;
ttitle "dbname Database|Rollback Segment Access";
select r.name c1,
username c2
from v$transaction t,
v$rollname r,
v$session s
where t.addr = s.taddr
and t.xidusn = r.usn
order by 1,2;
Here is the listing from this script:
@rbslock RBS User ---- ------------------ RB01 DMARTIN RB02 JFISHER
role.sql
This script accepts a role name and produces a report that shows all privileges that comprise the role, and all users who have been granted the roles.
rem role.sql <role_name> - Shows all privs with the role and all rem users w role.
set pause off;
set echo off;
set termout on;
set linesize 80;
set pagesize 999;
column c1 heading "Role";
column c2 format a40 heading "Table Name";
column c3 heading "Priv.";
break on c1 skip 0 on c2 skip 0 on c3 skip 0
ttitle "Role &&1 in Database dbname";
select substr(granted_role,1,29) c1
from sys.role_role_privs
where role = (upper('&&1'))
;
select substr(role,1,30) c1,
ltrim(rtrim(owner))||'.'||substr(table_name,1,29) c2,
substr(privilege,1,6) c3
from sys.role_tab_privs
where role = (upper('&&1'))
order by 1,2,3;
column c1 heading "Role";
column c2 heading "Grantee";
ttitle "Users with &&1 role";
select substr(granted_role,1,10) c1,
substr(grantee,1,20) c2
from dba_role_privs
where granted_role = (upper('&&1'))
order by 1,2;
Here is the listing from this script:
SQL > @role dba
Fri Mar 8 page 1
Role dba in Database dbname
Role
----------
EXP_FULL_DATABASE
IMP_FULL_DATABASE
Users with dba role
Role Grantee
---------- ----------
DBA ORADBA
SYS
SYSTEM
roles.sql
This report shows all roles within Oracle. It also shows all system and table privileges that comprise each role, and finally all users who have been granted the role. Be forewarned that this can be a very large report if the Oracle instance has many roles.
rem roles.sql - Shows all roles within database and all users (huge report)
set pause off;
set echo off;
set linesize 80;
set pagesize 60;
column c1 heading "Role";
column c2 format a40 heading "Table Name";
column c3 heading "Priv.";
break on c1 skip 0 on c2 skip 0
ttitle "Roles in Database dbname";
select substr(role,1,30) c1,
substr(granted_role,1,30)
from sys.role_role_privs;
ttitle "Roles in Database dbname";
select substr(role,1,30) c1,
ltrim(rtrim(owner))||'.'||substr(table_name,1,30) c2,
substr(privilege,1,6) c3
from sys.role_tab_privs
where owner not in ('SYS','SYSTEM')
order by 1,2,3;
column c1 heading "Role";
column c2 heading "Grantee";
ttitle "Roles Granted to Users in Database dbname";
select substr(granted_role,1,30) c1,
substr(grantee,1,20) c2
from dba_role_privs
where granted_role not in ('DBA','RESOURCE','CONNECT','SYS','SYSTEM',
'EXP_FULL_DATABASE','IMP_FULL_DATABASE','MONITORER')
order by 1,2;
Here is the listing from this script:
SQL> @roles
Fri Mar 8 page 1
Roles in Database
Role SUBSTR(GRANTED_ROLE,1,30)
---------- ------------------------------
DBA EXP_FULL_DATABASE
IMP_FULL_DATABASE
2 rows selected.
Fri Mar 8 page 1
Roles in Database
Role Table Name Priv.
---------- ---------------------------------------- ------
ORACLEUSR ORACLE.BRANCH DELETE
INSERT
SELECT
UPDATE
ORACLE.CAT_MGC DELETE
INSERT
SELECT
UPDATE
ORACLE.CHOICELIST DELETE
INSERT
SELECT
UPDATE
ORACLE.CISUPDATES DELETE
INSERT
SELECT
UPDATE
ORACLE.CUSTMAST DELETE
INSERT
SELECT
UPDATE
ORACLE.CUSTTYPE SELECT
ORACLE.ERR3 DELETE
INSERT
Fri Mar 8 page 1
Roles Granted to Users in Database dbname
Role Grantee
---------- ----------------------------------------
ORACLEDEV L119069
L250526
L623063
L650637
ORACLE
ORADBA
SYSTEM
ORACLEUSR EMANS
GENERAL
KRAMER
L12273
sqltext.sql
This script gives all of the SQL that currently resides in the SGA shared pool. This script could be extended to extract this SQL to run through the explain plan utility, to see where full-table scans exist.
rem sqltext.sql - Shows all SQL in the SGA shared pool.
rem Written by Don Burleson
set pagesize 9999;
set linesize 79;
set newpage 0;
set verify off;
break on address skip 2;
column address format 9;
select
address,
sql_text
from
v$sqltext
order by
address, piece;
Here is the output from sqltext.sql:
D09AFC4C select decode(object_type, 'TABLE', 2, 'VIEW', 2, 'PACKAGE',
3,'PACKAGE BODY', 3, 'PROCEDURE', 4, 'FUNCTION', 5, 0) from
all_objects where object_name = upper('V_$SQLTEXT') and
object_type in ('TABLE', 'VIEW', 'PACKAGE', 'PACKAGE BODY',
'PROCEDURE','FUNCTION') and owner = upper('SYS')
D09B653C select owner, table_name, table_owner, db_link from
all_synonyms where synonym_name = upper('V_$SQLTEXT') and
owner = upper( 'SYS')
D09BC5AC select owner, table_name, table_owner, db_link from
all_synonyms where synonym_name = upper('v$sqltext') and
(owner = 'PUBLIC'or owner = USER)
D09C2D58 select decode(object_type, 'TABLE', 2, 'VIEW', 2, 'PACKAGE',
3,'PACKAGE BODY', 3, 'PROCEDURE', 4, 'FUNCTION', 5, 0)
from user _objects where object_name = upper('v$sqltext')
and object_type in ('TABLE', 'VIEW', 'PACKAGE', 'PACKAGE
BODY', 'PROCEDURE','FUNCTION')
D09CFF4C UPDATE SHPMT SET
AMT_SUM_ILI=:b1,PMT_WGHT_SUM_ILI=:b2,CUST_PMT_W
GHT_SUM_ILI=:b3 WHERE SHPMT_ID = :b4 AND SHPMT_SYS_SRC_CD
= :b5
tblsize.sql
This script gives the amount of storage that a table consumes within its tablespace. Be aware that a table may consume far less than its initial extent. For example, a CUSTOMER table that is allocated with INITIAL 50 MB, may only consume 2 MB of real storage, even though 50 MB have been allocated for the table.
remtblsize.sql - gives the size of a table select tablespace_name, sum(bytes/1048576) MEG from dba_segments group by tablespace_name;
Here is the listing from this script:
SQL> @tblsize Fri Mar 8 page 1 TABLESPACE_NAME MEG ------------------------------ ---------- INDX 113.265625 MPTV 253.609375 ROLB 25.0195313 SYSTEM 11.3789063 TOOLS 6.34765625 USERS 1.06640625
tsfrag.sql
This script provides a map of all tables within a tablespace.
rem tsfrag.sql - shows a tablespace map.
rem written by Don Burleson
set linesize 132;
set pages 999;
rem set feedback off;
rem set verify off;
rem set heading off;
rem set termout off;
break on file_id skip page;
break on free skip 1;
compute sum of KB on free;
spool tsfrag;
column owner format a10;
column segment_name format a10;
column tablespace_name format a14;
column file_id format 99 heading ID;
column end format 999999;
column KB format 9999999;
column begin format 999999;
column blocks format 999999;
select
tablespace_name,
file_id,
owner,
segment_name,
block_id begin,
blocks,
block_id+blocks-1 end,
bytes/1024 KB,
'' free
from sys.dba_extents
where tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
union
select
tablespace_name,
file_id,
'' owner,
'' segment_name,
block_id begin,
blocks,
block_id+blocks+1 end,
bytes/1023 KB,
'F' free
from sys.dba_free_space
where tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
order by 1, 2, 5
;
/
spool off;
!cat tsfrag.lst
tsfree.sql
This report shows the amount of fragmentation within a tablespace for all tablespaces in the database. This report is useful for determining when the tablespaces should be exported and rebuilt, since tablespace fragmentation can impede performance.
set verify off;
clear breaks;
clear computes;
set pagesize 66;
set linesize 79;
set newpage 0;
--set echo off;
--set show off;
--set term off;
--set verify off;
column temp_col new_value spool_file noprint;
column today new_value datevar noprint;
column TABLESPACE_NAME FORMAT A15 HEADING 'Tablespace';
COLUMN PIECES FORMAT 9,999 HEADING 'Tablespace|Pieces';
COLUMN FILE_MBYTES FORMAT 99,999 HEADING 'Tablespace|Mbytes';
COLUMN FREE_MBYTES FORMAT 99,999 HEADING 'Free|Mbytes';
COLUMN CONTIGUOUS_FREE_MBYTES FORMAT 99,999 HEADING 'Contiguous|Free|Mbytes';
COLUMN PCT_FREE FORMAT 999 HEADING 'Percent|FREE';
COLUMN PCT_CONTIGUOUS_FREE FORMAT 999 HEADING 'Percent|FREE|Contiguous';
ttitle left datevar right sql.pno -
center ' Instance Data File Storage' SKIP 1 -
center ' in ORACLE Megabytes (1048576 bytes)' -
skip skip;
BREAK ON REPORT
COMPUTE SUM OF FILE_MBYTES ON REPORT
select to_char(sysdate,'mm/dd/yy') today,
TABLESPACE_NAME,
PIECES,
(D.BYTES/1048576) FILE_MBYTES,
(F.FREE_BYTES/1048576) FREE_MBYTES,
((F.FREE_BLOCKS / D.BLOCKS) * 100) PCT_FREE,
(F.LARGEST_BYTES/1048576) CONTIGUOUS_FREE_MBYTES,
((F.LARGEST_BLKS / D.BLOCKS) * 100) PCT_CONTIGUOUS_FREE
from SYS.DBA_DATA_FILES D, SYS.FREE_SPACE F
where D.STATUS = 'AVAILABLE' AND
D.FILE_ID= F.FILE_ID AND
D.TABLESPACE_NAME = F.TABLESPACE
order by TABLESPACE_NAME;
set verify on;
Here is the listing from this script:
SQL> @tsfree
03/08/96 Instance Data File Storage 1
in ORACLE Megabytes (1048576 bytes)
Contiguous Percent
Tablespace Tablespace Free Percent Free FREE
Tablespace Pieces Mbytes Mbytes FREE Mbytes Contiguous
------------ ----------- ----------- ------ ------- ---------- ----------
INDX 902 200 87 43 42 21
MPTV 20 500 246 49 180 36
PATROL 33 10 10 100 9 95
ROLB 10 40 15 37 2 6
SYSTEM 23 15 4 29 4 27
TEMP 315 80 80 100 0 0
TEMP 1 180 180 100 180 100
TOOLS 1 15 9 58 9 58
USERS 11 10 9 89 6 64
-----------
1,050
9 rows selected.
| Table of Contents |