收集表信息脚本
大神写的脚本分享给大家，用来收集表信息的
-- | PURPOSE : Prompt the user for a schema and and table name then query all |
-- | metadata about the table. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET PAGESIZE 9999
SET VERIFY OFF
SET FEEDBACK OFF
SET LONG 9000
-- +----------------------------------------------------------------------------+
-- | PROMPT USER FOR SCHEMA AND TABLE |
-- +----------------------------------------------------------------------------+
ACCEPT sch prompt 'Enter Schema (i.e. SCOTT) : '
ACCEPT tab prompt 'Enter Table (i.e. EMP) : '
PROMPT
PROMPT +----------------------------------------------------------------------------+
PROMPT | TABLE INFORMATION |
PROMPT +----------------------------------------------------------------------------+
COLUMN owner FORMAT A15 HEADING "Owner"
COLUMN table_name FORMAT A30 HEADING "Table Name"
COLUMN tablespace_name FORMAT A28 HEADING "Tablespace"
COLUMN last_analyzed FORMAT A20 HEADING "Last Analyzed"
COLUMN num_rows FORMAT 999,999,999 HEADING "# of Rows"
SELECT
owner
, table_name
, tablespace_name
, TO_CHAR(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed
, num_rows
FROM
dba_tables
WHERE
owner = UPPER('&sch')
AND table_name = UPPER('&tab')
/
PROMPT
PROMPT +----------------------------------------------------------------------------+
PROMPT | OBJECT INFORMATION |
PROMPT +----------------------------------------------------------------------------+
COLUMN object_id HEADING "Object ID"
COLUMN data_object_id HEADING "Data Object ID"
COLUMN created FORMAT A20 HEADING "Created"
COLUMN last_ddl_time FORMAT A20 HEADING "Last DDL"
COLUMN status HEADING "Status"
SELECT
object_id
, data_object_id
, TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') created
, TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') last_ddl_time
, status
FROM
dba_objects
WHERE
owner = UPPER('&sch')
AND object_name = UPPER('&tab')
AND object_type = 'TABLE'
/
PROMPT
PROMPT +----------------------------------------------------------------------------+
PROMPT | SEGMENT INFORMATION |
PROMPT +----------------------------------------------------------------------------+
COLUMN segment_type HEADING "Segment Type"
COLUMN bytes FORMAT 9,999,999,999,999 HEADING "Bytes"
COLUMN extents FORMAT 999,999,999 HEADING "Extents"
COLUMN initial_extent FORMAT 999,999,999,999 HEADING "Initial|Extent"
COLUMN next_extent FORMAT 999,999,999,999 HEADING "Next|Extent"
COLUMN min_extents FORMAT 999 HEADING "Min|Extents"
COLUMN max_extents FORMAT 9,999,999,999 HEADING "Max|Extents"
COLUMN pct_increase FORMAT 999.00 HEADING "Pct|Increase"
COLUMN freelists HEADING "Free|Lists"
COLUMN freelist_groups HEADING "Free|List Groups"
SELECT
segment_type segment_type
, bytes/1024/1024 Mbytes
, extents extents
, initial_extent initial_extent
, next_extent next_extent
, min_extents min_extents
, max_extents max_extents
, pct_increase pct_increase
, freelists freelists
, freelist_groups freelist_groups
FROM
dba_segments
WHERE
owner = UPPER('&sch')
AND segment_name = UPPER('&tab')
/
PROMPT
PROMPT +----------------------------------------------------------------------------+
PROMPT | COLUMNS |
PROMPT +----------------------------------------------------------------------------+
COLUMN column_name FORMAT A20 HEADING "Column Name"
COLUMN data_type FORMAT A25 HEADING "Data Type"
COLUMN nullable FORMAT A13 HEADing "Null?"
SELECT
column_name
, DECODE(nullable, 'Y', ' ', 'NOT NULL') nullable
, DECODE(data_type
, 'RAW', data_type || '(' || data_length || ')'
, 'CHAR', data_type || '(' || data_length || ')'
, 'VARCHAR', data_type || '(' || data_length || ')'
, 'VARCHAR2', data_type || '(' || data_length || ')'
, 'NUMBER', NVL2( data_precision
, DECODE( data_scale
, 0
, data_type || '(' || data_precision || ')'
, data_type || '(' || data_precision || ',' || data_scale || ')'
)
, data_type)
, data_type
) data_type
FROM
dba_tab_columns
WHERE
owner = UPPER('&sch')
AND table_name = UPPER('&tab')
ORDER BY
column_id
/
PROMPT
PROMPT +----------------------------------------------------------------------------+
PROMPT | CONSTRAINTS |
PROMPT | UNCOMMENT THIS SECTION FROM THE SCRIPT TO GET THE INFO ABOUT CONST |
PROMPT +----------------------------------------------------------------------------+
PROMPT
COLUMN constraint_name FORMAT A18 HEADING "Constraint Name"
COLUMN constraint_type FORMAT A11 HEADING "Constraint|Type"
COLUMN search_condition FORMAT A15 HEADING "Search Condition"
COLUMN r_constraint_name FORMAT A20 HEADING "R / Constraint Name"
COLUMN delete_rule FORMAT A11 HEADING "Delete Rule"
COLUMN status HEADING "Status"
BREAK ON constraint_name ON constraint_type
SELECT
a.constraint_name
, DECODE(a.constraint_type
, 'P', 'Primary Key'
, 'C', 'Check'
, 'R', 'Referential'
, 'V', 'View Check'
, 'U', 'Unique'
, a.constraint_type
) constraint_type
, b.column_name
, a.search_condition
, NVL2(a.r_owner, a.r_owner || '.' || a.r_constraint_name, null) r_constraint_name
, a.delete_rule
, a.status
FROM
dba_constraints a
, dba_cons_columns b
WHERE
a.owner = UPPER('&sch')
AND a.table_name = UPPER('&tab')
AND a.constraint_name = b.constraint_name
AND b.owner = UPPER('&sch')
AND b.table_name = UPPER('&tab')
ORDER BY
a.constraint_name
, b.position
/
SET PAGESIZE 9999
SET VERIFY OFF
SET FEEDBACK ON
