1. Listener Adm.
2. How to check session/process utilization.
3. Information about upgrades, downgrades, and critical patch updates that have been performed on the database
4. Resize tablespace script
5. Optimize Oracle UNDO Parameters
Static Listener Registration
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = XE)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = XE)
)
)
Invoke the database to register it sefl back to the listener
alter system register;
http://www.dba-oracle.com/oracle_news/2005_4_28_use_alter_system_register_command.htm
2. How to check session/process utilization.
set colsep “|” set linesize 32767 set pagesize 10000 COLUMN RESOURCE_NAME FORMAT A25 COLUMN CURRENT_UTILIZATION FORMAT 99999 COLUMN MAX_UTILIZATION FORMAT 99999 COLUMN INITIAL_ALLOCATION FORMAT A25 COLUMN LIMIT_VALUE FORMAT A25 SELECT * FROM v$resource_limit; set colsep “|” set linesize 32767 set pagesize 10000 COLUMN RESOURCE_NAME FORMAT A25 COLUMN CURRENT_UTILIZATION FORMAT 99999 COLUMN MAX_UTILIZATION FORMAT 99999 COLUMN INITIAL_ALLOCATION FORMAT A25 COLUMN LIMIT_VALUE FORMAT A25 SELECT * FROM gv$resource_limit WHERE RESOURCE_NAME IN ('processes','sessions','transactions') ORDER BY RESOURCE_NAME;
3. Information about upgrades, downgrades, and critical patch updates that have been performed on the database
set colsep “|” set linesize 32767 set pagesize 10000 COLUMN ACTION_TIME FORMAT A25 COLUMN ACTION FORMAT A25 COLUMN NAMESPACE FORMAT A25 COLUMN VERSION FORMAT A25 COLUMN ID FORMAT 999999 COLUMN COMMENTS FORMAT A25 SELECT * FROM DBA_REGISTRY_HISTORY; CTION_TIME “|”ACTION “|”NAMESPACE “|”VERSION “|” ID“|”BUNDLE_SERIES “|”COMMENTS -------------------------“|”-------------------------“|”-------------------------“|”-------------------------“|”-------“|”------------------------------“|”------------------------- 16-JUL-13 07.20.17.333069“|”APPLY “|”SERVER “|”11.2.0.3 “|” 0“|”PSU “|”Patchset 11.2.0.2.0 PM “|” “|” “|” “|” “|” “|”
SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)* 8192 )/1024/1024 ) || 'M;' SHRINK_DATAFILES FROM DBA_DATA_FILES DBADF, (SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS* 8192 /1024/1024)- CEIL((NVL(HWM,1)* 8192 )/1024/1024 ) > 0;
5. Optimize Oracle UNDO Parameters
Actual Undo Size SELECT SUM(a.bytes) "UNDO_SIZE" FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#; UNDO_SIZE ---------- 209715200 Undo Blocks per Second SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat; UNDO_BLOCK_PER_SEC ------------------ 3.12166667 DB Block Size SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]" FROM v$parameter WHERE name = 'db_block_size'; DB_BLOCK_SIZE [Byte] -------------------- 4096 Optimal Undo Retention 209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec] Using Inline Views, you can do all in one query! SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size' / ACTUAL UNDO SIZE [MByte] ------------------------ 200 UNDO RETENTION [Sec] -------------------- 10800 OPTIMAL UNDO RETENTION [Sec] ---------------------------- 16401