Oracle awrinfo sql Tips
This script will output general Automatic Workload Repository(AWR) information such as the size, data distribution, etc. in AWR and SYSAUX. The intended use of this script is for diagnosing abnormalities in AWR and not for diagnosing issues in the database instance. Please look at addmrpt.sql and awrrpt.sql for diagnosing database issues.
Oracle SYSAUX huge space Tips
If SYS.WRI$_ADV_OBJECTS is one of the largest objects, cancel or delete old segment adviser tasks.
The task ‘Get shrink advice based on object growth trend’ in particular can use a lot of space.
If there is space pressure, cancel and delete these tasks in Grid/Database Control via Performance> Advisors Home or manually using the DBMS_ADVISOR package.
Oracle rman catalog Tips
Error:
Oracle error from recovery catalog database: ORA-00955: name is already used by an existing object
Oracle error from recovery catalog database: ORA-00942: table or view does not exist
Fix:
drop catalog and recreate catalog.
Oracle SQLcl Tips
Oracle SQLcl (SQL Developer Command Line), a Java-based command-line interface for Oracle Database.
Using SQLcl, you can execute SQL and PL/SQL statements interactively or as as a batch file.
SQLcl provides inline editing, statement completion, command recall, and also supports existing SQL*Plus scripts.
Oracle 19c index stats[ORA-20005] Tips
Need to pay attention to the lock status of table statistics.
Better to use create index ... online compute statistics to gather index statistics.
-- Create test table t2021
SYS@pdb1> create table t2021 as select * from dba_objects;
Table created.
SYS@pdb1>
-- The statistics is NOT updated.
SYS@pdb1> col owner for a15
SYS@pdb1> col table_name for a20
SYS@pdb1> col partition_name for a25
SYS@pdb1> set linesize 300
SYS@pdb1> select owner,table_name,partition_name,num_rows,blocks,stale_stats,stattype_locked,last_analyzed from dba_tab_statistics where table_name = upper('t2021');
OWNER TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS STALE_S STATT LAST_ANAL
--------------- -------------------- ------------------------- ---------- ---------- ------- ----- ---------
SYS T2021
SYS@pdb1>
-- Gather table stats
SYS@pdb1> exec dbms_stats.gather_table_stats(NULL,'T2021');
PL/SQL procedure successfully completed.
SYS@pdb1> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SYS@pdb1> select owner,table_name,partition_name,num_rows,blocks,stale_stats,stattype_locked,last_analyzed from dba_tab_statistics where table_name = upper('t2021');
OWNER TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS STALE_S STATT LAST_ANALYZED
--------------- -------------------- ------------------------- ---------- ---------- ------- ----- -------------------
SYS T2021 73500 1425 NO 2021/11/19 09:42:44
SYS@pdb1>
-- Lock table's stats
SYS@pdb1> exec dbms_stats.lock_table_stats(NULL, 'T2021');
PL/SQL procedure successfully completed.
SYS@pdb1> select owner,table_name,partition_name,num_rows,blocks,stale_stats,stattype_locked,last_analyzed from dba_tab_statistics where table_name = upper('t2021');
OWNER TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS STALE_S STATT LAST_ANALYZED
--------------- -------------------- ------------------------- ---------- ---------- ------- ----- -------------------
SYS T2021 73500 1425 NO ALL 2021/11/19 09:42:44
SYS@pdb1>
-- Test create index on t2021 table and the index stats will not gathered auto.
SYS@pdb1> create index idx_t2021_object_id on t2021(object_id,0) online;
Index created.
SYS@pdb1>
SYS@pdb1> col index_name for a20
SYS@pdb1> select owner,index_name,table_name,partition_name,num_rows,blevel,leaf_blocks,clustering_factor,stale_stats,stattype_locked,last_analyzed from dba_ind_statistics where table_name= upper('t2021');
OWNER INDEX_NAME TABLE_NAME PARTITION_NAME NUM_ROWS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR STA STATT LAST_ANALYZED
--------------- -------------------- -------------------- ------------------------- ---------- ---------- ----------- ----------------- --- ----- -------------------
SYS IDX_T2021_OBJECT_ID T2021 ALL
SYS@pdb1>
SYS@pdb1> exec dbms_stats.gather_index_stats(NULL,'IDX_T2021_OBJECT_ID');
BEGIN dbms_stats.gather_index_stats(NULL,'IDX_T2021_OBJECT_ID'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 30110
ORA-06512: at "SYS.DBMS_STATS", line 30024
ORA-06512: at "SYS.DBMS_STATS", line 30001
ORA-06512: at "SYS.DBMS_STATS", line 9393
ORA-06512: at "SYS.DBMS_STATS", line 10317
ORA-06512: at "SYS.DBMS_STATS", line 29312
ORA-06512: at "SYS.DBMS_STATS", line 30097
ORA-06512: at line 1
SYS@pdb1>
-- if the table stats is locked, create index with online will not show error msg.
-- if the table stats is locked, create index with online and compute statistics will show error msg.
SYS@pdb1> create index idx_t2021_object_name on t2021(object_name) online compute statistics;
create index idx_t2021_object_name on t2021(object_name) online compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
SYS@pdb1>
PL/SQL Packages and Types Reference / 170 DBMS_STATS
Have a good work&life! 2021/11 via LinHong
Oracle session reset parameter Tips
Oracle Sample Schema Tips
Oracle Calibrate IO Test Tips
Oracle ORA-01552 Tips
Monitor wrong password user
Reset root password on Oracle Linux 8