Object related scripts for Oracle

 

SCRIPTs

-- Partition size :
----------------
SELECT partition_name, bytes/1024/1024 "MB" FROM dba_segments WHERE segment_name = '&TABLE_NAME' 




-- Unusable  indexes : 
-------------------
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';
ALTER INDEX '||DBO.owner||'.'||DBO.index_name || ' rebuild PARALLEL 16 ONLINE ' 



-- Session with Object : 
------------------------
select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='OBJ_NEW'; 

Select index_name , status, table_name from dba_indexes where table_name='&Table';
select OWNER , INDEX_NAME , TABLE_NAME  , DROPPED , STATUS FROM DBA_INDEXES WHERE TABLE_NAME = '&NAME';


select * from gv$access where object='TABLE1' and owner='DBO';
select INST_ID ,SID  from gv$access where object='TABLE1'  ;
 

select owner, mview_name, LAST_REFRESH_DATE, fullrefreshtim, increfreshtim from all_mview_analysis where owner='&USER_NAME';
Enter value for user_name: USER1




-- Find invalid objects :
-----------------------
select owner, count(*) from dba_tab_statistics where trunc(last_analyzed)=trunc(sysdate) group by owner;
select owner, count(*) from dba_objects where status='INVALID' group by owner;
select owner, object_type, count(*) from dba_objects where status='INVALID' group by owner, object_type;
select owner, count(*) from dba_objects where status='INVALID' group by owner;
select object_name , owner  from dba_objects where status='INVALID' and owner='USER1';





-- Statistics :
--------------
select owner,count(GK_1099B_STAGING) from dba_ind_statistics where trunc(last_analyzed)=trunc(sysdate) group by owner order by owner;
select owner,table_name, index_name, trunc(last_analyzed) from dba_ind_statistics where table_name='&Tname' order by owner;
select index_name from dba_indexes where table_name in ('TABLE1','TABLE2');
select owner, table_name, status, index_name from dba_indexes where table_name in ('TABLE1','TABLE2') order by 1,2;





-- Move table to another tablespace with partitions : 
-------------------------------------------------
select 'alter table USER_DBO.SECURITY move partition '||partition_name||' tablespace FLASH_DATA;'
from user_tab_partitions
where table_name = 'SECURITY'
order by partition_position
/
select 'alter index USER_DBO.'||i.index_name||' rebuild partition '||partition_name||' tablespace FLASH_DATA;'
from user_ind_partitions ip, user_indexes i
where i.index_name = ip.index_name
and i.table_name = 'SECURITY'
order by i.index_name, partition_name
/






-- Current Object  usage in database :
---------------------------
SELECT  vss.owner,
        vss.object_name,
        vss.subobject_name,
        vss.object_type ,
        vss.tablespace_name ,
        SUM(CASE statistic_name WHEN 'logical reads' THEN value ELSE 0 END
            + CASE statistic_name WHEN 'physical reads' THEN value ELSE 0 END) AS reads ,
        SUM(CASE statistic_name WHEN 'logical reads' THEN value ELSE 0 END) AS logical_reads ,
        SUM(CASE statistic_name WHEN 'physical reads' THEN value ELSE 0 END) AS physical_reads ,
        SUM(CASE statistic_name WHEN 'segment scans' THEN value ELSE 0 END) AS segment_scans ,
        SUM(CASE statistic_name WHEN 'physical writes' THEN value ELSE 0 END) AS writes
FROM    v$segment_statistics vss
WHERE   vss.owner IN ('SYS', 'SYSTEM')
GROUP BY vss.owner,
        vss.object_name ,
        vss.object_type ,
        vss.subobject_name ,
        vss.tablespace_name
ORDER BY reads DESC;




-- List the top 10 segments in the database that have the most number of PHYSICAL READS against them.
--------------------------------------------------------------------------------------------------
Script can also be changed to query on 'physical writes' instead.

set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10

select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;




-- Top Session with Physical reads :
----------------------------------
set linesize 120
col os_user format a10
col username format a15

