Oracle Primary key Foreign Key report

Oracle Primary key Foreign Key report
oracle pk key fk key
Oracle pk fk 
Listing Table Dependencies  ( Foreign Key References )

set feed off

set pagesize 10000
ttitle off
--
ttitle left'primary and foreign key relationsships' skip 2
set feed off
set pagesize 10000

--

column datum new_value datum noprint
column for_owner   format a5  heading 'table|owner'
column pri_tsname  format a10 heading 'tablespace'
column for_table   format a17 heading 'from|foreign|table'
column for_col     format a16 heading 'from|foreign|column'
column pri_table   format a17 heading 'to|primary|table'
column pri_col     format a16 heading 'to|primary|column'

break on for_owner skip 1

select
a.owner           for_owner, 
e.tablespace_name pri_tsname,
a.table_name      for_table,
c.column_name     for_col,
b.table_name      pri_table,
d.column_name     pri_col
from dba_constraints a,
dba_constraints      b,
dba_cons_columns     c,
dba_cons_columns     d,
dba_tables e
where a.owner  in ('SCOTT')   ------- <<<<<<<<<<<<<<
and a.r_constraint_name = b.constraint_name
and a.constraint_type = 'R'
and b.constraint_type = 'P'
and a.r_owner = b.owner
and a.constraint_name = c.constraint_name
and a.owner = c.owner
and a.table_name = c.table_name
and b.constraint_name = d.constraint_name
and b.owner = d.owner
and b.table_name = d.table_name
and b.table_name = e.table_name
order by a.owner,a.table_name;



primary and foreign key relationsships

                 from              from             to                to
table            foreign           foreign          primary           primary
owner tablespace table             column           table             column
----- ---------- ----------------- ---------------- ----------------- ----------------
SCOTT EXAMPLE    ORDER_LINES       ORDER_ID         ORDERS            ID
      USERS      ORDER_LINES       ORDER_ID         ORDERS            ID
      SYSTEM     ORDER_LINES       ORDER_ID         ORDERS            ID



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


CLEAR COLUMNS
CLEAR BREAK
SET PAUSE ON
SET FEEDBACK OFF
SET PAGESIZE 20
COLUMN "This Table References Fk" FORMAT A30
COLUMN "This Table Pk" FORMAT A30
BREAK ON "This Table References Fk" SKIP 2

SPOOL TAB_DEPEND.txt
SELECT A.TABLE_NAME "This Table References Fk", DECODE( A.R_OWNER, A.OWNER, B.TABLE_NAME, A.R_OWNER || '.' || B.TABLE_NAME ) "This Table Pk"
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B
WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.CONSTRAINT_TYPE ='R'
AND B.CONSTRAINT_TYPE IN ( 'U', 'P' )
GROUP BY A.TABLE_NAME,DECODE( A.R_OWNER, A.OWNER, B.TABLE_NAME,A.R_OWNER || '.' || B.TABLE_NAME )
ORDER BY 1, 2
/

SQL> connect scott/tiger

Connected.
SQL> ed pkfk1

SQL> @pkfk1

primary and foreign key relationsships

This Table References Fk       This Table Pk
------------------------------ ------------------------------
ORDER_LINES                    ORDERS

Oracle Shell Scripts for active session on unix

active session info on unix prompt


sh psora.sh
====================


