Everything to do with a table
Table info in detail
set linesize 155
set pagesize 60
set echo on feed on arraysize 1 LONG 5000 verify off
set linesize 140 echo off feed off
-- ========================
prompt
prompt
prompt Show the Table Structure
-- Show the Table Structure
col pos for 999 head "POS"
col data_type for A15
col pct_free format A4 heading "Null"
select column_name, data_type, data_length, nullable, column_id pos
from SYS.DBA_TAB_COLUMNS
where owner = upper('&&owner')
and table_name = upper('&&table')
order by column_id;
-- ========================
prompt
prompt
prompt Show Physical Attributes
-- Show Physical Attributes
col pct_free for 999 heading "%|Free"
col pct_increase for 999 heading "%|Incr"
col initial_extent for 999999999 heading "Init|Extent"
col next_extent for 9999999999999 heading "Next|Extent"
col max_extents for 9999999999 heading "Max|Ext"
col avg_row_len for 99999 heading "Avg|Row|Len"
SELECT PCT_FREE,
PCT_INCREASE,
INITIAL_EXTENT,
NEXT_EXTENT,
MAX_EXTENTS,
NUM_ROWS,
AVG_ROW_LEN
FROM SYS.DBA_TABLES
WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table');
-- ==============================
prompt
prompt
prompt Show the actual Maximum Size of a Row
-- Show the actual Maximum Size of a Row
col MaxRowSize for 999999999999
select sum(DATA_LENGTH) MaxRowSize
from sys.dba_tab_columns
where owner = upper('&&owner')
and table_name = upper('&&table');
-- ========================================================
prompt
prompt
prompt Show the Number of Physical EXTENTS that have been allocated Attributes
-- Show the Number of Physical EXTENTS that have been allocated Attributes
COL SEGMENT_NAME FORMAT A30 HEADING 'Table Name'
COL COUNTER FORMAT 9999999 HEADING 'Number Of Extents Used'
SELECT SEGMENT_NAME, COUNT(*) COUNTER
FROM SYS.DBA_EXTENTS
WHERE OWNER = upper('&&owner')
AND SEGMENT_NAME = upper('&&table')
GROUP BY SEGMENT_NAME;
COL TABSIZE FORMAT 999999999999 HEADING 'Table Size In Bytes'
--
-- =====================================
prompt
prompt
prompt Show the Physical SIZE IN BYTES of the TABLE
-- Show the Physical SIZE IN BYTES of the TABLE
SELECT SEGMENT_NAME, SUM(BYTES) TABSIZE
FROM SYS.DBA_EXTENTS WHERE
OWNER = upper('&&owner')
AND SEGMENT_NAME = upper('&&table')
GROUP BY SEGMENT_NAME;
-- =====================================================
-- GET ALL THE INDEX DETAILS
prompt
prompt
prompt Show all the indexes and their columns for this table
-- Show all the indexes and their columns for this table
COL OWNER FORMAT A8 heading "Index|Owner"
COL TABLE_OWNER FORMAT A8 heading "Table|Owner"
COL INDEX_NAME FORMAT A30 heading "Index Name"
COL COLUMN_NAME FORMAT A30 heading "Column Name"
COL COLUMN_POSITION FORMAT 9999 heading "Pos"
BREAK ON CONSTRAINT_NAME SKIP PAGE
SELECT IND.OWNER,
IND.TABLE_OWNER,
IND.INDEX_NAME,
IND.UNIQUENESS,
COL.COLUMN_NAME,
COL.COLUMN_POSITION
FROM SYS.DBA_INDEXES IND,
SYS.DBA_IND_COLUMNS COL
WHERE IND.TABLE_NAME = upper('&&table')
AND IND.TABLE_OWNER = upper('&&owner')
AND IND.TABLE_NAME = COL.TABLE_NAME
AND IND.OWNER = COL.INDEX_OWNER
AND IND.TABLE_OWNER = COL.TABLE_OWNER
AND IND.INDEX_NAME = COL.INDEX_NAME;
-- =========================================================
Prompt
Prompt
Prompt Display all the physical details of the Primary and Other Indexes for table
-- Display all the physical details of the Primary and Other Indexes for table
COL OWNER FOR A8 heading "Index|Owner"
COL TABLE_OWNER FOR A8 heading "Table|Owner"
COL INDEX_NAME FOR A30 heading "Index Name"
COL COLUMN_NAME FOR A30 heading "Column Name"
COL COLUMN_POSITION FOR 9999 heading "Pos"
COL PCT_FREE FOR 999 heading "%|Free"
COL PCT_INCREASE FORMAT 999 heading "%|Incr"
COL INITIAL_EXTENT FORMAT 999999999 heading "Init|Extent"
COL NEXT_EXTENT FORMAT 999999999 heading "Next|Extent"
COL MAX_EXTENTS FORMAT 9999999999 heading "Max|Ext"
SELECT IND.OWNER,IND.TABLE_OWNER,IND.INDEX_NAME,IND.UNIQUENESS,COL.COLUMN_NAME,COL.COLUMN_POSITION,IND.PCT_FREE,
IND.PCT_INCREASE,IND.INITIAL_EXTENT,IND.NEXT_EXTENT,IND.MAX_EXTENTS
FROM DBA_INDEXES IND,DBA_IND_COLUMNS COL
WHERE IND.TABLE_NAME = upper('&&table')
AND IND.TABLE_OWNER = upper('&&owner')
AND IND.TABLE_NAME = COL.TABLE_NAME
AND IND.OWNER = COL.INDEX_OWNER
AND IND.TABLE_OWNER = COL.TABLE_OWNER
AND IND.INDEX_NAME = COL.INDEX_NAME;
--
-- ====================================================================
-- GET ALL THE CONSTRAINT DETAILS
-- ====================================================================
prompt
prompt
prompt Show the Non-Foreign Keys Constraints on this table
-- Show the Non-Foreign Keys Constraints on this table
COL OWNER FOR A9 heading "Owner"
COL constraint_name for A22 heading "Constraint Name"
COL r_constraint_name for A22 heading "Referenced|Constraint Name"
COL DELETE_RULE FOR A9 heading "DelRule"
COL TABLE_NAME FOR A18 heading "Table Name"
COL COLUMN_NAME FOR A30 heading "Column Name"
COLUMN POSITION FOR 9999 heading "Pos"
break on constraint_name skip page
SELECT COL.OWNER,COL.CONSTRAINT_NAME,COL.COLUMN_NAME,COL.POSITION,
DECODE (CON.CONSTRAINT_TYPE,'P','primary','R','foreign','U','unique','C','check') "Type"
FROM DBA_CONS_COLUMNS COL,DBA_CONSTRAINTS CON
WHERE COL.OWNER = upper('&&owner')
AND COL.TABLE_NAME = upper('&&table')
AND CONSTRAINT_TYPE <> 'R'
AND COL.OWNER = CON.OWNER
AND COL.TABLE_NAME = CON.TABLE_NAME
AND COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME
ORDER BY COL.CONSTRAINT_NAME, COL.POSITION;
-- ====================================================================
prompt
prompt
prompt Show the Foreign Keys on this table pointing at other tables Primary key fields for referential integreity purposes
-- Show the Foreign Keys on this table pointing at other tables Primary key fields for referential integreity purposes
col "Ref Tab" for a22
col "Ref Const" for a22
col "Constraint Name" for a22
SELECT CON.CONSTRAINT_NAME "Constraint Name", CON.R_CONSTRAINT_NAME,
CON.DELETE_RULE, COL.COLUMN_NAME, COL.POSITION,
-- CON1.OWNER,
CON1.TABLE_NAME "Ref Tab",CON1.CONSTRAINT_NAME "Ref Const"
-- COL1.COLUMN_NAME "Ref Column",
-- COL1.POSITION
--FROM DBA_CONS_COLUMNS COL,
FROM DBA_CONSTRAINTS CON1,DBA_CONS_COLUMNS COL,DBA_CONSTRAINTS CON
WHERE CON.OWNER = upper('&&owner')
AND CON.TABLE_NAME = upper('&&table')
AND CON.CONSTRAINT_TYPE = 'R'
AND COL.OWNER = CON.OWNER
AND COL.TABLE_NAME = CON.TABLE_NAME
AND COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME
AND CON1.OWNER = CON.OWNER
AND CON1.CONSTRAINT_NAME = CON.R_CONSTRAINT_NAME
AND CON1.CONSTRAINT_TYPE IN ( 'P', 'U' );
-- ================================================================
prompt
prompt
prompt Show the Foreign Keys pointing at this table via the recursive call to the constraints table
-- Show the Foreign Keys pointing at this table via the recursive call to the constraints table
col "Constraint Name" for a35
col TABLE_NAME for A25 heading "Table Name"
SELECT CON1.OWNER||'.'||CON1.TABLE_NAME||'-'||CON1.CONSTRAINT_NAME "Constraint Name",CON1.DELETE_RULE,CON1.STATUS,
CON.TABLE_NAME,CON.CONSTRAINT_NAME,COL.POSITION,COL.COLUMN_NAME
FROM DBA_CONSTRAINTS CON,DBA_CONS_COLUMNS COL,DBA_CONSTRAINTS CON1
WHERE CON.OWNER = upper('&&owner')
AND CON.TABLE_NAME = upper('&&table')
AND ((CON.CONSTRAINT_TYPE = 'P') OR (CON.CONSTRAINT_TYPE = 'U'))
AND COL.TABLE_NAME = CON1.TABLE_NAME
AND COL.CONSTRAINT_NAME = CON1.CONSTRAINT_NAME
AND CON1.OWNER = CON.OWNER
AND CON1.R_CONSTRAINT_NAME = CON.CONSTRAINT_NAME
AND CON1.CONSTRAINT_TYPE = 'R'
GROUP BY CON1.OWNER,CON1.TABLE_NAME,CON1.CONSTRAINT_NAME,CON1.DELETE_RULE,
CON1.STATUS,CON.TABLE_NAME,CON.CONSTRAINT_NAME,COL.POSITION,COL.COLUMN_NAME;
-- ==========================================================
prompt
prompt
prompt Show all the check Constraints
-- Show all the check Constraints
SET HEADING OFF
col search_condition for a38
select 'alter table '||TABLE_NAME||' add constraint '||constraint_name||' check (',SEARCH_CONDITION,'); '
from dba_constraints WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table')
AND CONSTRAINT_TYPE = 'C';
-- ==========================================================
prompt
prompt
prompt Show all the Triggers that have been created on this table
-- Show all the Triggers that have been created on this table
-- add query to extract Trigger Body etcc WHEN CLAUSE here.
SET ARRAYSIZE 1
SET LONG 6000000
select owner,'create or replace trigger ',trigger_name,description,trigger_body,'/'
from dba_triggers
where owner = upper('&&owner')
and table_name = upper('&&table');
-- ========================================================
prompt
prompt
prompt Show all the GRANTS made on this table and it's columns.
-- Show all the GRANTS made on this table and it's columns.
-- ========================================================
-- Table 1st
-- =========
select 'GRANT ',privilege,' ON ',TABLE_NAME,' TO ',GRANTEE,';'
from DBA_TAB_PRIVS
where OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table');
-- Columns 2nd
-- ===========
SELECT 'GRANT ',PRIVILEGE,' ( ',COLUMN_NAME,' ) ',' ON ',TABLE_NAME,' TO ',GRANTEE,';'
FROM DBA_COL_PRIVS
WHERE OWNER = upper('&&owner')
AND TABLE_NAME = upper('&&table');
SET HEADING ON
====
@table_info1
SQL> set linesize 155
SQL> set pagesize 60
SQL> set echo on feed on arraysize 1 LONG 5000 verify off
SQL> set linesize 140 echo off feed off
Show the Table Structure
EMPNO NUMBER 22 N 1
ENAME VARCHAR2 10 Y 2
JOB VARCHAR2 9 Y 3
MGR NUMBER 22 Y 4
HIREDATE DATE 7 Y 5
SAL NUMBER 22 Y 6
COMM NUMBER 22 Y 7
DEPTNO NUMBER 22 Y 8
Elapsed: 00:00:00.00
Show Physical Attributes
10 65536 1048576 2147483645 14 38
Elapsed: 00:00:00.00
Show the actual Maximum Size of a Row
136
Elapsed: 00:00:00.00
Show the Number of Physical EXTENTS that have been allocated Attributes
EMP 1
Elapsed: 00:00:00.04
Show the Physical SIZE IN BYTES of the TABLE
EMP 65536
Elapsed: 00:00:00.04
Show all the indexes and their columns for this table
SCOTT SCOTT SYS_C0022543 UNIQUE EMPNO 1
Elapsed: 00:00:00.00
Display all the physical details of the Primary and Other Indexes for table
SCOTT SCOTT SYS_C0022543 UNIQUE EMPNO 1 10 65536 1048576 2147483645
Elapsed: 00:00:00.00
Show the Non-Foreign Keys Constraints on this table
SCOTT SYS_C0022543 EMPNO 1 primary
Elapsed: 00:00:00.79
Show the Foreign Keys on this table pointing at other tables Primary key fields for referential integreity purposes
Elapsed: 00:00:00.01
Show the Foreign Keys pointing at this table via the recursive call to the constraints table
Elapsed: 00:00:03.74
Show all the check Constraints
Elapsed: 00:00:00.30
Show all the Triggers that have been created on this table
Elapsed: 00:00:00.30
Show all the GRANTS made on this table and it's columns.
Elapsed: 00:00:00.03
Elapsed: 00:00:00.00
No comments:
Post a Comment