col pid format 9999999999
PROMPT SESSIONS SORTED BY PHYSICAL READS
PROMPT
select
  OSUSER os_user,username,
    PROCESS pid,
    ses.SID sid,
    SERIAL#,
    PHYSICAL_READS,
     BLOCK_CHANGES
 from       v$session ses,
   v$sess_io sio
  where      ses.SID = sio.SID
and username is not null
and status='ACTIVE'
 order      by PHYSICAL_READS;
 
 

 
-- Find IO on  Object :
----------------------
col c0 heading 'Begin|Interval|time' format a8
col c1 heading 'Owner'               format a10
col c2 heading 'Object|Type'         format a10
col c3 heading 'Object|Name'         format a15
col c4 heading 'Average|CPU|Cost'    format 9,999,999,999
col c5 heading 'Average|IO|Cost'     format 9,999,999
select
 to_char(sn.begin_interval_time,'mm-dd hh24') c0,  
 p.object_owner                               c1,
 p.object_type                                c2,
 p.object_name                                c3,
 avg(p.cpu_cost)                              c4,
 avg(p.io_cost)                               c5
from
 dba_hist_sql_plan p,
 dba_hist_sqlstat  st,
 dba_hist_snapshot sn
group by
 to_char(sn.begin_interval_time,'mm-dd hh24'),
 p.object_owner,
 p.object_type,
 p.object_name
order by
 1,2,3 desc; 
 
 
 
-- Rebuilding unusable indexes, index partitions, and index subpartitions :
--------------------------------------------------------------------------
select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status='UNUSABLE';
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from dba_ind_partitions where status='UNUSABLE';
select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||';' from dba_ind_subpartitions where status='UNUSABLE';
select owner, index_name , table_name, status, index_type from dba_indexes where status='UNUSABLE';




-- Re-Compile Invalid  : 
------------------------

set heading on;
set feedback on;
set echo on;
Set lines 999;

Spool run_invalid.sql

select 
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;

set heading off;
set feedback off;
set echo off;
spool off;




-- Find Invalid Objects by Owner :
----------------------------------
set pages 50000 lines 32767
select OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID' and OWNER in ('&OWNER') GROUP BY OWNER, OBJECT_TYPE ORDER BY OBJECT_TYPE;



-- Recompile Invalid Objects :
-----------------------------
spool invalid_one.lst
select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type) || ' ' ||owner|| '."'||object_name||'" '||decode(object_type,'PACKAGE BODY','COMPILE BODY','compile')|| ' ; '
from dba_objects where object_type in ('PACKAGE','PACKAGE BODY','PROCEDURE','VIEW', 'TRIGGER','FUNCTION','SYNONYM') and status='INVALID' and owner in ('&owner') 
/
spool off



-- Who's accessing a particular table :
-----------------------------------
SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id FROM dba_objects where object_name='TABLE1' and object_type='TABLE' and owner='SCOTT') ;


SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id FROM dba_objects where object_name='TABLE1');




-- Find the all session ID of the object :
-----------------------------------------
select sid from v$access where object='&Object_name'
 
 

-- Get the session details from the above 
-------------------------------------------
select OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from v$locked_object where SESSION_ID='&SessionID';



-- Find Segment with High Usage in particular tablespace :
-------------------------------------------------------
col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSAUX' ORDER BY BLOCKS desc) where rownum < 11;



-- All details about object : 
-----------------------------

select * from ALL_OBJECTS
where upper(object_type) in ('PROCEDURE') and owner in ('USER_DBO','USER2_DBO2') and 
(upper(object_name) like '%TIGER_AREA%' or upper(object_name) like '%MAIN_EVENT%' or upper(object_name) like '%TIGER_AREA%')
;




alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300
col owner       for a15
col object_type for a15
col object_name for a30
col temporary  for a10
col generated  for a20
select owner,
       object_name,
    -- subobject_name,
    -- object_id,
    -- data_object_id,
       object_type,
       created,
       last_ddl_time,
    -- timestamp,
       status,
       temporary,
       generated,
       secondary,
    -- namespace,
       edition_name