##!/bin/sh
# Script Name: psora
export ORACLE_HOME=/opt/app/oracle/product/11.2
export ORACLE_SID=orcl
sqlplus / as sysdba <set pagesize 30
set heading off
set feedback off
select 'Database is ', name from v\$database;
set heading on
prompt
prompt All currently running processes
prompt ===============================
set pagesize 60
column sid format 999 heading "SID"
column Typ format a3
column process format a9 heading "Clnt PID"
column spid format a5 heading "Serv PID"
column username format a10 heading "Ora User"
column osuser format a8 heading "OS User"
column term format a7 heading "tty"
column stat format a2 heading "ST"
column lw format a2 heading "LK"
column com format a8 heading "Command"
column mach format a12 heading "Host"
column latchw format a2 heading "LA"
select s.sid,
nvl(decode(s.sql_hash_value,0,' ','*'),'?')||nvl(s.process,'?') process,
nvl(p.spid,'?') spid,
s.username username, nvl(substr(s.osuser,1,8),'?') osuser,
replace(replace(nvl(substr(s.terminal,1,7),'?'),'PC',''),'tty','') term,
decode(s.status,'ACTIVE','R','INACTIVE','W','KILLED','Z') stat,
decode(s.lockwait,NULL,'N','Y') lw,
nvl(decode(s.command,1,'CRT TABL',2,'INSERT',3,'SELECT',4,'CRT CLST',
5,'ALT CLST',6,'UPDATE',7,'DELETE',8,'DROP',9,'CRT INDX',
10,'DRP INDX',11,'ALT INDX',12,'DRP TABL',15,'ALT TABL',
17,'GRANT',18,'REVOKE',19,'CRT SYNM',20,'DRP SYNM',21,
'CRT VIEW',22,'DRP VIEW',26,'LOC TBLE',27,'NOTHING',28,
'RENAME',29,'COMMENT',30,'AUDIT',31,'NOAUDIT',32,'CRT XTDB',
33,'DRP XTDB',34,'CRT DBSE',35,'ALT DBSE',36,'CRT RSEG',
37,'ALT RSEG',38,'DRP RSEG',39,'CRT TBSP',40,'ALT TBSP',
41,'DRP TBSP',42,'ALT SESS',43,'ALT USER',44,'COMMIT',
45,'ROLLBACK',46,'SVEPOINT','IDLE'),'?') com,
substr(nvl(s.machine,'?'),1,12) mach,
decode(p.latchwait,NULL,'N','Y') latchw,
decode(least(instr(p.program,'TCP'),1),1,'TCP',
decode(least(instr(p.program,'TNS'),1),1,'TNS',
decode(least(instr(p.program,'Pip'),1),1,'Pip',' '))) Type
from v\$session s, v\$process p
where addr = paddr
and p.background is NULL
union
select s.sid,
nvl(decode(s.sql_hash_value,0,' ','*'),'?')||nvl(s.process,'?') process,
nvl(p.spid,'?') spid,
p.username username, nvl(substr(s.osuser,1,8),'?') osuser,
replace(replace(substr(p.terminal,1,7),'PC',''),'tty','') term,
decode(s.status,'ACTIVE','R','INACTIVE','W','KILLED','Z') stat,
decode(s.lockwait,NULL,'N','Y') lw,
nvl(decode(s.command,1,'CRT TABL',2,'INSERT',3,'SELECT',4,'CRT CLST',
5,'ALT CLST',6,'UPDATE',7,'DELETE',8,'DROP',9,'CRT INDX',
10,'DRP INDX',11,'ALT INDX',12,'DRP TABL',15,'ALT TABL',
17,'GRANT',18,'REVOKE',19,'CRT SYNM',20,'DRP SYNM',21,
'CRT VIEW',22,'DRP VIEW',26,'LOC TBLE',27,'NOTHING',28,
'RENAME',29,'COMMENT',30,'AUDIT',31,'NOAUDIT',32,'CRT XTDB',
33,'DRP XTDB',34,'CRT DBSE',35,'ALT DBSE',36,'CRT RSEG',
37,'ALT RSEG',38,'DRP RSEG',39,'CRT TBSP',40,'ALT TBSP',
41,'DRP TBSP',42,'ALT SESS',43,'ALT USER',44,'COMMIT',
45,'ROLLBACK',46,'SVEPOINT'),'?') com,
substr(nvl(s.machine,'?'),1,12) mach,
decode(p.latchwait,NULL,'N','Y') latchw,
decode(least(instr(p.program,'TCP'),1),1,'TCP',
decode(least(instr(p.program,'TNS'),1),1,'TNS',
decode(least(instr(p.program,'Pip'),1),1,'Pip',' '))) Type
from v\$session s, v\$process p
where addr = paddr
and p.background is not NULL
order by 1;
prompt
prompt Rollback Segment Usage
prompt ======================
column uname format a30
column usn format 99
column sid format 999
column xacts format 9999
column extents format 999999
column extends format 99999
column waits format 9999
column wraps format 9999
column shrnk format 9999
column name format a12
column osusr format a6
SELECT r.usn, r.name uname, s.osuser osusr, s.sid, x.extents, x.xacts, x.extends, x.waits, x.shrinks shrnk, x.wraps, x.writes
FROM v\$rollstat X,
v\$rollname R,
v\$session S,
v\$transaction T
WHERE t.addr = s.taddr (+)
AND x.usn (+) = r.usn
AND t.xidusn (+) = r.usn
ORDER BY r.usn;
exit;
eof1


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



