以下是我在一些网站上一点点节选出来的！我还会继续整理！先来一部分！大家觉得好， 　　网站太多了请到www.orakle.com各网站的索引都有！ 　　Oracle DBA 常用 Script 　　1.Description: This script will display the active user 　　and the rollback segment being used in the database. 　　Code: 　　column rr heading 'RB Segment' format a18 　　column us heading 'Username' format a15 　　column os heading 'OS User' format a10 　　column te heading 'Terminal' format a10 　　SELECT r.name rr, 　　nvl(s.username,'no transaction') us, 　　s.osuser os, 　　s.terminal te 　　FROM 　　v$lock l, 　　v$session s, 　　v$rollname r 　　WHERE 　　l.sid = s.sid(+) AND 　　trunc(l.id1/65536) = r.usn AND 　　l.type = 'TX' AND 　　l.lmode = 6 　　ORDER BY r.name 　　/ 　　2. Description: When you connect to sqlplus, you see the the following sql prompt. SQL> 　　By using this sql in the glogin.sql, you will see a prompt similar to the following: 　　SCOTT@DB-01> 　　Code: 　　The following code works on Oracle 8i (8.1.5, 8.1.6, 8.1.7). 　　You have to insert the following line of code in glogin.sql which is usually 　　$ORACLE_HOME/sqlplus/admin 　　set termout off 　　set echo off 　　define X=NotConnected 　　define Y=DBNAME 　　Column Usr New_Value X 　　Column DBName New_Value Y 　　Select SYS_CONTEXT('USERENV','SESSION_USER') Usr From Dual; 　　--- The following does not work in 8.1.5 but works --- in 8.1.6 or above 　　Select SYS_CONTEXT('USERENV','DB_NAME') DBNAME From Dual; 　　--- If you are using 8.1.5, use this . 　　Select Global_Name DBNAME from Global_Name; 　　set termout on 　　set sqlprompt '&X@&Y> ' 　　Please note that this method will work only when you make a new sql plus session because when you make a new sql plus session, then only glogin.sql is 　　3. Description: I got sick and tired of not having bitwise functions for numbers in Oracle so I went ahead and wrote my own. 　　Code: 　　CREATE OR REPLACE PACKAGE bitwise IS 　　FUNCTION hexconverter (pi_number IN NUMBER) 　　RETURN CHAR; 　　FUNCTION hexconverter (pi_hexstr IN CHAR) 　　RETURN NUMBER; 　　FUNCTION bitand (pi_num1 IN NUMBER, pi_num2 IN NUMBER) 　　RETURN NUMBER; 　　FUNCTION bitor (pi_num1 IN NUMBER, pi_num2 IN NUMBER) 　　RETURN NUMBER; 　　FUNCTION bitxor (pi_num1 IN NUMBER, pi_num2 IN NUMBER) 　　RETURN NUMBER; 　　END bitwise; 　　/ 　　CREATE OR REPLACE PACKAGE BODY bitwise 　　IS 　　FUNCTION numtohexchar (pi_number IN NUMBER) 　　RETURN CHAR 　　IS 　　v_hextoreturn CHAR (1); 　　BEGIN 　　IF pi_number = 0 THEN 　　v_hextoreturn := '0'; 　　ELSIF pi_number = 1 THEN 　　v_hextoreturn := '1'; 　　ELSIF pi_number = 2 THEN 　　v_hextoreturn := '2'; 　　ELSIF pi_number = 3 THEN 　　v_hextoreturn := '3'; 　　ELSIF pi_number = 4 THEN 　　v_hextoreturn := '4'; 　　ELSIF pi_number = 5 THEN 　　v_hextoreturn := '5'; 　　ELSIF pi_number = 6 THEN 　　v_hextoreturn := '6'; 　　ELSIF pi_number = 7 THEN 　　v_hextoreturn := '7'; 　　ELSIF pi_number = 8 THEN 　　v_hextoreturn := '8'; 　　ELSIF pi_number = 9 THEN 　　v_hextoreturn := '9'; 　　ELSIF pi_number = 10 THEN 　　v_hextoreturn := 'A'; 　　ELSIF pi_number = 11 THEN 　　v_hextoreturn := 'B'; 　　ELSIF pi_number = 12 THEN 　　v_hextoreturn := 'C'; 　　ELSIF pi_number = 13 THEN 　　v_hextoreturn := 'D'; 　　ELSIF pi_number = 14 THEN 　　v_hextoreturn := 'E'; 　　ELSIF pi_number = 15 THEN 　　v_hextoreturn := 'F'; 　　ELSE 　　raise_application_error (-20000, 'Invalid value', TRUE); 　　END IF; 　　RETURN v_hextoreturn; 　　END numtohexchar; 　　FUNCTION hexchartonum (pi_hexchar IN CHAR) 　　RETURN NUMBER 　　IS 　　v_numtoreturn NUMBER (2); 　　BEGIN 　　IF pi_hexchar = '0' THEN 　　v_numtoreturn := 0; 　　ELSIF pi_hexchar = '1' THEN 　　v_numtoreturn := 1; 　　ELSIF pi_hexchar = '2' THEN 　　v_numtoreturn := 2; 　　ELSIF pi_hexchar = '3' THEN 　　v_numtoreturn := 3; 　　ELSIF pi_hexchar = '4' THEN 　　v_numtoreturn := 4; 　　ELSIF pi_hexchar = '5' THEN 　　v_numtoreturn := 5; 　　ELSIF pi_hexchar = '6' THEN 　　v_numtoreturn := 6; 　　ELSIF pi_hexchar = '7' THEN 　　v_numtoreturn := 7; 　　ELSIF pi_hexchar = '8' THEN 　　v_numtoreturn := 8; 　　ELSIF pi_hexchar = '9' THEN 　　v_numtoreturn := 9; 　　ELSIF pi_hexchar = 'A' THEN 　　v_numtoreturn := 10; 　　ELSIF pi_hexchar = 'B' THEN 　　v_numtoreturn := 11; 　　ELSIF pi_hexchar = 'C' THEN 　　v_numtoreturn := 12; 　　ELSIF pi_hexchar = 'D' THEN 　　v_numtoreturn := 13; 　　ELSIF pi_hexchar = 'E' THEN 　　v_numtoreturn := 14; 　　ELSIF pi_hexchar = 'F' THEN 　　v_numtoreturn := 15; 　　ELSE 　　raise_application_error (-20000, 'Invalid value', TRUE); 　　END IF; 　　RETURN v_numtoreturn; 　　END hexchartonum; 　　FUNCTION hexconverter (pi_number IN NUMBER) 　　RETURN CHAR 　　IS 　　i NUMBER; 　　v_digit NUMBER (2); 　　v_hexstr VARCHAR2 (16); 　　BEGIN 　　v_hexstr := ; 　　FOR i IN REVERSE 0 .. 15 　　LOOP 　　v_digit := MOD (TRUNC (pi_number / POWER (16, i)), 16); 　　v_hexstr := v_hexstr || numtohexchar (v_digit); 　　END LOOP; 　　RETURN v_hexstr; 　　END hexconverter; 　　FUNCTION hexconverter (pi_hexstr IN CHAR) 　　RETURN NUMBER 　　IS 　　i NUMBER; 　　v_digit NUMBER (2); 　　v_numtoreturn NUMBER; 　　v_hexstr16 CHAR (16); 　　BEGIN 　　v_hexstr16 := LPAD (pi_hexstr, 16, '0'); 　　v_numtoreturn := 0; 　　FOR i IN 0 .. 16 　　LOOP 　　V_digit := hexchartonum (SUBSTR (v_hexstr16, i, 1)); 　　v_numtoreturn := v_numtoreturn + v_digit * POWER (16, 16 - i); 　　END LOOP; 　　RETURN v_numtoreturn; 　　END hexconverter; 　　FUNCTION bitand (pi_num1 IN NUMBER, pi_num2 IN NUMBER) 　　RETURN NUMBER 　　IS 　　v_hex1 CHAR (16); 　　v_hex2 CHAR (16); 　　v_raw1 RAW (8); 　　v_raw2 RAW (8); 　　v_rawresult RAW (8); 　　v_hexresult VARCHAR2 (16); 　　v_numresult NUMBER; 　　BEGIN 　　v_hex1 := hexconverter (pi_num1); 　　v_hex2 := hexconverter (pi_num2); 　　v_raw1 := HEXTORAW (v_hex1); 　　v_raw2 := HEXTORAW (v_hex2); 　　v_rawresult := UTL_RAW.bit_and (v_raw1, v_raw2); 　　v_hexresult := RAWTOHEX (v_rawresult); 　　v_numresult := hexconverter (v_hexresult); 　　RETURN v_numresult; 　　END bitand; 　　FUNCTION bitor (pi_num1 IN NUMBER, pi_num2 IN NUMBER) 　　RETURN NUMBER 　　IS 　　v_hex1 CHAR (16); 　　v_hex2 CHAR (16); 　　v_raw1 RAW (8); 　　v_raw2 RAW (8); 　　v_rawresult RAW (8); 　　v_hexresult VARCHAR2 (16); 　　v_numresult NUMBER; 　　BEGIN 　　v_hex1 := hexconverter (pi_num1); 　　v_hex2 := hexconverter (pi_num2); 　　v_raw1 := HEXTORAW (v_hex1); 　　v_raw2 := HEXTORAW (v_hex2); 　　v_rawresult := UTL_RAW.bit_or (v_raw1, v_raw2); 　　v_hexresult := RAWTOHEX (v_rawresult); 　　v_numresult := hexconverter (v_hexresult); 　　RETURN v_numresult; 　　END bitor; 　　FUNCTION bitxor (pi_num1 IN NUMBER, pi_num2 IN NUMBER) 　　RETURN NUMBER 　　IS 　　v_hex1 CHAR (16); 　　v_hex2 CHAR (16); 　　v_raw1 RAW (8); 　　v_raw2 RAW (8); 　　v_rawresult RAW (8); 　　v_hexresult VARCHAR2 (16); 　　v_numresult NUMBER; 　　BEGIN 　　v_hex1 := hexconverter (pi_num1); 　　v_hex2 := hexconverter (pi_num2); 　　v_raw1 := HEXTORAW (v_hex1); 　　v_raw2 := HEXTORAW (v_hex2); 　　v_rawresult := UTL_RAW.bit_xor (v_raw1, v_raw2); 　　v_hexresult := RAWTOHEX (v_rawresult); 　　v_numresult := hexconverter (v_hexresult); 　　RETURN v_numresult; 　　END bitxor; 　　END bitwise; 　　/ 　　4. Description: This Script search for Oracle error messages in last 100 lines in the alert log file , send a email message to concerned and keep log to a 　　You should pass name of ORACLE_SID as a parameter. 　　For eg: Ck_alerlog FINL 　　Code: 　　#!/usr/bin/sh 　　# Script Type: Shell (Bourne) 　　# 　　# Script name: ck_alertlog.sh 　　# 　　# Date: Thu 06 06 2000 　　# Author: Thomas Kuruvilla 　　# Comments: Script checks last 100 lines of 　　# the alert log for specific 　　# Oracle errors, then pages or e-mails depending #on the error. 　　# Parameter: ORACLE_SID 　　# ------------------- 　　# Revision Log 　　# 　　# 00/00/00 : Name of modifier - description 　　#of Modifications 　　# 　　#-------------------------------------------------- 　　#------------------------- 　　SID=$1 　　DIR=/usr/local/bin/dbabin 　　ORACLE_SID=$SID; export ORACLE_SID 　　ORACLE_HOME=`grep -v "^[#]" /var/opt/oracle/oratab|grep $ORACLE_SID|cut -d: 　　export ORACLE_HOME 　　cd $ORACLE_HOME/../.. 　　ALERT_DEST=`pwd` 　　ALERT_DEST=$ALERT_DEST/admin/$SID/bdump 　　LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH 　　PATH=$ORACLE_HOME/bin:$PATH; export PATH 　　COLLECTOR=`tail -100 $ALERT_DEST/alert_$SID.log |egrep "ORA-255 |ORA-255:|ORA-0255|ORA-214|ORA-214:|ORA-00214|ORA-270|ORA-27 0:|ORA-00270|ORA-272|ORA-272:|ORA-00272|ORA-600|ORA-600:|ORA -00600|ORA-1122|ORA-1122:|ORA-01122|ORA-1578|ORA1578:|ORA-01 578|ORA-1628|ORA-1628:|ORA-01628|ORA-1630|ORA-1630:|ORA-0163 0|ORA1631|ORA-1631:|ORA-01631|ORA-1632|ORA-1632:|ORA-01632|O RA-1650|ORA-1650:|ORA01650|ORA-1652|ORA-1652:|ORA-01652|ORA- 1653|ORA-1653:|ORA-01653|ORA-1654|ORA1654:|ORA-01654|ORA-165 　　for i in $COLLECTOR 　　do 　　ORAERR=`echo $i | grep ORA-...` 　　if [ $ORAERR ] then 　　i=`echo $i | awk ' 　　{ 　　print $1 　　}'` 　　y=`cat $DIR/tmpalert$SID.log|egrep -ch$i` 　　if [ $y -ge 2 ] then 　　continue 　　else 　　echo $i Paged at `date '+%m/%d/%y %H:%M'` >> $DIR/tmpalert$SID.log 　　echo "Oracle errors in alert log on $SID" | /usr/bin/mailx -s "check 　　fi; 　　fi; 　　--Done 　　5. Description: Creating a ascii text file of oracle table data with pipe 　　Code: 　　#!/bin/ksh 　　#First line in the .txt file is column names 　　USR=username/password 　　echo "set feedback off set pagesize 0 　　select 'XXTT' || table_name from user_tables ;" | sqlplus -s $USR | grep 　　while read TABL 　　do 　　STR="nothing" 　　STR1="nothing" 　　echo "desc $TABL " | sqlplus -s $USR | tail +3 | grep "^[A-Z,a-z]" | awk 　　while read COLUM 　　do 　　if [ "$STR" = "nothing" ] then 　　STR=$COLUM 　　STR1=$COLUM 　　else 　　STR="$STR || '|' || $COLUM" 　　STR1="$STR1|$COLUM" 　　fi 　　Done 　　echo "$STR1" > $TABL.txt 　　echo "set feedback off set pagesize 0 set linesize 2000 select $STR from $TABL;"| sqlplus -s $USR >> $TABL.txt 　　Done 　　6. Description: Maps out the database physically with sizes and paths of all the physical files .It will give the names and sizes of rollback segments also.. 　　Code: 　　spool dbmap.rpt 　　start title132.sql "Database Layout " "dbmap.sql" 　　prompt ================================ 　　prompt Tablespace/Datafile Listing 　　prompt ================================ 　　prompt 　　prompt 　　column "Location" format A60; 　　column "Tablespace Name" format A15; 　　column "Size(M)" format 999,990; 　　break on "Tablespace Name" skip 1 nodup; 　　compute sum of "Size(M)" on "Tablespace Name"; 　　SELECT tablespace_name "Tablespace Name", 　　file_name "Location", bytes/1048576"Size(M)" 　　FROM sys.dba_data_files 　　Order by tablespace_name; 　　Prompt 　　Prompt ================================ 　　Prompt Redo Log Listing 　　Prompt ================================ 　　Prompt 　　Prompt 　　column "Group" format 999; 　　column "File Location" format A50; 　　column "Bytes (M)" format 99,990; 　　break on "Group" skip 1 nodup; 　　Select a.group# "Group", b.member "File 　　Location", (a.bytes/1024) "Bytes (K)" 　　From v$log a, v$logfile b 　　Where a.group# = b.group# 　　Order by 1,2; 　　Prompt 　　Prompt ================================ 　　Prompt Control File Listing 　　Prompt ================================ 　　Prompt 　　Prompt 　　column name format A80 heading "CONTROL FILE 　　NAME" 　　column status format a10 heading "STATUS" 　　Select name,status 　　From v$controlfile; 　　Prompt 　　Prompt ================================ 　　Prompt Rollback Listing 　　Prompt ================================ 　　Prompt 　　Prompt 　　column "Segment Name" format A15; 　　column "Tablespace" format A15; 　　Column "Initial (M)" Format 99,990; 　　Column "Next (M)" Format 99,990; 　　column "Min Ext." FORMAT 9999; 　　column "Max Ext." FORMAT 99999999999; 　　column "Status" Format A7; 　　Select segment_name "Segment Name", 　　tablespace_name "Tablespace", 　　(initial_extent/1024)/1024 "Initial (M)", 　　(next_extent/1024)/1024 "Next (M)", 　　min_extents "Min Ext.", 　　max_extents "Max Ext.", 　　status "Status" 　　From sys.dba_rollback_segs 　　Order by tablespace_name, segment_name; 　　Spool Off; 　　7. Description: Detects possible next extent failures 　　Code: 　　Select 　　substr(sg.tablespace_name,1,30)Tablespace, 　　substr(sg.segment_name,1,30) Object, 　　sg.extents extents, 　　sg.next_extent next, 　　max(sp.bytes) available 　　From dba_free_space sp, dba_segments sg 　　Where sp.tablespace_name = sg.tablespace_name 　　Group by sg.tablespace_name, 　　sg.segment_name, sg.extents, sg.next_extent 　　Order by 1,2; 　　8. Description: Displays DB start time, and calculates how long the DB has 　　Code: 　　set head off pages 0 　　Select 　　'Current System time and date is '|| 　　To_Char(sysdate,'HH24:MI:SS " on " ddth 　　FMMonth YYYY')||chr(10)||'Database 　　'||NAME||' has been up since '|| 　　To_Char(To_Date(a.value||b.value,'JSSSSS') 　　,'HH24:MI:SS " on " ddth FMMonth YYYY') 　　||chr(10)||' ( Which is 　　'||Trunc(sysdate-to_date(a.value||b.value,'JSSSSS'))||' days '|| 　　(sysdate-to_date(a.value||b.value,'JSSSSS') 　　)-trunc(sysdate-to_date(a.value||b.value,'JSSSS' 　　)))),'SSSSS') 　　,'HH24 "hours" MI "mins and" SS "seconds )"') 　　from v$instance a,v$instance b,v$database 　　where a.key='STARTUP TIME - JULIAN' 　　and b.key='STARTUP TIME - SECONDS'; 　　9. Description: Displays the user and the text of the statement the user is 　　Code: 　　SELECT OSUSER,SERIAL#,SQL_TEXT 　　FROM V$SESSION, V$SQL 　　WHERE 　　V$SESSION.SQL_ADDRESS=V$SQL.ADDRESS AND V$SESSION.STATUS = 'ACTIVE'; 　　10. Description: Extract package and package body source from the database 　　Code: 　　COL SORT1 NOPRINT 　　COL SORT2 NOPRINT 　　COL SORT3 NOPRINT 　　COL SORT4 NOPRINT 　　BREAK ON SORT1 SKIP 1 　　set linesize 120 　　SET HEADING OFF 　　SET ECHO OFF 　　SET FEEDBACK OFF 　　SET PAGESIZE 0 　　SPOOL bldpack.sql 　　select 'set echo on ' from dual; 　　select 'spool bldpack.lst' from dual; 　　select 'Remark Build package definitions' from dual; 　　SELECT NAME SORT1, LINE SORT2, 　　'Create or Replace ' || TEXT 　　FROM USER_SOURCE 　　WHERE TYPE = 'PACKAGE' AND LINE = 1 　　UNION 　　SELECT NAME SORT1, LINE SORT2,TEXT 　　FROM USER_SOURCE 　　WHERE TYPE = 'PACKAGE' AND LINE > 1 　　UNION 　　SELECT NAME SORT1, 999999 SORT2, '/' 　　FROM USER_SOURCE 　　WHERE TYPE = 'PACKAGE' AND LINE =1 　　ORDER BY 1,2; 　　spool off 　　SPOOL bldpbdy.sql 　　select 'set echo on ' from dual; 　　select 'spool bldpbdy.lst' from dual; 　　select 'Remark Build package body definitions' from dual; 　　SELECT NAME SORT1, LINE SORT2,'Createor 　　Replace '||TEXT 　　FROM USER_SOURCE 　　WHERE TYPE = 'PACKAGE BODY' AND LINE = 1 　　UNION 　　SELECT NAME SORT1, LINE SORT2,TEXT 　　FROM USER_SOURCE 　　WHERE TYPE = 'PACKAGE BODY' AND LINE > 1 　　UNION 　　SELECT NAME SORT1, 999999 SORT2,'/' 　　FROM USER_SOURCE 　　WHERE TYPE = 'PACKAGE BODY' AND LINE = 1 　　ORDER BY 1,2; 　　spool off 　　SPOOL bldproc.sql 　　select 'set echo on ' from dual; 　　select 'spool bldproc.lst' from dual; 　　select 'Remark Build procedure definitions' from 　　dual; 　　SELECT NAME SORT1, LINE SORT2, 　　'Create or Replace ' || TEXT 　　FROM USER_SOURCE 　　WHERE TYPE = 'PROCEDURE' AND LINE = 1 　　UNION 　　SELECT NAME SORT1, LINE SORT2, TEXT 　　FROM USER_SOURCE 　　WHERE TYPE = 'PROCEDURE' AND LINE>1 　　UNION 　　SELECT NAME SORT1, 999999 SORT2,'/' 　　FROM USER_SOURCE 　　WHERE TYPE = 'PROCEDURE' AND LINE = 1 　　ORDER BY 1,2; 　　select 'Remark Build function definitions' from 　　dual; 　　SELECT NAME SORT1, LINE SORT2, 　　'Create or Replace ' || TEXT 　　FROM USER_SOURCE 　　WHERE TYPE = 'FUNCTION' AND LINE = 1 　　UNION 　　SELECT NAME SORT1, LINE SORT2, TEXT 　　FROM USER_SOURCE 　　WHERE TYPE = 'FUNCTION' AND LINE > 1 　　UNION 　　SELECT NAME SORT1, 999999 SORT2,'/' 　　FROM USER_SOURCE 　　WHERE TYPE = 'FUNCTION' AND LINE = 1 　　ORDER BY 1,2; 　　spool off 　　11. Description: Extract the code for views from the database 　　Code: 　　SET HEADING OFF 　　SET ECHO OFF 　　SET FEEDBACK OFF 　　SET PAGESIZE 0 　　set numwidth 10 　　SELECT uv.view_name SORT1, 0 SORT2, 0 SORT3, 　　0 SORT4, 　　'create or replace view '||uv.view_name ||' (' 　　from dba_views uv 　　WHERE uv.owner = upper('&2') 　　and uv.view_name = upper('&1') 　　union all 　　SELECT utc.view_name SORT1, utc.column_id 　　SORT2, 0 SORT3, 0 SORT4,utc.column_name 　　from dba_tab_columns 　　WHERE utc.owner = upper('&2') 　　and utc.table_name = upper('&1') 　　and utc.column_id = 1 　　union all 　　SELECT utc.view_name SORT1, utc.column_id 　　SORT2, 0 SORT3, 0 SORT4, 　　' , '||utc.column_name 　　from dba_tab_columns 　　WHERE utc.owner = upper('&2') 　　and utc.table_name = upper('&1') 　　and utc.column_id <> 1 　　SELECT uv.view_name SORT1,999 SORT2,0 　　SORT3,0 SORT4,' )' 　　from dba_views uv 　　WHERE uv.owner = upper('&2') 　　and uv.view_name = upper('&1') 　　ORDER BY 1, 2, 3, 4; 　　SELECT uv.text 　　from dba_views uv 　　WHERE uv.owner = upper('&2') 　　and uv.view_name = upper('&1'); 　　SELECT uv.view_name SORT1,999 SORT2,0 　　SORT3,0 SORT4,' ;' 　　from dba_views uv 　　WHERE uv.owner = upper('&2') 　　and uv.view_name = upper('&1'); 　　12. Description: This script takes a snapshot of v$filestats at the current time and saves it. It then waits 10 seconds and takes another snapshot and 　　Code: 　　col name for a50 　　set linesize 132 　　set pages 666 　　-- drop temporary table 　　drop table jh$filestats; 　　create table jh$filestats as 　　select file#, PHYBLKRD, PHYBLKWRT 　　from v$filestat; 　　prompt Waiting...... 　　exec dbms_lock.sleep(10); 　　prompt NOTE: Only the top 10 files... 　　select * from ( 　　select df.name, fs.phyblkrd - t.phyblkrd "Reads",fs.PHYBLKWRT - t.PHYBLKWRT "Writes",(fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) "Total IO" 　　from v$filestat fs, v$datafile df, jh$filestats t 　　where df.file# = fs.file# and t.file# = fs.file# 　　and (fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) > 0 　　order by "Total IO" desc ) 　　where rownum <= 10 　　/ 　　13. Description: Finds the two highest salaries from table "emp". 　　Code: 　　select a.empno,a.sal 　　from emp a 　　where 2> 　　( 　　select count(*) 　　from emp 　　where sal>a.sal 　　); 　　14. Description: Finds the two lowest salaries from table "emp". 　　Code: 　　select a.empno,a.sal 　　from emp a 　　where 2> 　　( 　　select count(*) 　　from emp 　　where sal　　) 　　15. Description: Find Child Tables for a given parent table. 　　Code: 　　set echo off 　　set verify off 　　accept xTable prompt 'Enter Table Name: ' 　　TTITLE LEFT 'Child Tables for the table: '&xTABLE 　　break on TABLE_NAME 　　SELECT B.TABLE_NAME, C.COLUMN_NAME, 　　C.POSITION 　　FROM USER_CONSTRAINTS A, 　　USER_CONSTRAINTS B, 　　USER_CONS_COLUMNS C 　　WHERE 　　A.CONSTRAINT_NAME= 　　B.R_CONSTRAINT_NAME 　　AND 　　A.TABLE_NAME = C.TABLE_NAME 　　AND 　　A.CONSTRAINT_NAME = C.CONSTRAINT_NAME 　　AND 　　A.TABLE_NAME = UPPER('&xTable') 　　ORDER BY B.TABLE_NAME, C.POSITION; 　　TTITLE LEFT 'Parent tables for the table: '&xTable 　　SELECT A.TABLE_NAME, C.COLUMN_NAME, 　　C.POSITION 　　FROM USER_CONSTRAINTS A, 　　USER_CONSTRAINTS B, 　　USER_CONS_COLUMNS C 　　WHERE 　　A.CONSTRAINT_NAME=B.R_CONSTRAINT_NAME 　　AND 　　B.TABLE_NAME = C.TABLE_NAME 　　AND 　　B.CONSTRAINT_NAME = C.CONSTRAINT_NAME 　　AND 　　B.TABLE_NAME = UPPER('&xTable') 　　ORDER BY A.TABLE_NAME, C.POSITION; 　　16. Description: Script prompts for a constraint name then it returns 　　Code: 　　-- displays constraint info 　　-- created by Noah Monsey 　　-- 02/02/2000 　　set lines 120 　　set serveroutput on size 100000 　　set scan on 　　clear buffer 　　set verify off 　　set feedback off 　　declare 　　l_constraint_name varchar2(30); 　　l_constraint_type varchar2(1); 　　l_table_name varchar2(30); 　　l_search_condition long; 　　l_related_constraint varchar2(30); 　　l_related_table varchar2(30); 　　l_rcolumn varchar2(30); 　　l_column varchar2(30); 　　l_query varchar2(400); 　　l_column_count integer; 　　cursor lc_constraint (p_constraint_name varchar2) is 　　select * 　　from dba_constraints 　　where constraint_name = p_constraint_name; 　　cursor lc_related_constraint(p_r_constraint_name varchar2) 　　is 　　select table_name from dba_constraints 　　where constraint_name = p_r_constraint_name; 　　cursor lc_columns (p_constraint_name varchar2) is 　　select column_name from dba_cons_columns 　　where constraint_name = p_constraint_name; 　　begin 　　dbms_output.enable(1000000); 　　for rec_constraint in 　　lc_constraint(upper('&constraint_name')) 　　loop 　　l_constraint_type := ; 　　l_table_name := ; 　　l_related_constraint := ; 　　l_search_condition := ; 　　l_constraint_type := 　　rec_constraint.constraint_type; 　　l_table_name := 　　rtrim(rec_constraint.table_name); 　　l_related_constraint := 　　rec_constraint.r_constraint_name; 　　l_search_condition := 　　rec_constraint.search_condition; 　　l_constraint_name := 　　rec_constraint.constraint_name; 　　l_column_count := 0; 　　dbms_output.put_line( 　　); 　　if l_constraint_type = 'P' then 　　dbms_output.put_line(l_constraint_name || ' is a primary key constraint on 　　open lc_columns(l_constraint_name); 　　fetch lc_columns into l_column; 　　dbms_output.put_line('Column ' || 　　l_column); 　　close lc_columns; 　　end if; 　　if l_constraint_type = 'U' then 　　dbms_output.put_line(l_constraint_name || ' is a unique constraint on table ' 　　for rec_columns in 　　lc_columns(l_constraint_name) 　　loop 　　dbms_output.put_line('Column ' || 　　rec_columns.column_name); 　　end loop; 　　end if; 　　if l_constraint_type = 'C' then 　　dbms_output.put_line(l_constraint_na 　　me || ' is a check constraint ' || 　　rtrim(l_table_name) || ' where '|| 　　l_search_condition); 　　end if; 　　if l_constraint_type = 'R' then 　　open 　　lc_related_constraint(l_related_constr 　　aint); 　　fetch lc_related_constraint into 　　l_related_table; 　　dbms_output.put_line('Table 　　'||l_table_name ||' referential 　　constraint '|| l_related_constraint ||' 　　failed because '); 　　l_query := ' select ' ; 　　l_column_count := 0; 　　open lc_columns(l_constraint_name); 　　fetch lc_columns into l_column; 　　l_query := l_query || ' ' || rtrim(l_column); 　　if l_column_count > 1 then 　　l_query := l_query || ','; 　　end if; 　　l_column_count := l_column_count +1; 　　dbms_output.put_line('Column'||l_column); 　　close lc_columns; 　　l_query := l_query || 'from'|| l_table_name || ' minus select ' ; 　　l_column_count := 0; 　　dbms_output.put_line('Table'|| l_related_table||' is missing related values 　　open 　　lc_columns(l_related_constraint); 　　fetch lc_columns into l_rcolumn; 　　l_query := l_query || ' ' || 　　rtrim(l_rcolumn); 　　if l_column_count > 1 then 　　l_query := l_query || ','; 　　end if; 　　l_column_count := l_column_count +1; 　　dbms_output.put_line('Column'||l_rcolumn); 　　close lc_columns; 　　l_query := l_query || ' from ' || rtrim(l_related_table) ||';'; 　　dbms_output.put_line(l_query); 　　close lc_related_constraint; 　　end if; 　　if l_constraint_type not in ('C','P','U','R') then 　　dbms_output.put_line(l_table_name||' 　　has a unknown constraint type'); 　　end if; 　　end loop; 　　end; 　　/ 　　set feedback on 　　17. Find out the duplicate records in a Table 　　Code: 　　SELECT primary_key FROM table_name 　　MINUS 　　( 　　SELECT DISTINCT primary_key 　　FROM table_name 　　) 　　You can also use * instead of the primary_key. 　　18. Description: This Script will help you to find out which Database you are 　　Connected to? If You are working on multiple Databases on different Servers 　　Code: 　　select distinct A.name DATABASE, 　　B.machine SERVER 　　from V$database A, V$session B 　　where b.username is 　　/