Scripts:查询数据库中所有的表dba_tables_all.sql

最新推荐文章于 2022-12-08 09:13:04 发布

转载最新推荐文章于 2022-12-08 09:13:04 发布 · 717 阅读

Scripts 专栏收录该内容

136 篇文章

订阅专栏

此脚本用于查询Oracle数据库中的所有表及其详细信息，包括所有者、表名、表空间、最后分析时间以及记录数量。确保在开发环境中测试后再在生产环境中运行。

-- +----------------------------------------------------------------------------+
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : dba_tables_all.sql |
-- | CLASS : Database Administration |
-- | PURPOSE : Query all tables (and owners) within the database. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+

SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;

PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : All Database Tables |
PROMPT | Instance : &current_instance |
PROMPT +------------------------------------------------------------------------+

SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT OFF
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN owner FORMAT a20 HEADING "Owner"
COLUMN table_name FORMAT a30 HEADING "Table Name"
COLUMN tablespace_name FORMAT a30 HEADING "Tablespace"
COLUMN last_analyzed FORMAT a20 HEADING "Last Analyzed"
COLUMN num_rows FORMAT 999,999,999,999 HEADING "# of Rows"

DEFINE spool_file=database_tables.lst

SPOOL &spool_file

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 NOT IN ('SYS', 'SYSTEM')
ORDER BY
owner
, table_name
/

SPOOL OFF

SET TERMOUT ON

PROMPT
PROMPT Report written to &spool_file
PROMPT