【学习】
2025年阅读清单
-
Oracle SYSAUX huge space Tips
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
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 existFix:
drop catalog and recreate catalog.
-
Oracle sqlcl Tips
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
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 statisticsto gather index statistics.Test senarios.
-- 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>Reference
PL/SQL Packages and Types Reference / 170 DBMS_STATS
Have a good work&life! 2021/11 via LinHong
-
Oracle session reset parameter Tips
Oracle session reset parameter Tips
-
Oracle Sample Schema Tips
Oracle Sample Schema Tips
-
Oracle Calibrate IO Test Tips
Oracle Calibrate IO Test Tips
-
Oracle ORA-01552 Tips
Oracle ORA-01552 Tips
-
Oracle Monitor wrong password user Tips
Monitor wrong password user
-
Oracle Reset Linux 8 Root password Tips
Reset root password on Oracle Linux 8
-
Oracle 21c RAC Linux 8 Install Tips
Oracle 21c RAC Linux 8 Install Tips
Tim had prepared the detail install tips regarding oracle 21c RAC.
Very convenient and efficient!
I changed VM memory size 800MB/5GB/5GB and change the DB’s size to 800MB for my mac’s limited memeory.
-
Oracle nslookup Linux 8 Install Tips
Oracle nslookup Linux 8 Install Tips
- Oracle 26ai Auto Redo Prioritization Tips
- Oracle Private AI Services ContainerTips
- Oracle 26ai RPM Install Tips
- Oracle OCI Network Performance TestTips
- CPU Perf Sysbench Test Tips
- Oracle 26ai 23.26.1 OnP Install Tips
- Oracle 26ai 23.26.1 OnP Tips
- Oracle 26ai release from 2025/10/15 Tips
- Oracle redo generation's causes Tips
- Oracle 23ai Cell events Tips