| SQL
4DBA's
Object scripts
|
rem OBJECT SIZES
rem description: Shows all segments - tables and indexes.
col owner for A16
col KBytes for 999,999
col Object# for 99999
col S1 for A8 head 'Seg Type'
break on tablespace_name
select tablespace_name
,owner
,segment_type S1
,count(*) "Object#"
,trunc(sum(bytes/1024),0) "KBytes"
from sys.DBA_SEGMENTS
where owner not in ('SYS','SYSTEM')
group by tablespace_name,owner,segment_type
/
rem OBJECT TYPES
rem description: Shows all objects - tables, indexes and views.
col owner for A12
break on owner
compute sum of NR_OBJECTS on owner
select owner
,object_type
,count(*) NR_OBJECTS
from sys.DBA_OBJECTS
where owner like upper('&Owner%')
and owner not in ('SYS','SYSTEM')
group by owner,object_type
/
rem SYNONYMS
rem description: Shows all synonyms of all owners grouped by owner.
select owner,count(*) from DBA_SYNONYMS
where owner not in ('SYS','SYSTEM')
group by owner
/
rem indT.sql
rem description: Shows all indexed columns by table.
col column_position format 99 head 'COL|pos'
col uniqueness format A9 head 'Unique?'
select C.index_name,uniqueness,C.column_name,column_position
from ALL_IND_COLUMNS C
,ALL_INDEXES I
where C.index_name = I.index_name
and I.table_name = upper('&table_name')
order by index_name,column_position
;
rem indI.sql
rem description: Shows all indexed columns by Index.
col table_name format A24 trunc
col index_name format A18 trunc
col column_name format A22 trunc
col column_position format 99 head 'COL|pos'
col uniqueness format A9 head 'Unique?'
prompt searching for index name.
select C.table_name,C.index_name,uniqueness,C.column_name,column_position
from ALL_IND_COLUMNS C
,ALL_INDEXES I
where C.index_name = I.index_name
and I.index_name = upper('&index_name')
order by index_name,column_position ;
;
rem ALL INDEXES
rem description: List all indexes.
rem compatible : Oracle8 upwards
col Cols for 9999 head 'Nr.Of|Cols'
col N0 for A12 head 'OWNER'
col N1 for A22 head 'Table name' trunc
col N2 for A9 head 'Table|Created'
break on N0 on N1 on N2
select U1.Name N0
,O2.Name N1
,O2.CTIME N2
,O1.Name "Index name"
,O1.CTIME "Created"
,Cols
from SYS.IND$ I,
SYS.OBJ$ O1,
SYS.OBJ$ O2,
SYS.USER$ U1
where I.Obj# = O1.Obj#
and I.BO# = O2.Obj#
and O1.Owner# = U1.User#
and O1.Owner# > 1
order by U1.User#,O1.Name
/
rem REBUILD.sql
rem desc: Creates rebuild script for indexes with extents > 10
rem This makes it possible to defragment or move the index.
define spooldir=C:\temp
set feed off head off wrap on
select 'prompt index: '||owner||'.'||segment_name||' extents now: '||extents||'
alter index '||owner||'.'||segment_name||'
rebuild tablespace '||tablespace_name||' ;'
from DBA_SEGMENTS
where owner = upper('&owner')
and segment_type = 'INDEX'
and extents > 10
spool &spooldir\rebuild.tmp
/
spool off
set feed ON head ON
prompt run: @rebuild.tmp
rem REBUILD2.sql
rem desc: Creates rebuild script for indexes with extents > 10
rem This makes it possible change the initial/next storage size of the index.
define spooldir=C:\temp
set feed off head off pages 100
set wrap ON
select 'prompt index: '||owner||'.'||segment_name||' Kbytes now: '||bytes/1024||'
-- extents now: '||extents||'
alter index '||owner||'.'||segment_name||'
rebuild tablespace '||tablespace_name||'
storage (initial '||bytes/1024||'K next '||bytes/5120||'K pctincrease 0) ;'
from dba_segments
where owner = upper('&owner')
and segment_type = 'INDEX'
and extents > 10
spool &spooldir\rebuild.tmp
/
spool off
set feed ON head ON
prompt run: @rebuild.tmp
rem INVALID.sql
rem desc: Show all invalid objects in the database.
col owner for A10 trunc
col object_name for A26 trunc
col object_ID noprint
col object_type for A12 head Type
col timestamp for A20 noprint
select * from dba_objects
where status='INVALID'
/
rem COMPILE.sql
rem description: compiles all invalid objects in the database
define spooldir=C:\temp
set head off feedback off wrap on trimspool on
spool &spooldir\compile.tmp
select 'prompt '||object_type||' '||owner||'.'||object_name||'
prompt Status was: '||status||'
alter '
|| object_type||' '||owner||'.'||object_name
|| ' compile;'
from DBA_OBJECTS
where status <> 'VALID'
and object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'VIEW')
/
select 'prompt '||object_type||' '||owner||'.'||object_name||'
prompt Status was: '||status||'
alter PACKAGE '
|| owner||'.'||object_name
|| ' compile body;'
from DBA_OBJECTS
where status <> 'VALID'
and object_type = 'PACKAGE BODY'
/
spool off
set feedback on head on
@@&spooldir\compile.tmp
rem ANALYZE.sql
rem desc: Analyze all tables of one owner
define spooldir=C:\temp
set pagesize 0 feed OFF verify off
accept Owner prompt 'Enter owner :'
select 'analyze table '||owner||'.'||table_name||' estimate statistics;'
from DBA_TABLES
where owner = upper('&Owner')
spool &spooldir\ana.tmp
/
spool off
set feed on
@ana.tmp
rem ANALYZE1.sql
rem desc: Analyze 1 table.
define spooldir=C:\temp
set pagesize 0 feed OFF verify off
accept Table_name prompt 'Enter table name :'
select 'analyze table '||owner||'.'||table_name||' estimate statistics;'
from DBA_TABLES
where table_name = upper('&&Table_name')
spool &spooldir\ana.tmp
/
spool off
set feed on
@ana.tmp
undefine Table_name
rem ANALYZESTOP.sql
rem desc: Deletes all statistics of one owner
rem Very quick as it only deletes statistics of analyzed tables.
define spooldir=C:\temp
set pagesize 0 feed OFF verify off
accept Owner prompt 'Enter owner :'
select 'analyze table '||owner||'.'||table_name||' delete statistics;'
from DBA_TABLES
where owner = upper('&Owner')
and num_rows is not NULL
spool &spooldir\ana.tmp
/
spool off
set feed on
@ana.tmp
rem ANALYZED.sql
rem desc: Which tables have statistics and have been analyzed?
select owner,table_name,num_rows
from DBA_TABLES
where owner = upper('&Owner')
and num_rows is not NULL
/
| SQL
4DBA's
Object scripts
|