##テーブル名などのオブジェクト確認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
;