快捷導(dǎo)航

Oracle不同權(quán)限獲取表,視圖,字段的sql整理方式

更新時間：2026年03月27日 09:48:44 作者：未來@音律

這篇文章主要介紹了Oracle不同權(quán)限獲取表,視圖,字段的sql整理方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

1. Oracle獲取表sql

1.1 有DBA權(quán)限獲取方式

select distinct TABLE_NAME from dba_tables 
where OWNER ='庫名' 
and  owner NOT IN ('SYS','SYSTEM','OUTLN','XDB','WMSYS','CTXSYS','MDSYS')

1.2 普通權(quán)限獲取方式

select distinct TABLE_NAME from all_tables 
where OWNER ='庫名' 
and  owner NOT IN ('SYS','SYSTEM','OUTLN','XDB','WMSYS','CTXSYS','MDSYS')

2. Oracle獲取表字段sql

2.1 有DBA權(quán)限獲取方式

SELECT UPPER(T.TABLE_NAME),NVL(cc.comments, ''),C.COLUMN_NAME,CC.COMMENTS,C.DATA_TYPE,C.DATA_LENGTH,C.DATA_PRECISION,C.DATA_SCALE
 ,NULLABLE,CASE WHEN PK.CONSTRAINT_TYPE = 'P' THEN 'y'  ELSE 'n'  END
 FROM DBA_tables T JOIN DBA_TAB_COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME AND T.OWNER = C.OWNER
 LEFT JOIN DBA_COL_COMMENTS CC ON C.OWNER = CC.OWNER AND C.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME
 LEFT JOIN 
    (SELECT CONS.TABLE_NAME, CONS.OWNER, COLS.COLUMN_NAME, CONS.CONSTRAINT_TYPE
     FROM DBA_CONSTRAINTS CONS 
     JOIN DBA_CONS_COLUMNS COLS ON CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME 
     WHERE CONS.CONSTRAINT_TYPE = 'P'
    ) PK ON C.OWNER = PK.OWNER AND C.TABLE_NAME = PK.TABLE_NAME AND C.COLUMN_NAME = PK.COLUMN_NAME
    where UPPER(T.TABLE_NAME) in ( 逗號分隔的表名 ) and t.OWNER NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'XDB', 'WMSYS', 'CTXSYS', 'MDSYS')
 ORDER BY T.TABLE_NAME, C.COLUMN_ID

2.2 普通權(quán)限獲取方式

SELECT UPPER(T.TABLE_NAME),NVL(cc.comments, ''),C.COLUMN_NAME,CC.COMMENTS,C.DATA_TYPE,C.DATA_LENGTH,C.DATA_PRECISION,C.DATA_SCALE
 ,NULLABLE,CASE WHEN PK.CONSTRAINT_TYPE = 'P' THEN 'y'  ELSE 'n'  END
 FROM all_tables T JOIN ALL_TAB_COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME AND T.OWNER = C.OWNER
 LEFT JOIN ALL_COL_COMMENTS CC ON C.OWNER = CC.OWNER AND C.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME
 LEFT JOIN 
    (SELECT CONS.TABLE_NAME, CONS.OWNER, COLS.COLUMN_NAME, CONS.CONSTRAINT_TYPE
     FROM ALL_CONSTRAINTS CONS 
     JOIN ALL_CONS_COLUMNS COLS ON CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME 
     WHERE CONS.CONSTRAINT_TYPE = 'P'
    ) PK ON C.OWNER = PK.OWNER AND C.TABLE_NAME = PK.TABLE_NAME AND C.COLUMN_NAME = PK.COLUMN_NAME
    where UPPER(T.TABLE_NAME) in ( 逗號分隔的表名 ) and t.OWNER NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'XDB', 'WMSYS', 'CTXSYS', 'MDSYS')
 ORDER BY T.TABLE_NAME, C.COLUMN_ID

3. Oracle獲取視圖sql

3.1 有DBA權(quán)限獲取方式

select distinct view_name from all_views 
where owner not in ('SYS','SYSTEM','OUTLN','XDB','WMSYS','CTXSYS','MDSYS') 
and OWNER ='庫名';

3.2 普通權(quán)限獲取方式

select distinct view_name from all_views 
where owner not in ('SYS','SYSTEM','OUTLN','XDB','WMSYS','CTXSYS','MDSYS') 
and OWNER ='庫名';

4. Oracle獲取視圖字段sql

4.1 有DBA權(quán)限獲取方式

SELECT 
		c.table_name AS view_name, 
		NVL ( cm.comments, '' ) AS cmt, 
		c.column_name, 
		cm.comments, 
		c.data_type, 
		c.data_length, 
		c.data_precision, 
		c.data_scale, 
		c.nullable, 
		 'n' AS is_primary_key 
FROM 
		dba_tab_columns c 
LEFT JOIN dba_col_comments cm ON c.OWNER = cm.OWNER 
AND c.table_name = cm.table_name 
AND c.column_name = cm.column_name 
WHERE 
		c.OWNER = '庫名' 
AND c.table_name IN ( 逗號分隔的表名 )
ORDER BY c.table_name,c.column_id

4.2 普通權(quán)限獲取方式

SELECT 
		c.table_name AS view_name, 
		NVL ( cm.comments, '' ) AS cmt, 
		c.column_name, 
		cm.comments, 
		c.data_type, 
		c.data_length, 
		c.data_precision, 
		c.data_scale, 
		c.nullable, 
		 'n' AS is_primary_key 
FROM 
		all_tab_columns c 
LEFT JOIN all_col_comments cm ON c.OWNER = cm.OWNER 
AND c.table_name = cm.table_name 
AND c.column_name = cm.column_name 
WHERE 
		c.OWNER = '庫名' 
AND c.table_name IN ( 逗號分隔的表名 )
ORDER BY c.table_name,c.column_id