1 Reply Latest reply: Nov 26, 2012 3:51 AM by 975952 RSS

    abcdef

    975952
      to_date('03-23-2010','mm-dd-yyyy')
      to_date('2008-06-08','yyyy-mm-dd')
      DBMS_OUTPUT.PUT_LINE(' 4th Where clause: ' || WHERE_CLAUSE);
      HKey_Local Machine -> Software -> Microsoft -> MSLicensing
      topas

      Removing batch of Files in linux:
      =====================================

      find . -name "*.arc" -mtime +20 -exec rm -f {} \;
      find . -name "*.dbf" -mtime +60 -exec mv {} /backup/Arch_Bkp_02May11/ \;

      ALTER DATABASE
      SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
      ================================================================================

      Find top N records:
      ===================
      select * from (select ename from emp order by sal)
      where rownum <=n;

      Find top Nth record: (n=0 for 1st highest)
      =========================================
      select * from emp a
      where (n =
      (select count(distinct b.sal) from emp b
      where b.sal > a.sal));

      Query for Listing last n records from the table
      =================================================
      select * from (select * from emp order by rownum desc) where rownum<4

      HOW TO tablespace wise and file wise info
      ============================
      col file_name for a45
      col tablespace_name for a15
      set linesize 132

      select a.tablespace_name,a.file_name,a.AUTOEXTENSIBLE,----a.status,
      round(a.bytes/1024/1024,2) Total_MB,
      round(sum(b.bytes)/1024/1024,2) Free_MB,
      round((a.bytes/1024/1024 - sum(b.bytes)/1024/1024),2) Used_MB
      from dba_data_files a,dba_free_space b
      where a.file_id=b.file_id
      and a.tablespace_name=b.tablespace_name
      group by a.tablespace_name,b.file_id,a.file_name,a.bytes,a.AUTOEXTENSIBLE--,a.status
      order by tablespace_name;

      col tablespace_name for a15

      SELECT tablespace_name,ts_#,num_files,sum_free_mbytes,count_blocks,max_mbytes,
      sum_alloc_mbytes,DECODE(sum_alloc_mbytes,0,0,100 * sum_free_mbytes /sum_alloc_mbytes ) AS pct_free
      FROM (SELECT v.name AS tablespace_name,ts# AS ts_#,
      NVL(SUM(bytes)/1048576,0) AS sum_alloc_mbytes,
      NVL(COUNT(file_name),0) AS num_files
      FROM dba_data_files f,v$tablespace v
      WHERE v.name = f.tablespace_name (+)
      GROUP BY v.name,ts#),
      (SELECT v.name AS fs_ts_name,ts#,NVL(MAX(bytes)/1048576,0) AS max_mbytes,
      NVL(COUNT(BLOCKS) ,0) AS count_blocks,
      NVL(SUM(bytes)/1048576,0) AS sum_free_mbytes
      FROM dba_free_space f,v$tablespace v
      WHERE v.name = f.tablespace_name(+)
      GROUP BY v.name,ts#)
      WHERE tablespace_name = fs_ts_name
      ORDER BY tablespace_name;

      ==================================
      col file_name for a45
      col tablespace_name for a15
      set linesize 132

      select a.tablespace_name,a.file_name,a.AUTOEXTENSIBLE,----a.status,
      round(a.bytes/1024/1024,2) Total_MB,
      round(sum(b.bytes)/1024/1024,2) Free_MB,
      round((a.bytes/1024/1024 - sum(b.bytes)/1024/1024),2) Used_MB
      from dba_data_files a,dba_free_space b
      where a.file_id=b.file_id
      and a.tablespace_name=b.tablespace_name
      group by a.tablespace_name,b.file_id,a.file_name,a.bytes,a.AUTOEXTENSIBLE--,a.status
      order by file_name;

      =============================================================
      HOW TO FIND CHILD TABLES
      ===========================================
      col column_name for a30
      col owner for a10
      set linesize 132

      select --a.table_name parent_table,
      b.owner,
      b.table_name child_table
      , a.constraint_name , b.constraint_name
      from dba_constraints a ,dba_constraints b
      where a.owner='LEIQA20091118'
      and a.constraint_name = b.r_constraint_name
      --and b.constraint_type = 'R'
      and a.constraint_type IN ('P','U')
      and a.table_name =upper('&tabname');

      List foreign keys and referenced table and columns:
      ======================================================
      SELECT DECODE(c.status,'ENABLED','C','c') t,
      SUBSTR(c.constraint_name,1,31) relation,
      SUBSTR(cc.column_name,1,24) columnname,
      SUBSTR(p.table_name,1,20) tablename
      FROM user_cons_columns cc, user_constraints p,
      user_constraints c
      WHERE c.table_name = upper('&table_name')
      AND c.constraint_type = 'R'
      AND p.constraint_name = c.r_constraint_name
      AND cc.constraint_name = c.constraint_name
      AND cc.table_name = c.table_name
      UNION ALL
      SELECT DECODE(c.status,'ENABLED','P','p') t,
      SUBSTR(c.constraint_name,1,31) relation,
      SUBSTR(cc.column_name,1,24) columnname,
      SUBSTR(c.table_name,1,20) tablename
      FROM user_cons_columns cc, user_constraints p,
      user_constraints c
      WHERE p.table_name = upper('PERSON')
      AND p.constraint_type in ('P','U')
      AND c.r_constraint_name = p.constraint_name
      AND c.constraint_type = 'R'
      AND cc.constraint_name = c.constraint_name
      AND cc.table_name = c.table_name
      ORDER BY 1, 4, 2, 3
      /

      List a child table's referential constraints and their associated parent table:
      ==============================================================

      SELECT t.owner CHILD_OWNER,
      t.table_name CHILD_TABLE,
      t.constraint_name FOREIGN_KEY_NAME,
      r.owner PARENT_OWNER,
      r.table_name PARENT_TABLE,
      r.constraint_name PARENT_CONSTRAINT
      FROM user_constraints t, user_constraints r
      WHERE t.r_constraint_name = r.constraint_name
      AND t.r_owner = r.owner
      AND t.constraint_type='R'
      AND t.table_name = <child_table_name>;

      parent tables:
      ================

      select constraint_name,constraint_type,r_constraint_name
      from dba_constraints
      where table_name ='TM_PAY_BILL'
      and constraint_type in ('R');

      select CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME from user_cons_columns where table_name='FS_FR_TERMINALLOCATION';

      select a.OWNER,a.TABLE_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE
      ,b.COLUMN_NAME,b.POSITION
      from dba_constraints a,dba_cons_columns b
      where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
      and a.TABLE_NAME=b.TABLE_NAME
      and a.table_name=upper('TM_GEN_INSTRUCTION')
      and a.constraint_type in ('P','U');


      select constraint_name,constraint_type,r_constraint_name
      from dba_constraints
      where table_name ='TM_PAY_BILL'
      and constraint_type in ('R');

      ===============================================
      HOW TO FIND INDEXES
      =====================================
      col column_name for a30
      col owner for a25

      select a.owner,a.index_name, --a.table_name,a.tablespace_name,
      b.column_name,b.column_position
      from dba_indexes a,dba_ind_columns b
      where a.owner='SCE'
      and a.index_name=b.index_name
      and a.table_name = upper('&tabname')
      order by a.index_name,b.column_position;

      col column_name for a40
      col index_owner for a15

      select index_owner,index_name,column_name,
      column_position from dba_ind_columns
      where table_owner= upper('VISILOGQA19') and table_name ='TBLTRANSACTIONGROUPMAIN';

      -- check for index on FK
      ===============================

      set linesize 121
      col status format a6
      col columns format a30 word_wrapped
      col table_name format a30 word_wrapped

      SELECT DECODE(b.table_name, NULL, 'Not Indexed', 'Indexed' ) STATUS, a.table_name, a.columns, b.columns from (
      SELECT SUBSTR(a.table_name,1,30) table_name,
      SUBSTR(a.constraint_name,1,30) constraint_name, MAX(DECODE(position, 1,
      SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position, 2,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 3,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 4,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 5,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 6,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 7,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 8,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 9,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,10,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,11,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,12,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,13,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,14,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,15,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,16,', '|| SUBSTR(column_name,1,30),NULL)) columns
      from user_cons_columns a, user_constraints b
      WHERE a.constraint_name = b.constraint_name
      AND constraint_type = 'R'
      GROUP BY SUBSTR(a.table_name,1,30), SUBSTR(a.constraint_name,1,30) ) a, (
      SELECT SUBSTR(table_name,1,30) table_name,
      SUBSTR(index_name,1,30) index_name, MAX(DECODE(column_position, 1,
      SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position, 2,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 3,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 4,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 5,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 6,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 7,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 8,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 9,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,10,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,11,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,12,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,13,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,14,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,15,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,16,', '||SUBSTR(column_name,1,30),NULL)) columns
      from user_ind_columns group by SUBSTR(table_name,1,30), SUBSTR(index_name,1,30) ) b
      where a.table_name = b.table_name (+) and b.columns (+) like a.columns || '%';

      ==================================================
      HOW TO FIND unique keys
      ===========================
      col column_name for a30
      col owner for a10
      set linesize 132

      select a.owner , --a.table_name,
      a.constraint_name,a.constraint_type,
      b.column_name,b.position
      from dba_constraints a, dba_cons_columns b
      where a.table_name = upper('&tabname')
      and a.constraint_name = b.constraint_name
      and a.constraint_type in ('P','U')
      and a.owner=b.owner
      order by a.owner,a.constraint_name,b.position;
      ==================================
      HOW TO FIND ROWlocks
      ======================
      col object_name for a30
      col terminal for a20
      set linesize 1000
      col spid for a10
      col osuser for a15


      select to_char(logon_time,'DD-MON-YYYY HH24:MI:SS'),OSUSER,--owner,
      s.sid, s.serial#,p.spid,
      s.terminal,l.locked_mode,o.object_name,l.ORACLE_USERNAME --,o.object_type
      from v$session s, dba_objects o,v$locked_object l, V$process p
      where o.object_id=l.object_id
      and s.sid=l.session_id
      and s.paddr=p.addr
      order by logon_time;


      SELECT OWNER||'.'||OBJECT_NAME AS Object, OS_USER_NAME, ORACLE_USERNAME,
      PROGRAM, NVL(lockwait,'ACTIVE') AS Lockwait,DECODE(LOCKED_MODE, 2,
      'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5,'SHARE ROW EXCLUSIVE',
      6, 'EXCLUSIVE', 'UNKNOWN') AS Locked_mode, OBJECT_TYPE, SESSION_ID, SERIAL#, c.SID
      FROM SYS.V_$LOCKED_OBJECT A, SYS.ALL_OBJECTS B, SYS.V_$SESSION c
      WHERE A.OBJECT_ID = B.OBJECT_ID AND C.SID = A.SESSION_ID
      ORDER BY Object ASC, lockwait DESC;

      SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
      id1, id2, lmode, request, type
      FROM V$LOCK
      WHERE (id1, id2, type) IN
      (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
      ORDER BY id1, request;


      find locks
      =====================
      set linesize 1000

      SELECT --osuser,
      a.username,a.serial#,a.sid,--a.terminal,
      sql_text
      from v$session a, v$sqltext b, V$process p
      where a.sql_address =b.address
      and a.paddr = p.addr
      and p.spid = '&os_pid'
      order by address, piece;

      select sql_text
      from V$sqltext_with_newlines
      where address =
      (select prev_sql_addr
      from V$session
      where username = :uname and sid = :snum) ORDER BY piece

      set pagesize 50000
      set linesize 30000
      set long 500000
      set head off

      select s.username su,s.sid,s.serial#,substr(sa.sql_text,1,540) txt
      from v$process p,v$session s,v$sqlarea sa
      where p.addr=s.paddr
      and s.username is not null
      and s.sql_address=sa.address(+)
      and s.sql_hash_value=sa.hash_value(+)
      and spid=&SPID;

      privileges
      ===========
      select * from dba_sys_privs where grantee = 'SCE';
      select * from dba_role_privs where grantee = 'SCE'
      select * from dba_sys_privs where grantee in ('CONNECT','APPL_CONNECT');

      Check high_water_mark_statistics
      ===================================
      select * from DBA_HIGH_WATER_MARK_STATISTICS;

      Multiple Blocksizes:
      =========================
      alter system set db_16k_cache_size=64m;
      create tablespace index_ts datafile '/data1/index_ts01.dbf' size 10240m blocksize 16384;

      11g default profiles:
      ========================
      alter profile default limit password_lock_time unlimited;
      alter profile default limit password_life_time unlimited;
      alter profile default limit password_grace_time unlimited;

      logfile switch over:
      ---------------------------
      select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARCHIVED,
      STATUS,to_char(FIRST_TIME,'DD-MON-YYYY HH24:MI:SS') switch_time
      from v$log;

      Temporary tablespace usage:
      ============================

      SELECT b.tablespace,
      ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
      a.sid||','||a.serial# SID_SERIAL,
      a.username,
      a.program
      FROM sys.v_$session a,
      sys.v_$sort_usage b,
      sys.v_$parameter p
      WHERE p.name = 'db_block_size'
      AND a.saddr = b.session_addr
      ORDER BY b.tablespace, b.blocks;


      SELECT A2.TABLESPACE, A2.SEGFILE#, A2.SEGBLK#, A2.BLOCKS,
      A1.SID, A1.SERIAL#, A1.USERNAME, A1.OSUSER, A1.STATUS
      FROM V$SESSION A1,V$SORT_USAGE A2 WHERE A1.SADDR = A2.SESSION_ADDR;

      ========================================
      ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

      Inactive sessions killing:
      -----------------------------
      SELECT 'ALTER SYSTEM KILL SESSION ' || '''' || SID || ',' ||
      serial# || '''' || ' immediate;' text
      FROM v$session
      WHERE status = 'INACTIVE'
      AND last_call_et > 86400
      AND username IN (SELECT username FROM DBA_USERS WHERE user_id>56);

      Procedure:
      -----------
      CREATE OR REPLACE PROCEDURE Inactive_Session_Cleanup AS
      BEGIN
      FOR rec_session IN (SELECT 'ALTER SYSTEM KILL SESSION ' || '''' || SID || ',' ||
      serial# || '''' || ' immediate' text
      FROM v$session
      WHERE status = 'INACTIVE'
      AND last_call_et > 43200
      AND username IN (SELECT username FROM DBA_USERS WHERE user_id>60)) LOOP
      EXECUTE IMMEDIATE rec_session.text;
      END LOOP;
      END Inactive_Session_Cleanup;
      /

      sequence using plsql
      =========================
      Declare
      v_next NUMBER;
      script varchar2(5000);
      BEGIN
      SELECT (MAX(et.dcs_code) + 1) INTO v_next FROM et_document_request et;
      script:= 'CREATE SEQUENCE et_document_request_seq
      MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH '||
           v_next || ' INCREMENT BY 1 CACHE 20';
      execute immediate script;
      end;

      ===========================
      --------------------------------
      Terminal wise session
      ---------------------------------
      select TERMINAL,count(*) from v$session
      group by TERMINAL;
      --------------------------------
      total sessions
      -------------------------
      select count(*) from v$session
      where TERMINAL not like '%UNKNOWN%'
      and TERMINAL is not null;
      ------------------------
      HOW TO FIND DUPLICATE TOKEN NUMBERS
      ===========================================
      select count(distinct a.token_number) dup
      from tm_pen_bill a,tm_pen_bill b
      where a.token_number = b.token_number
      and a.bill_number <> b.bill_number
      and a.token_number is not null;

      when Block Corruption occurs:
      --------------------------------
      select * from DBA_EXTENTS
      WHERE file_id = '13' AND block_id BETWEEN '44157' and '50649';

      select BLOCK_ID,SEGMENT_NAME,BLOCKS from dba_extents where FILE_ID='14'
      and BLOCK_ID like '%171%';

      select BLOCK_ID,SEGMENT_NAME,BLOCKS from dba_extents where FILE_ID='14'
      and SEGMENT_NAME = 'TEMP_TD_PAY_ALLOTMENT_NMC';

      DBVERIFY:
      -----------
      dbv blocksize=8192 file=users01.dbf log=dbv_users01.log

      ==============================================================

      DBMS_REPAIR:(Block Corruption)
      ------------
      exec dbms_repair.admin_tables(table_name=>'REPAIR_TABLE',table_type=>dbms_repair.repair_table,action=>dbms_repair.create_action,tablespace=>'USERS');

      variable v_corrupt_count number;
      exec dbms_repair.check_object('scott','emp',corrupt_count=>:v_corrupt_count);
      print v_corrupt_count;


      ==============================================================

      Password:
      -------------
      select login,substr(utl_raw.cast_to_varchar2(utl_raw.cast_to_varchar2(password)),1,30) password
      from mm_gen_user where active_flag = 'Y' and user_id=64 and LOGIN='GOPAL' ;

      CHARACTERSET
      ------------------
      select * from NLS_DATABASE_PARAMETERS;
      SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
      select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
      ==========================================================
      EXPLAIN PLAN TABLE QUERY
      ========================
      EXPLAIN PLAN SET STATEMENT_ID='5'
      FOR
      "DML STATEMENT"

      PLAN TABLE QUERY
      ===============================
      set linesize 1000
      set arraysize 1000
      col OBJECT_TYPE for a20
      col OPTIMIZER for a20
      col object_name for a30
      col options for a25


      select COST,OPERATION,OPTIONS,OBJECT_TYPE,
      OBJECT_NAME,OPTIMIZER
      --,ID,PARENT_ID,POSITION,CARDINALITY
      from plan_table
      where statement_id='&statement_id';

      Rman settings: disk formats
      %t represents a timestamp
      %s represents the backup set number
      %p represents the piece number


      The dbms_workload_repository.create_snapshot procedure creates a manual snapshot in the AWR as seen in this example:

      EXEC dbms_workload_repository.create_snapshot;

      Calculation of a table the size of the space occupied by
      ========================================================
      select owner, table_name,
      NUM_ROWS,
      BLOCKS * AAA/1024/1024 "Size M",
      EMPTY_BLOCKS,
      LAST_ANALYZED
      from dba_tables
      where table_name = 'XXX';

      Finding statement/s which use lots of shared pool memory:
      ==========================================================
      SELECT substr(sql_text,1,40) "SQL", count(*) , sum(executions) "TotExecs"
      FROM v$sqlarea
      WHERE executions < 5
      GROUP BY substr(sql_text,1,40)
      HAVING count(*) > 30
      ORDER BY 2;


      See a table size table
      =========================================
      select sum (bytes) / (1024 * 1024) as "size (M)" from user_segments
      where segment_name = upper ('& table_name');

      See a index size table
      =========================================
      select sum (bytes) / (1024 * 1024) as "size (M)" from user_segments
      where segment_name = upper ('& index_name');

      monitoring table space I / O ratio
      ====================================
      select B.tablespace_name name, B.file_name "file", A.phyrds pyr,
      A.phyblkrd pbr, A.phywrts pyw, A.phyblkwrt pbw
      from v $ filestat A, dba_data_files B
      where A.file # = B.file_id
      order by B.tablespace_name;

      monitor the file system I / O ratio
      =====================================
      select substr (C.file #, 1,2) "#", substr (C.name, 1,30) "Name",
      C.status, C.bytes, D.phyrds, D.phywrts
      from v $ datafile C, v $ filestat D
      where C.file # = D.file #;

      the hit rate monitor SGA
      =========================
      select a.value + b.value "logical_reads", c.value "phys_reads",
      round (100 * ((a.value + b.value)-c.value) / (a.value + b.value)) "BUFFER HIT RATIO"
      from v $ sysstat a, v $ sysstat b, v $ sysstat c
      where a.statistic # = 38 and b.statistic # = 39
      and c.statistic # = 40;

      monitoring SGA in the dictionary buffer hit ratio
      ==================================================
      select parameter, gets, Getmisses, getmisses / (gets + getmisses) * 100 "miss ratio",
      (1 - (sum (getmisses) / (sum (gets) + sum (getmisses ))))* 100 "Hit ratio"
      from v $ rowcache
      where gets + getmisses <> 0
      group by parameter, gets, getmisses;

      monitoring SGA shared cache hit ratio should be less than 1%
      =============================================================
      select sum (pins) "Total Pins", sum (reloads) "Total Reloads",
      sum (reloads) / sum (pins) * 100 libcache
      from v $ librarycache;

      select sum (pinhits-reloads) / sum (pins) "hit radio", sum (reloads) / sum (pins) "reload percent"
      from v $ librarycache;

      monitoring SGA in the redo log buffer hit ratio should be less than 1%
      =========================================================================
      SELECT name, gets, misses, immediate_gets, immediate_misses,
      Decode (gets, 0,0, misses / gets * 100) ratio1,
      Decode (immediate_gets + immediate_misses, 0,0,
      immediate_misses / (immediate_gets + immediate_misses) * 100) ratio2
      FROM v $ latch WHERE name IN ('redo allocation', 'redo copy');

      control memory and hard disk sort ratio, it is best to make it smaller than .10, an increase sort_area_size
      =============================================================================================================
      SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

      monitoring what the current database who are running SQL statements?
      ===================================================================
      SELECT osuser, username, sql_text from v $ session a, v $ sqltext b
      where a.sql_address = b.address order by address, piece;

      monitoring the dictionary buffer?
      =====================================
      SELECT (SUM (PINS - RELOADS)) / SUM (PINS) "LIB CACHE" FROM V $ LIBRARYCACHE;
      SELECT (SUM (GETS - GETMISSES - USAGE - FIXED)) / SUM (GETS) "ROW CACHE" FROM V $ ROWCACHE;
      SELECT SUM (PINS) "EXECUTIONS", SUM (RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V $ LIBRARYCACHE;
      The latter divided by the former, this ratio is less than 1%, close to 0% as well.

      SELECT SUM (GETS) "DICTIONARY GETS", SUM (GETMISSES) "DICTIONARY CACHE GET MISSES"
      FROM V $ ROWCACHE

      see the table a high degree of fragmentation?
      =================================================
      SELECT owner,segment_name table_name, COUNT (*) extents
      FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY owner,segment_name
      HAVING COUNT (*) = (SELECT MAX (COUNT (*)) FROM dba_segments GROUP BY segment_name);

      =======================================================================
      Fragmentation:
      =================
      select table_name,round((blocks*8),2)||'kb' "size"
      from user_tables
      where table_name = 'BIG1';

      Actual Data:
      =============
      select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
      from user_tables
      where table_name = 'BIG1';



      The establishment of an example data dictionary view to 8I
      =======================================================
      $ ORACLE_HOME / RDBMS / ADMIN / CATALOG.SQL

      The establishment of audit data dictionary view with an example to 8I
      ======================================================
      $ ORACLE_HOME / RDBMS / ADMIN / CATAUDIT.SQL

      To establish a snapshot view using the data dictionary to 8I Case
      =====================================================
      $ ORACLE_HOME / RDBMS / ADMIN / CATSNAP.SQL

      The table / index moving table space
      =======================================
      ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;
      ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;

      How can I know the system's current SCN number?
      =================================================
      select max (ktuxescnw * power (2, 32) + ktuxescnb) from x$ktuxe;

      Will keep a small table into the pool
      ======================================
      alter table xxx storage (buffer_pool keep);


      Check the permissions for each user
      ===================================
      SELECT * FROM DBA_SYS_PRIVS;


      =====================================================================
      Tablespace auto extend check:
      =================================

      col file_name for a50

      select FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files
      order by TABLESPACE_NAME;


      COL SEGMENT_NAME FOR A30

      select SEGMENT_NAME,TABLESPACE_NAME,BYTES,EXTENTS,INITIAL_EXTENT,
      NEXT_EXTENT,MAX_EXTENTS,PCT_INCREASE
      from user_segments
      where segment_name in ('TD_PAY_CHEQUE_PREPARED','TM_PAY_BILL','TD_PAY_PAYORDER');

      select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,PCT_INCREASE
      from dba_tablespaces;

      alter tablespace temp default storage(next 5m maxextents 20480 pctincrease 0);

      ALTER TABLE TD_PAY_CHEQUE_PREPARED
      default STORAGE ( NEXT 10 M maxextents 20480 pctincrease 0);

      Moving table from one tablespace to another
      ===============================================
      alter table KHAJANE.TEMP_TM_PAY_ALLOTMENT_NMC move tablespace khajane_ts;

      ==============================================
      for moving datafiles location:
      ========================================

      alter database rename file a to b;

      ======================================================================

      for logfile Clearence:
      -------------------------

      select * from global_name;

      col member for a50
      set linesize 132
      set trimspool on

      select 'alter database clear logfile ' || '''' || member || '''' || ';'
      from v$logfile where status ='STALE';

      logfile switch over:
      ---------------------------

      select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARCHIVED,
      STATUS,to_char(FIRST_TIME,'DD-MON-YYYY HH24:MI:SS') switch_time
      from v$log;