Oracle查询库结构及表属性SQL总结

最新推荐文章于 2024-06-26 14:45:01 发布

原创最新推荐文章于 2024-06-26 14:45:01 发布 · 1.3k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #sql

数据库专栏收录该内容

5 篇文章

订阅专栏

本文总结了查询Oracle数据库中库结构和表属性的相关SQL操作，包括获取所有用户、数据库名称与字符编码、表空间大小，以及查询表、序列、视图、触发器和物化视图。此外，还涵盖了查询表的列属性、主键、外键、索引和唯一约束的详细信息。

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'