オブジェクト確認SQL

##テーブル名などのオブジェクト確認SQL
実際には実行して確かめていないけど、こんな感じのSQLで対象オブジェクトが確認できると思います。


・アクセスできるオブジェクトをすべて示す
SELECT OWNER,
       OBJECT_NAME,
       OBJECT_TYPE,
       CREATED,
       LAST_DDL_TIME,
       STATUS
  FROM DBA_OBJECTS
 WHERE OWNER = 'スキーマ名'
   AND OBJECT_NAME like 'テーブル名'
;

・テーブル名
SELECT OWNER,
       TABLE_NAME,
       TABLESPACE_NAME,
       STATUS,
       LAST_ANALYZED
  FROM DBA_TABLES
 WHERE OWNER = 'スキーマ名'
   AND TABLE_NAME like 'テーブル名'
 ORDER BY OWNER, TABLE_NAME
;

・テーブルコメント
SELECT COMMENTS 
  FROM ALL_TAB_COMMENTS
 WHERE OWNER = 'スキーマ名' 
   AND TABLE_NAME = 'テーブル名'
;

・カラム
SELECT                    
       OWNER             , 
       TABLE_NAME         ,
       COLUMN_ID          ,
       COLUMN_NAME        ,
       DATA_TYPE          ,
       DATA_LENGTH        ,
       DATA_PRECISION     ,
       DATA_SCALE         ,
       DATA_DEFAULT       ,
       NULLABLE           
  FROM DBA_TAB_COLUMNS 
 WHERE OWNER = 'スキーマ名'
   AND TABLE_NAME = 'テーブル名'
 ORDER BY COLUMN_ID
;

・インデックス
SELECT
   OWNER                    ,
   INDEX_NAME               ,
   INDEX_TYPE               ,
   TABLE_OWNER              ,
   TABLE_NAME               ,
   UNIQUENESS               ,
   STATUS                   ,
   TABLESPACE_NAME          ,
   PARTITIONED              ,
   COMPRESSION              ,
   LAST_ANALYZED            
 FROM DBA_INDEXES
WHERE TABLE_OWNER = 'スキーマ名'
  AND TABLE_NAME  = 'テーブル名'
ORDER BY OWNER, INDEX_NAME
;

・インデックスのカラム
select *
from DBA_IND_COLUMNS
where TABLE_OWNER = '&1'
and TABLE_NAME = '&2'
ORDER BY INDEX_NAME, COLUMN_POSITION
;

・順序をすべて示す
SELECT
 SEQUENCE_OWNER    ,
 SEQUENCE_NAME     ,
 MIN_VALUE         ,
 MAX_VALUE         ,
 INCREMENT_BY      ,
 CYCLE_FLAG        ,
 ORDER_FLAG        ,
 CACHE_SIZE        ,
 LAST_NUMBER       
FROM DBA_SEQUENCES
WHERE SEQUENCE_OWNER = 'スキーマ名'
  AND SEQUENCE_NAME = 'シーケンス名'
ORDER BY SEQUENCE_OWNER, SEQUENCE_NAME
;

・トリガー（テーブル名で検索）
SELECT
 OWNER             ,
 TABLE_OWNER       ,
 TABLE_NAME        ,
 TRIGGER_NAME      ,
 TRIGGER_TYPE      ,
 COLUMN_NAME       ,
 STATUS
  FROM DBA_TRIGGERS
 WHERE OWNER = 'スキーマ名'
   AND TABLE_NAME = 'テーブル名'
 ORDER BY TRIGGER_NAME
;

・トリガー（トリガー名で検索）
SELECT
 OWNER             ,
 TABLE_OWNER       ,
 TABLE_NAME        ,
 TRIGGER_NAME      ,
 TRIGGER_TYPE      ,
 COLUMN_NAME       ,
 STATUS
  FROM DBA_TRIGGERS
 WHERE OWNER = 'スキーマ名'
   AND TRIGGER_NAME = 'trigger名'
 ORDER BY TRIGGER_NAME
;

--マテリアライズド・ビュー一覧
SELECT OWNER,
       OBJECT_NAME,
       CREATED,
       LAST_DDL_TIME,
       STATUS
  FROM DBA_OBJECTS
 WHERE OWNER not in ('OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS','OUTLN','TSMSYS','WMSYS',
                     'XDB','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','ANONYMOUS','CTXSYS',
                     'DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','DBA_WORK')
   AND OBJECT_TYPE = 'MATERIALIZED VIEW'
;

