Dear Readers,
In this article, we will see the Oracle: Important commands for Lock/Unlock Stats and Backup/Restore Old Stats (SecureCRT).
MenuBar Name : Lock/Unlock Stats and Backup/Restore Old Stats
Tab Name : Check Stats for Table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
--example : 'DEPT','EMP','SALGRADE' set linesize 300 set pagesize 100 col owner for a15 col LAST_ANALYZED for a30 col table_name for a40 select OWNER,TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS , CHAIN_CNT, AVG_ROW_LEN CACHE, to_char(LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') LAST_ANALYZED from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER'; Enter value for selected_tables_check_example: 'TEST' Enter value for owner: VINOD old 4: from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER' new 4: from dba_tables where table_name in ('TEST') and owner='VINOD' OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT CACHE LAST_ANALYZED --------------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------- ------------------------------ VINOD TEST 2 5 0 0 3 18-FEB-2021 22:00:21 |
Tab Name : Backup Existing Stats
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT'); exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016'); select count(*) from vinod.TEST_STAT; SQL> exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016'); PL/SQL procedure successfully completed. SQL> select count(*) from vinod.TEST_STAT; COUNT(*) ---------- 2 |
Tab Name : Gather_stats
|
1 2 3 4 5 6 7 8 9 |
SQL> exec dbms_stats.gather_table_stats(ownname=>'VINOD',tabname=>'TEST',estimate_percent=>100, cascade=> TRUE, DEGREE => 2 ,granularity => 'ALL' , method_opt=>'for all columns size auto'); PL/SQL procedure successfully completed. --New Stats (Use First Query) OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT CACHE LAST_ANALYZED --------------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------- ------------------------------ VINOD TEST 2 5 0 0 3 24-FEB-2021 17:49:19 |
Tab Name : Import Old Stats
|
1 2 3 4 5 6 7 8 9 10 11 |
exec dbms_stats.import_table_stats('VINOD','TEST',NULL,'TEST_STAT','stats04252016',CASCADE=>TRUE); SQL> exec dbms_stats.import_table_stats('VINOD','TEST',NULL,'TEST_STAT','stats04252016',CASCADE=>TRUE); PL/SQL procedure successfully completed. -- Old Stats (Use First Query) OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT CACHE LAST_ANALYZED --------------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------- ------------------------------ VINOD TEST 2 5 0 0 3 18-FEB-2021 22:00:21 |
Tab Name : Check table stats locked or not?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
set linesize 300 col table_name for a40 SELECT stattype_locked,table_name,last_analyzed FROM dba_tab_statistics WHERE owner = '&OWNER' and stattype_locked='ALL'; SQL> alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS'; Session altered. SQL> SELECT stattype_locked,table_name,last_analyzed FROM dba_tab_statistics WHERE owner = 'VINOD' and stattype_locked='ALL'; STATT TABLE_NAME LAST_ANALYZED ----- ---------------------------------------- ------------------------------ ALL TEST 02/18/2021 22:00:21 |
Tab Name : Lock Table Stats
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
exec dbms_stats.lock_table_stats('VINOD','TEST'); SQL> exec dbms_stats.lock_table_stats('VINOD','TEST'); PL/SQL procedure successfully completed. SQL> alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS'; Session altered. SQL> SELECT stattype_locked,table_name,last_analyzed FROM dba_tab_statistics WHERE owner = 'VINOD' and stattype_locked='ALL'; STATT TABLE_NAME LAST_ANALYZED ----- ---------------------------------------- ------------------------------ ALL TEST 02/18/2021 22:00:21 |
Tab Name : Unlock Table Stats
|
1 2 3 4 5 6 7 8 9 10 11 |
exec dbms_stats.unlock_table_stats('SCOTT', 'DEPT'); SQL> exec dbms_stats.unlock_table_stats('&OWNER','&TABLE'); Enter value for owner: VINOD Enter value for table: TEST PL/SQL procedure successfully completed. SELECT stattype_locked,table_name,last_analyzed FROM dba_tab_statistics WHERE owner = 'VINOD' and stattype_locked='ALL'; no rows selected |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Note: Please test scripts in Non Prod before trying in Production.
Loading...
