Oracle dba’s scripts
Posted on May 8, 2008
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 found in
$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 executed.
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 file.
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: -f2
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-270:|ORA-00270|ORA-272|ORA-272:|ORA-00272|ORA-600|ORA-600:|ORA-00600|ORA-1122|ORA-1122:|ORA-01122|ORA-1578|ORA1578:|ORA-01578|ORA-1628|ORA-1628:|ORA-01628|ORA-1630|ORA-1630:|ORA-01630|ORA1631|ORA-1631:|ORA-01631|ORA-1632|ORA-1632:|ORA-01632|ORA-1650|ORA-1650:|ORA01650|ORA-1652|ORA-1652:|ORA-01652|ORA-1653|ORA-1653:|ORA-01653|ORA-1654|ORA1654:|ORA-01654|ORA-1655|ORA-1655:|ORA-01655″`
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 aler_$SID.log” xxx@myorg.com;
fi;
fi;
–Done
5. Description: Creating a ascii text file of oracle table data with pipe delimited columns.
Code:
#!/bin/ksh
#First line in the .txt file is column names
USR=username/password
echo “set feedback off\n set pagesize 0\n
select ‘XXTT’ || table_name from user_tables ;” | sqlplus -s $USR | grep ‘^XXTT’ | cut -c5- |
while read TABL
do
STR=”nothing”
STR1=”nothing”
echo “desc $TABL\n” | sqlplus -s $USR | tail +3 | grep “^[A-Z,a-z]” | awk ‘{print $1}’ |
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\nset pagesize 0\nset linesize 2000\nselect $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
Having max(sp.bytes) < sg.next_extent
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 been up.
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 ‘||To_Char(To_Date(trunc(86400*(
(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 executing
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 reports on the delta.
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<a.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 information about the constraint.
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 := null;
l_table_name := null;
l_related_constraint := null;
l_search_condition := null;
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 table ‘ || l_table_name);
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 ‘ || l_table_name);
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 presently
Connected to? If You are working on multiple Databases on different Servers using SQL*Net or Net8.
Code:
select distinct A.name DATABASE,
B.machine SERVER
from V$database A, V$session B
where b.username is null
/
Popularity: 2% [?]
Related Posts:
» Filed Under Other
Comments
Leave a Reply