from   dba_objects
where  upper(object_name) like upper('%&OBJECT_NAME%')
order by owner, object_name;




alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300 
col owner       for a15
col object_type for a15
col object_name for a30
col temporary  for a10
col generated  for a20
select owner,
       object_name,
    -- subobject_name,
    -- object_id,
    -- data_object_id,
       object_type,
       created,
       last_ddl_time,
    -- timestamp,
       status,
       temporary,
       generated,
       secondary,
    -- namespace,
       edition_name
from   dba_objects
where 1=1   
and last_ddl_time > sysdate -1   ---- last one day
order by owner, object_name;



-- Compile the objects  :
-------------------------
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set pagesize 300 linesize 300 
col info for a80
select
decode( object_type, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' ) info ,
'-- ', status,created,last_ddl_time
from dba_objects a
where 1=1
and STATUS = 'INVALID' 
and OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE','TRIGGER', 'VIEW' )
and owner not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by object_type,object_name;



-- Object with Tablespace :
--------------------------

set pagesize 300 linesize 150
col owner    for a25
col object_name  for a30
col SEGMENT_NAME for a25
select object_name,o.owner,tablespace_name ,o.object_type, o.status,count(*) from dba_objects o,dba_segments s
where 1=1
-- and STATUS='INVALID' 
--and o.OWNER='OWNER'
and s.segment_name = o.object_name 
and s.segment_type = o.object_type 
and s.owner = o.owner
 and  upper(object_name) like upper('%&OBJECT_NAME%')
group by object_name,o.owner,o.object_type, o.status ,tablespace_name 
order by owner
/






Index used or not :
--------------------
Select index_name , status, table_name from dba_indexes where table_name='&Table';
 

- Check indexes under schema :
------------------------------
there are no indexes on that schema.
select count(*) from user_indexes where table_owner='TABLE_DBO';

  COUNT(*)
----------
         0
         
         
         
         
 
-- Object with Larger size on specific tablespace :
---------------------------------------------------
 select
   owner as "Schema"
   , segment_name as "Object Name"
   , segment_type as "Object Type"
   , round(bytes/1024/1024/1024,2) as "Object Size (GB)"
   , tablespace_name as "Tablespace"
 from dba_segments
 where tablespace_name='DATA1'
order by  round(bytes/1024/1024/1024,2);




To move LOB :
-------------
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE USERS'||CHR(10)||
                   'LOB ('||COLUMN_NAME||') STORE AS '||SEGMENT_NAME||CHR(10)||
                   '(TABLESPACE USERS);' SQL_STATEMENTS FROM DBA_LOBS
                   WHERE
                   TABLESPACE_NAME ='SYSTEM' and OWNER='DBO';
	
			   
				   
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE USERS;' FROM dba_tables where TABLESPACE_NAME='SYSTEM' and  owner='DBO';	

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' rebuild online TABLESPACE USERS;' FROM DBA_INDEXES WHERE  OWNER='DBO' and TABLESPACE_NAME='SYSTEM';

SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE USERS'||CHR(10)||
                   'LOB ('||COLUMN_NAME||') STORE AS '||SEGMENT_NAME||CHR(10)||
                   '(TABLESPACE USERS);' SQL_STATEMENTS FROM DBA_LOBS
                   WHERE TABLESPACE_NAME='SYSTEM'  and  owner not in ('SYS','SYSTEM');
	
			   
				   
SQL> select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME from dba_lobs where SEGMENT_NAME='SYS_LOB0000104411C00003$$'; 
 


select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME from dba_lobs where TABLE_NAME='TABLE1$$'; 




1. move table to same or different tablespace. Rebuild of indexes is must after move of table as rowid of rows are changed.
2. do export and import of table. Need business outage.
3. rebuild only indexes(with/without online) to claim free space of indexes only.
4. alter table table_name shrink space cascade; This will try to put blocks togather starting from start of first free  lock in the datafile. Cascade option will take care of indexes, so no need to rebuild indexes in this case.
5. online re-org of tablespace/tables with exception for tables with data type long and raw.










The Oracle extents are mapped to OS data blocks. The mapping can be revealed by dba_free_space and dba_extents.



SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as basicfile (tablespace data1);'
FROM DBA_LOB_PARTITIONS 
WHERE TABLE_OWNER = 'DBO' 
AND TABLESPACE_NAME = 'DATA' ;
AND TABLE_NAME='TABLE1' ;


SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as basicfile (tablespace data1);'
FROM DBA_LOB_PARTITIONS 
WHERE TABLE_OWNER = 'DBO' 
AND TABLESPACE_NAME = 'DATA' ;
AND TABLE_NAME='TABLE1' ; 
 

SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME ||' TABLESPACE CSTR_XML_DATA ;'FROM dba_TAB_SUBPARTITIONS WHERE TABLE_NAME IN ('TRS'); 
 



-- Sub-Partition on Indexes :
--------------------------
SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name
FROM   dba_ind_SUBPARTITIONS
WHERE  status = 'UNUSABLE';



-- Partition on Indexes :
-------------------------
SELECT index_owner, index_name, partition_name, tablespace_name
FROM   dba_ind_PARTITIONS
WHERE  status = 'UNUSABLE';



-- Index  : 
-----------
SELECT owner, index_name, tablespace_name
FROM   dba_indexes
WHERE  status = 'UNUSABLE';



-- Partitions of the objects : 
------------------------------
COLUMN high_value FORMAT A20
SELECT table_name,
       partition_name,
       high_value,
       num_rows
FROM   user_tab_partitions
ORDER BY table_name, partition_name;





-- Top 10 Objects in database : 
-------------------------------
select a.owner, a.TABLE_NAME, b.SIZE_GB, ((a.BLOCKS*8192/1024/1024/1024)-(a.NUM_ROWS*AVG_ROW_LEN/1024/1024/1024)) as ACTUAL_GB, 
(b.SIZE_GB-((a.BLOCKS*8192/1024/1024/1024)-(a.NUM_ROWS*AVG_ROW_LEN/1024/1024/1024))) Savings,
a.TABLESPACE_NAME, b.SEGMENT_TYPE TABLE_TYPE, a.last_analyzed
from 
dba_tables a, 
(select * from 
 (select owner, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, sum(bytes/1024/1024/1024) Size_GB 
 from 
 dba_segments 
where segment_type like '%TABLE%' 
 group by segment_name, owner, SEGMENT_TYPE, TABLESPACE_NAME 
 order by 5 desc) 
where rownum<11) b 
where a.TABLE_NAME=b.SEGMENT_NAME and a.owner=b.owner
order by SIZE_GB desc;



Below (in case of subpartition) :
--------------------------------
select 'alter table '|| table_owner||'.'||table_name ||' move subpartition '||subpartition_name||' lob ('||column_name||') store as securefile (tablespace '||tablespace_name||' ) update global indexes parallel 4;' as MOVE_SCRPT
from dba_lob_subpartitions
where table_owner='owner'
--and table_name='table_name'


The syntax to move a LOB partition is the following.

alter table <table name> move partition <table partition name>
lob (<lob column name>) store as <optional lob partition name> (tablespace <lob tablespace name>);

-or-

alter table <table name> move partition <table partition name>
lob (<lob column name>) store as (tablespace <lob tablespace name>); 



If Insert Statement is Running Slow :
 
Consider dropping the indexes on a table before loading a large batch of data. This makes the insert statement run faster. Once the inserts are completed, you can recreate the index again.
 
If you are inserting thousands of rows in an online system, use a temporary table to load data. 
 












Ensure that this temporary table does not have any index. Since moving data from one table to another is much
faster than loading from an external source, you can now drop indexes on your primary table, move data from temporary to final table, and finally recreate the indexes.


Found this post interesting? Subscribe us  😊😉