Precheck in table and index level:
Step 1:
Check the Table size and Index size
select segment_name,TABLESPACE_NAME ,segment_type, bytes/1024/1024/1024 size_gb from dba_segments where segment_name = ‘&segment_name’ or segment_name in (select index_name from dba_indexes where table_name=’&tablename’ and table_owner=’&owner’);
Check the invalid object count
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID’;
Check the LAST_ANALYZED stats of the table
SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME IN (&tablename);
Table and Index size output before REORG :-
select segment_name,TABLESPACE_NAME ,segment_type, bytes/1024/1024/1024 size_gb from dba_segments where segment_name = ‘&segment_name’ or segment_name in (select index_name from dba_indexes where table_name=’&tablename’ and table_owner=’&owner’);
Table Fragmentation query in database level :-
set lines 170
set pages 10000
col owner format a30
col table_name format a30
col TOTAL_SIZE format 99999999999
col ACTUAL_SIZE format 999999999999
col FRAGMENTED_SPACE format 999999999999
select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),0) “TOTAL_SIZE”, round((num_rows*avg_row_len
/1024/1024),0) “ACTUAL_SIZE”, round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),0) “FRAGMENTED_SPACE” from
dba_tables where owner not in (‘SYS’,’SYSTEM’,’FDBA’,’PERFSTAT’,’DBMON’) and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 100 order by 8 desc;
EXPORT / IMPORT DATAPUMP:-
EXPORT
expdp \”/ as sysdba\” directory=expdp_tab_wtls dumpfile= lob_2-Aug-2023.dmp logfile= lob_2-Aug-2023.log tables=SCHEMA.TABLE_CONTENT_HTML
IMPORT
impdp \”/ as sysdba\” directory=expdp_tab_wtls dumpfile=lob_2-Aug-2023.dmp logfile=IMPlob_2-Aug-2023
.log REMAP_TABLESPACE=wtlsdata:data TABLE_EXISTS_ACTION=REPLACE
Collecting the Gather the statistics of the table
spool /orashare1/Tablearchive/lvprod/DIM_MM/LOVIA_PROD_DIM_MM_STATS.log
SET PAGESIZE 2000
SET LINESIZE 500
SET TIME ON TIMING ON;
SET FEED ON
set echo on
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => ‘SCHEMA’, tabname => ‘TABLE NAME’, ESTIMATE_PERCENT => 100,METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’, CASCADE => TRUE,DEGREE => 200);
end;
/
Post check in table and index level :-
Checking index status after import:
SELECT INDEX_NAME, TABLE_NAME,STATUS FROM ALL_INDEXES WHERE TABLE_NAME IN (‘TABLENAME’);
Check the Table size and Index size
select segment_name,TABLESPACE_NAME ,segment_type, bytes/1024/1024/1024 size_gb from dba_segments where segment_name = ‘&segment_name’ or segment_name in (select index_name from dba_indexes where table_name=’&tablename’ and table_owner=’&owner’);
Check the invalid object count
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID’;
CLEAR THE INVALID OBJECTS:
Sql> @?/rdbms/admin/utlrp.sql
