Maclean’s Oracle Database Tech Blog Archives

Script set echo off set feedback off column timecol new_value timestamp column spool_extension new_value suffix select to_char(sysdate,’Mondd_hh24mi’) timecol, ‘.out’ spool_extension from sys.dual; column output new_value dbname select value || ‘_’ output from v$parameter where name = ‘db_name’; spool lfsdiag_&&dbname&&timestamp&&suffix set trim on set trims on set lines 130 set pages 100 set verify off alter…

以下脚本用于列出Oracle Buffer Cache的详细信息: REM List Buffer Cache Details SET LINESIZE 200 PAGESIZE 1400 SELECT /*+ ORDERED USE_HASH(o u) MERGE */ DECODE(obj#, NULL, to_char(bh.obj), u.name || ‘.’ || o.name) name, COUNT(*) total, SUM(DECODE((DECODE(lru_flag, 8, 1, 0) + DECODE(SIGN(tch – 2), 1, 1, 0)), 2, 1, 1, 1, 0)) hot, SUM(DECODE(DECODE(SIGN(lru_flag – 8), 1, 0, 0, 0,…

以下脚本用以列出Database-instance-session的NLS参数和所在时区: REM List NLS Parameters set linesize 90 pagesize 1400 col Parameter for a40 col Value for a40 SELECT Parameter, Value FROM NLS_DATABASE_PARAMETERS / SELECT Parameter, Value FROM NLS_INSTANCE_PARAMETERS / SELECT Parameter, Value FROM NLS_SESSION_PARAMETERS ORDER BY 1 / select dbtimezone from dual / select sessiontimezone from dual /

以下脚本可以用于列出数据库内的排序活跃性能信息并监控临时空间的使用: REM SORT ACTIVITY set linesize 150 pagesize 1400; SELECT d.tablespace_name “Name”, TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),’99,999,990.900′) “Size (M)”, TO_CHAR(NVL(t.hwm, 0)/1024/1024,’99999999.999′) “HWM (M)”, TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), ‘990.00’) “HWM % ” , TO_CHAR(NVL(t.bytes/1024/1024, 0),’99999999.999′) “Using (M)”, TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), ‘990.00’) “Using %” FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes…

以下脚本用以列出数据库中对象的依赖性: REM OBJECT DEPENDENT select D_OBJ#,        do.object_name,        do.object_type dtyp,        do.status      dsta,        D_TIMESTAMP,        ORDER#,        P_OBJ#,        po.object_name,        po.object_type ptyp,        po.status      psta,        P_TIMESTAMP   from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po  where D_OBJ# = do.object_ID    and P_OBJ# = po.object_ID    and do.object_ID in        (select object_id from…

Below lists Oracle SQL hints: Sql hints View more documents from Maclean Liu

Below lists Oracle SQL Function , and which version introduce this: SQL> SELECT NAME,VERSION,DATATYPE,DESCR FROM v$sqlfn_metadata order by 2; NAME VERSION DATATYPE DESCR —————————— ———— ——– ——————————————————————————– OPTKDUSTOOPN INVALID UNKNOWN KDUSTOOPN OPTTLK2 SQL/DS UNKNOWN LIKE OPTURNE SQL/DS UNKNOWN OPTTUA SQL/DS UNKNOWN UNION ALL COS SQL/DS NUMERIC COS SIN SQL/DS NUMERIC SIN TAN SQL/DS NUMERIC TAN…

该脚本可以用于列出Oracle日志文件切换的频率图: REM Log Switch Frequency Map col Day for a10 col Date for a10 set linesize 80 SELECT trunc(first_time) “Date”, to_char(first_time, ‘Dy’) “Day”, count(1) “Total”, SUM(decode(to_char(first_time, ‘hh24′), ’00’, 1, 0)) “h0”, SUM(decode(to_char(first_time, ‘hh24′), ’01’, 1, 0)) “h1”, SUM(decode(to_char(first_time, ‘hh24′), ’02’, 1, 0)) “h2”, SUM(decode(to_char(first_time, ‘hh24′), ’03’, 1, 0)) “h3”, SUM(decode(to_char(first_time, ‘hh24′), ’04’, 1, 0)) “h4”,…

该脚本用以汇总表空间使用情况报告: REM tablespace report set linesize 200 select a.tablespace_name, round(a.bytes_alloc / 1024 / 1024) megs_alloc, round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free, round((a.bytes_alloc – nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used, round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free, 100 – round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used, round(maxbytes / 1048576) Max from (select f.tablespace_name, sum(f.bytes)…

以下脚本可以用于收集数据库安全风险评估信息: REM list database vulnerability assessment info set escape on; set linesize 140 ; spool db_vulnerability_assessment.log Select role from dba_roles r where role not in (‘CONNECT’, ‘RESOURCE’, ‘DBA’, ‘SELECT_CATALOG_ROLE’, ‘EXECUTE_CATALOG_ROLE’, ‘DELETE_CATALOG_ROLE’, ‘EXP_FULL_DATABASE’, ‘WM_ADMIN_ROLE’, ‘IMP_FULL_DATABASE’, ‘RECOVERY_CATALOG_OWNER’, ‘AQ_ADMINISTRATOR_ROLE’, ‘AQ_USER_ROLE’, ‘GLOBAL_AQ_USER_ROLE’, ‘OEM_MONITOR’, ‘HS_ADMIN_ROLE’) and not exists (Select 1 from dba_role_privs p where p.granted_role = r.role) / select tp.grantee,…