Oracle查詢庫結構及表屬性
- 庫結構（用戶、表、序列、視圖、觸發器、物化視圖）
- 表屬性（欄位、主鍵、外鍵、索引、唯一約束）
庫結構（用戶、表、序列、視圖、觸發器、物化視圖）
- 【
用戶】 查詢 資料庫中的所有用戶
SELECT username FROM all_users WHERE username NOT IN ('SYS','SYSTEM','CTXSYS');
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE
FROM DBA_USERS WHERE INITIAL_RSRC_CONSUMER_GROUP!='SYS_GROUP';
- 【
編碼格式】查詢 所有資料庫名稱和字符編碼
SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
- 【
表空間】查詢 所有表空間及表空間大小
SELECT * FROM DBA_TABLESPACES
- 【
表】根據用戶名 查詢所有表
select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from all_tables
where owner ='OWNERNAME'
and TABLE_NAME not in (select mview_name from all_mviews where owner='OWNERNAME')
and TABLE_NAME not in (select table_name from all_external_locations where owner='OWNERNAME')
AND NESTED ='NO'
- 【
序列】 查詢 序列
SELECT * FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='OWNERNAME'
- 【
視圖】根據用戶名 查詢所有視圖
SELECT * FROM DBA_OBJECTS WHERE OWNER = 'OWNERNAME' AND OBJECT_TYPE='VIEW'
- 【
觸發器】根據用戶名 查詢所有觸發器
SELECT TRIGGER_NAME FROM DBA_TRIGGERS WHERE OWNER= 'OWNERNAME'
- 【
物化視圖】根據用戶名 查詢所有物化視圖
SELECT * FROM DBA_OBJECTS WHERE OWNER = 'OWNERNAME' AND OBJECT_TYPE='MATERIALIZED VIEW'
表屬性（欄位、主鍵、外鍵、索引、唯一約束）
- 【
欄位】根據表名 查詢 列屬性
SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE FROM ALL_TAB_COLUMNS
WHERE OWNER='OWNERNAME' AND TABLE_NAME='TABLENAME'
- 【
主鍵】根據表名 查詢 主鍵
select a.constraint_name,a.column_name from dba_cons_columns a, dba_constraints b
where a.constraint_name = b.constraint_name and b.constraint_type = 'P'
and a.table_name ='TABLENAME' and a.owner ='OWNERNAME'
- 【
外鍵】根據表名 查詢 外鍵 及 外鍵關聯的詳細資訊
SELECT distinct DBA_CONS_COLUMNS.CONSTRAINT_NAME,DBA_CONS_COLUMNS.COLUMN_NAME,
DBA_INDEXES.TABLE_NAME,DBA_IND_COLUMNS.COLUMN_NAME
FROM DBA_CONSTRAINTS
JOIN DBA_CONS_COLUMNS ON (DBA_CONSTRAINTS.CONSTRAINT_NAME = DBA_CONS_COLUMNS.CONSTRAINT_NAME)
JOIN DBA_INDEXES ON (DBA_CONSTRAINTS.R_CONSTRAINT_NAME = DBA_INDEXES.INDEX_NAME)
JOIN DBA_IND_COLUMNS ON (DBA_INDEXES.INDEX_NAME = DBA_IND_COLUMNS.INDEX_NAME)
WHERE CONSTRAINT_TYPE = 'R'
and DBA_CONS_COLUMNS.TABLE_NAME ='TABLENAME' and DBA_CONS_COLUMNS.OWNER = 'OWNERNAME';
- 【
索引】根據表名 查詢 索引
select distinct dba_ind_columns.index_name,dba_ind_columns.column_name,
dba_ind_columns.column_position,dba_indexes.uniqueness from dba_ind_columns,dba_indexes
where dba_ind_columns.index_name = dba_indexes.index_name
and dba_ind_columns.table_name = 'TABLENAME' and dba_indexes.table_owner='OWNERNAME'
and dba_ind_columns.index_name not in
(select constraint_name from dba_constraints where table_name = 'TABLENAME');
- 【
唯一約束】根據表名 查詢 唯一約束
select distinct cu.COLUMN_NAME,cu.CONSTRAINT_NAME from dba_cons_columns cu, dba_constraints au
where cu.constraint_name = au.constraint_name and au.constraint_type = 'U'
and au.table_name = 'TABLENAME' and cu.owner='OWNERNAME'
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/278144.html
標籤：其他
上一篇：java筆記--資料庫（1）