SID Clnt PID Serv Ora User OS User tty ST LK Command Host LA TYP
---- --------- ----- ---------- -------- ------- -- -- -------- ------------ -- ---
2 10163 10163 oracle oracle UNKNOWN R N ? apt-amd-02 N
3 10165 10165 oracle oracle UNKNOWN R N ? apt-amd-02 N
4 10169 10169 oracle oracle UNKNOWN R N ? apt-amd-02 N
5 10171 10171 oracle oracle UNKNOWN R N ? apt-amd-02 N
6 10173 10173 oracle oracle UNKNOWN R N ? apt-amd-02 N
7 10175 10175 oracle oracle UNKNOWN R N ? apt-amd-02 N
8 10177 10177 oracle oracle UNKNOWN R N ? apt-amd-02 N
9 10179 10179 oracle oracle UNKNOWN R N ? apt-amd-02 N
10 10181 10181 oracle oracle UNKNOWN R N ? apt-amd-02 N
11 10183 10183 oracle oracle UNKNOWN R N ? apt-amd-02 N
12 10185 10185 oracle oracle UNKNOWN R N ? apt-amd-02 N
13 10187 10187 oracle oracle UNKNOWN R N ? apt-amd-02 N
14 10189 10189 oracle oracle UNKNOWN R N ? apt-amd-02 N
15 10191 10191 oracle oracle UNKNOWN R N ? apt-amd-02 N
16 10193 10193 oracle oracle UNKNOWN R N ? apt-amd-02 N
18 10257 10257 oracle oracle UNKNOWN R N ? apt-amd-02 N
20 10263 10263 oracle oracle UNKNOWN R N ? apt-amd-02 N
21 10265 10265 oracle oracle UNKNOWN R N ? apt-amd-02 N
22 10267 10267 oracle oracle UNKNOWN R N ? apt-amd-02 N
27 10301 10301 oracle oracle UNKNOWN R N ? apt-amd-02 N
31 10321 10321 oracle oracle UNKNOWN R N ? apt-amd-02 N
32 *19999 20000 SYS oracle ? R N SELECT apt-amd-02 N TNS
35 10329 10329 oracle oracle UNKNOWN R N ? apt-amd-02 N
36 10323 10323 oracle oracle UNKNOWN R N ? apt-amd-02 N
39 10731 10731 oracle oracle UNKNOWN R N ? apt-amd-02 N
50 19564 19564 oracle oracle UNKNOWN R N ? apt-amd-02 N
SQL>

SQL> Rollback Segment Usage

SQL> ======================


USN UNAME OSUSR SID EXTENTS XACTS EXTENDS WAITS SHRNK WRAPS WRITES
--- ------------------------------ ------ ---- ------- ----- ------- ----- ----- ----- ----------
0 SYSTEM 6 0 0 0 0 0 5864
21 _SYSSMU21_1694229119$ 3 0 50 3 17 104 37428688
22 _SYSSMU22_1205666354$ 3 0 43 6 18 113 40558478
23 _SYSSMU23_3748597963$ 3 0 62 2 17 119 59366818
24 _SYSSMU24_2622248712$ 3 0 44 4 16 98 41052052
25 _SYSSMU25_4160302927$ 4 0 47 4 19 109 33633816
26 _SYSSMU26_98525116$ 4 0 101 5 22 200 37865310
27 _SYSSMU27_1850131278$ 3 0 61 4 19 122 48728572
28 _SYSSMU28_855357226$ 5 0 65 4 19 119 41685458
29 _SYSSMU29_2316658341$ 3 0 83 8 21 153 51136230
30 _SYSSMU30_3387177988$ 3 0 58 6 16 115 41442164

Oracle role Info

@roleinfo DBA
@roleinfo SCOTT





-- -------------------
-- http://www.allenhayden.com/cgi/getdoc.pl?file=ascr74.htm from this site
-- roleup.sql
--
-- Creates a graphical list of all roles
-- that a certain role/user has been assigned.
--
-- Modifications:
-- 08-Sep-98 SEC Created
--
-- Usage:
-- @roleup {ROLE/USER_NAME}
--
-- Notes:
-- Must have select on DBA views, and create table privilege.
--
-- --------------------

