Table Defragmentation Methods : -
- Shrinking tables
- Moving tables (Alter Table Move example)
- Online redefinition (Row Migration Using Online Redefinition )
- Export/Import ( EXPDP | IMPDP - table_exists_action=tuncate | Or drop and recreate expdp | impdp )
Fragmentation and Defragmentation Table scenario using shrinking table
C:\Users\varun>sqlplus sys/system123@orcl as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 5 21:12:10 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.6
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> conn soe/soe@pdb
Connected.
SQL> set sqlprompt "ADMIN> "
- Drop and recreate table cust
ADMIN> DROP TABLE CUST CASCADE CONSTRAINTS;
Table dropped.
ADMIN> CREATE TABLE CUST
2 ( CUSTOMER_NO NUMBER(6),
3 FIRST_NAME VARCHAR2(20),
4 LAST_NAME VARCHAR2(20),
5 NOTE1 VARCHAR2(100),
6 NOTE2 VARCHAR2(100)
7 ) PCTFREE 0 TABLESPACE SOETBS;
Table created.
- Insert values into CUST table
ADMIN> INSERT INTO CUST
2 SELECT LEVEL AS CUSTOMER_NO,
3 DBMS_RANDOM.STRING('A', 15) FIRST_NAME,
4 DBMS_RANDOM.STRING('A', 12) LAST_NAME,
5 DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE1,
6 DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE2
7 FROM DUAL
8 CONNECT BY LEVEL <= 100000;
100000 rows created.
ADMIN> COMMIT;
Commit complete.
- Create Index CUST_NO_IDX, CUST_FNAME_IDX on CUST object
ADMIN> CREATE INDEX CUST_NO_IDX ON CUST (CUSTOMER_NO) NOLOGGING TABLESPACE SOETBS;
Index created.
ADMIN> CREATE INDEX CUST_FNAME_IDX ON CUST(FIRST_NAME) NOLOGGING TABLESPACE SOETBS;
Index created.
ADMIN> BEGIN
2 FOR I IN 1..100000 LOOP
3 IF MOD(I,2)=0 THEN
4 UPDATE CUST SET NOTE1=NULL, NOTE2=DBMS_RANDOM.STRING('A',
5 TRUNC(DBMS_RANDOM.value(0,10))) WHERE CUSTOMER_NO=I;
6 IF MOD(I,100)=0 THEN
7 COMMIT;
8 END IF;
9 ELSIF MOD(I,3)=0 THEN
10 DELETE CUST WHERE CUSTOMER_NO=I;
11 END IF;
12 END LOOP;
13 COMMIT;
14 END;
15 /
PL/SQL procedure successfully completed.
ADMIN> -- index dropped because we want to test full table scan (FTS)
ADMIN> DROP INDEX CUST_NO_IDX;
Index dropped.
- Analyze table cust to detremine status of used and free blocks
ADMIN> ANALYZE TABLE CUST COMPUTE STATISTICS;
Table analyzed.
- Script to display deleted space on CUST table
ADMIN> SELECT BLOCKS, BLOCKS*8192/1024 TOTAL_SIZE_KB, AVG_SPACE,
2 round(BLOCKS*AVG_SPACE/1024,2) FREE_SPACE_KB
3 FROM USER_TABLES WHERE TABLE_NAME='CUST';
BLOCKS TOTAL_SIZE_KB AVG_SPACE FREE_SPACE_KB
---------- ------------- ---------- -------------
1762 14096 4122 7092.74
- Query to enable row movement in the CUST table.
- ( It means already some update in progress stop or let complete)
ADMIN> ALTER TABLE CUST ENABLE ROW MOVEMENT;
ALTER TABLE CUST ENABLE ROW MOVEMENT
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
- Again run enable row movement.
ADMIN> ALTER TABLE CUST ENABLE ROW MOVEMENT;
Table altered.
- Execute to shrink the CUST table
ADMIN> ALTER TABLE CUST SHRINK SPACE CASCADE;
Table altered.
- Analyze table CUST
ADMIN> ANALYZE TABLE CUST COMPUTE STATISTICS;
Table analyzed.
- Check the space status of CUST Table
ADMIN> SELECT BLOCKS, BLOCKS*8192/1024 TOTAL_SIZE_KB, AVG_SPACE,
2 round(BLOCKS*AVG_SPACE/1024,2) FREE_SPACE_KB
3 FROM USER_TABLES WHERE TABLE_NAME='CUST';
BLOCKS TOTAL_SIZE_KB AVG_SPACE FREE_SPACE_KB
---------- ------------- ---------- -------------
866 6928 73 61.74
ADMIN>
No comments:
Post a Comment