・マテリアライズド・ビュー
SELECT
 OWNER                       ,
 MVIEW_NAME                  ,
 REFRESH_MODE                ,
 REFRESH_METHOD              ,
 QUERY_LEN                   ,
 UPDATABLE                   ,
 UPDATE_LOG                  ,
 MASTER_LINK                 ,
 REWRITE_ENABLED             ,
 REWRITE_CAPABILITY          ,
 FAST_REFRESHABLE            ,
 LAST_REFRESH_TYPE           ,
 LAST_REFRESH_DATE           ,
 STALENESS                   ,
 UNKNOWN_PREBUILT            ,
 UNKNOWN_PLSQL_FUNC          ,
 UNKNOWN_EXTERNAL_TABLE      ,
 UNKNOWN_CONSIDER_FRESH      ,
 UNKNOWN_IMPORT              ,
 UNKNOWN_TRUSTED_FD          ,
 COMPILE_STATE               ,
 USE_NO_INDEX                ,
 STALE_SINCE                 
from dba_mviews
 WHERE OWNER like 'スキーマ名'
   AND MVIEW_NAME like 'MVIEW名'
 ORDER BY OWNER, MVIEW_NAME
;

・マテリアライズド・ビュー・ログ
SELECT
 LOG_OWNER            ,
 MASTER               , --変更がロギングされるマスター・マテリアライズド・ビュー名
 LOG_TABLE            , --マスター・マテリアライズド・ビューへの変更が記録される表の名前
 LOG_TRIGGER          ,
 ROWIDS               ,
 PRIMARY_KEY          ,
 OBJECT_ID            ,
 FILTER_COLUMNS       ,
 SEQUENCE             ,
 INCLUDE_NEW_VALUES   
  FROM DBA_MVIEW_LOGS
 WHERE LOG_OWNER like 'ログオーナー名'
   AND MASTER    like 'MVIEW名'
 ORDER BY LOG_OWNER, MASTER
;

--マテリアライズド・ビューのコメント
SELECT MVIEW_NAME, COMMENTS
 FROM DBA_MVIEW_COMMENTS
WHERE OWNER = 'スキーマ名'
AND MVIEW_NAME = 'MVIEW名'
;

・プロシージャ
SELECT OWNER               ,
       OBJECT_NAME         ,
       OBJECT_TYPE         
  FROM DBA_PROCEDURES
 WHERE OWNER = '&1'
   AND OBJECT_TYPE = 'PROCEDURE'
 ORDER BY OBJECT_NAME
;

・制約
SELECT
   OWNER                   ,
   TABLE_NAME              ,
   CONSTRAINT_TYPE         ,
   CONSTRAINT_NAME         ,
   STATUS                  ,
   INDEX_OWNER             ,
   INDEX_NAME              ,
   INVALID                 
 FROM DBA_CONSTRAINTS
WHERE OWNER = 'スキーマ名'
  AND TABLE_NAME  = 'テーブル名'
ORDER BY OWNER, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
;

・シノニム
SELECT OWNER          ,
       SYNONYM_NAME   ,
       TABLE_OWNER    ,
       TABLE_NAME     ,
       DB_LINK        
  FROM DBA_SYNONYMS
 WHERE OWNER = 'スキーマ名'
   AND SYNONYM_NAME like 'シノニム名'
 ORDER BY OWNER, SYNONYM_NAME
;

・権限
SELECT 
       OWNER,
       TABLE_NAME,
       GRANTOR,
       GRANTEE,
       PRIVILEGE,
       GRANTABLE,
       HIERARCHY
  FROM DBA_TAB_PRIVS
 WHERE OWNER = '&1'
   AND TABLE_NAME = '&2'
 ORDER BY OWNER, TABLE_NAME, GRANTOR, GRANTEE, PRIVILEGE
;

・ユーザーおよびロールに付与されたシステム権限
select
GRANTEE,PRIVILEGE,ADMIN_OPTION
from DBA_SYS_PRIVS
where GRANTEE='スキーマ名'
GRANTEE,PRIVILEGE
;

・データベース内のすべてのユーザーおよびロールに付与されたロールを示す
select
GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE
from DBA_ROLE_PRIVS
where GRANTEE ='スキーマ名'
order by GRANTEE,GRANTED_ROLE
;

・ユーザー一覧
SELECT
 USERNAME             ,
 ACCOUNT_STATUS       ,
 PROFILE              ,
 DEFAULT_TABLESPACE   AS DEFAULT_TS,
 TEMPORARY_TABLESPACE AS TEMP_TS,
 CREATED              ,
 LOCK_DATE            ,
 EXPIRY_DATE          
FROM DBA_USERS
ORDER BY USERNAME
;

・DBリンク
SELECT
 OWNER            ,
 DB_LINK          ,
 USERNAME         ,
 HOST             ,
 CREATED          
FROM DBA_DB_LINKS
ORDER BY OWNER, DB_LINK, HOST, CREATED
;

・インスタンス
SELECT HOST_NAME,
       NAME,
       STATUS,
       DATABASE_ROLE,
       DATAGUARD_BROKER "BROKER",
       TO_CHAR(STARTUP_TIME,'YYYY/MM/DD HH24:MI:SS') STARTUP,
       ARCHIVER
  FROM V$INSTANCE,
       V$DATABASE
;