prompt Examining the data dictionary. Please wait....

set heading off echo off verify off term off feedback off

whenever sqlerror stop 1;

-- temp table - essentially a reverse of dba_sys_privs, to
-- allow CONNECT BY operations, working from the bottom up.
create table user_dba_role_privs
(granted_role varchar2(30),
grantee varchar2(30))
storage (initial 100k next 200k);

insert into user_dba_role_privs
select grantee, granted_role
from dba_role_privs;

set heading on term on feedback on

column role_level format a50 heading "Role Level"

define role_parameter=&1;
prompt Roles for user/role &role_parameter : ;

SELECT LPAD(' ',2*(LEVEL-1)) || grantee role_level
FROM user_dba_role_privs
START WITH granted_role = upper('&role_parameter' )
CONNECT BY PRIOR grantee = granted_role
/

set heading off echo off verify off term off feedback off
drop table user_dba_role_privs;
set heading on term on feedback on


-- -------------------
--
-- roledown.sql
--
-- Creates a graphical list of all roles/users
-- under a certain role.
--
-- Modifications:
-- 08-Sep-98 SEC Created
--
-- Usage:
-- @roledown {ROLE_NAME}
--
-- Notes:
-- Must have select on DBA views, and create table privilege.
--
-- --------------------

prompt Examining the data dictionary. Please wait....

set heading off echo off verify off term off feedback off

whenever sqlerror stop 1;

-- temp table - essentially a copy of dba_sys_privs,
-- to allow CONNECT BY operations, from top down.
create table user_dba_role_privs
storage (initial 100k next 200k)
as select * from dba_role_privs
where rownum <1;

insert into user_dba_role_privs
select * from dba_role_privs;

def role_parameter=&1;
column role_level format a50 heading "Role Level"

prompt Roles/users under role &role_parameter : ;

set heading on term on feedback on

SELECT LPAD(' ',2*(LEVEL-1)) || grantee role_level
FROM user_dba_role_privs
START WITH granted_role = upper('&role_parameter')
CONNECT BY PRIOR grantee = granted_role
/

set heading off term off feedback off
drop table user_dba_role_privs;
set heading on term on feedback on

-- -------------------
--
-- usp.sql (User System Privileges)
--
-- Creates list of all privileges assigned to a
-- user, whether granted directly, or through a role.
--
-- Modifications:
-- 08-Sep-98 SEC Created
--
-- Usage:
-- @usp {ROLE/USER_NAME}
--
-- Notes:
-- Must have select on DBA views, and create table privilege.
--
-- --------------------

prompt Examining the data dictionary. Please wait....


set heading off echo off verify off term off feedback off

whenever sqlerror stop 1;

create table user_dba_role_privs
(granted_role varchar2(30),
grantee varchar2(30))
storage (initial 100k next 200k);

insert into user_dba_role_privs
select grantee, granted_role
from dba_role_privs;

create table temp_dba_role_privs
(granted_role varchar2(30))
storage (initial 100k next 200k);

column role_level format a30

define role_parameter=&1;
prompt Roles for user/role &role_parameter : ;

insert into temp_dba_role_privs
SELECT grantee
FROM user_dba_role_privs
START WITH granted_role = upper('&role_parameter' )
CONNECT BY PRIOR grantee = granted_role
/

set heading on term on feedback on

select distinct privilege , 'Thru role '|| granted_role "Access Route"
from temp_dba_role_privs t , dba_sys_privs d
where t.granted_role = d.grantee
union
select privilege , 'Direct'
from dba_sys_privs
where grantee = upper('&role_parameter')
order by 1
/

set heading off echo off verify off term off feedback off
drop table user_dba_role_privs;
drop table temp_dba_role_privs;


set heading on term on feedback on

Oracle Database Support Matrix

Oracle Database Support Matrix
Oracle Support Matrix
Oracle latest version
=============


Read metalink 161818.1

Oracle Database (RDBMS) Releases Support Status Summary [ID 161818.1]

ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts [ID 880782.1]


Oracle 11gR1 Upgrade Companion [ID 601807.1]

Client / Server / Interoperability Support Between Different Oracle Versions [ID 207303.1]

Core Oracle Database Certification Information (Doc ID 1306539.1)

Certification Information for Oracle Database on Linux x86-64 (Doc ID 1304727.2) ,MOS Doc ID 742060.1