Oracle元数据查询总结

 

select DISTINCT(OWNER) from all_tables

select TABLE_NAME  from all_tables where OWNER = 'WZZLSDB'

select A.OWNER,A.TABLE_NAME ,A.NUM_ROWS,A.NUM_ROWS * A.avg_row_len AS STORAGE_SIZE  ,
B.CREATED ,B.LAST_DDL_TIME ,C.COMMENTS 
from all_tables A,all_objects B ,all_tab_comments C
where  A.TABLE_NAME = B.object_name AND A.OWNER =B.OWNER 
AND A.TABLE_NAME = C.TABLE_NAME  AND A.OWNER =C.OWNER 
AND A.OWNER  = 'WZZLSDB' AND A.TABLE_NAME ='TZ_BZ'

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE, COLUMN_ID FROM USER_TAB_COLUMNS
WHERE table_name='TZ_BZ'
ORDER BY TABLE_NAME, COLUMN_ID;
SELECT *  

-- 表注释
SELECT TABLE_NAME,COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_TYPE = 'TABLE' AND table_name='TZ_BZ';
SELECT COMMENTS FROM all_tab_comments WHERE  owner='WZZLSDB' AND table_name ='TZ_BZ'
-- 表字段信息
SELECT  A.COLUMN_NAME, A.DATA_TYPE,   B.COMMENTS 
FROM ALL_TAB_COLUMNS A, ALL_COL_COMMENTS B 
WHERE A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME AND a.table_name='TZ_BZ'
ORDER BY A.TABLE_NAME, A.COLUMN_ID;

SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, A.DATA_LENGTH, A.DATA_PRECISION, A.DATA_SCALE, A.NULLABLE, A.COLUMN_ID, A.DATA_DEFAULT, B.COMMENTS 
FROM USER_TAB_COLUMNS A, USER_COL_COMMENTS B 
WHERE A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME AND a.table_name='TZ_BZ'
ORDER BY A.TABLE_NAME, A.COLUMN_ID;

-- 索引信息
SELECT DISTINCT A.TABLE_NAME, A.INDEX_NAME, A.UNIQUENESS, 
LISTAGG(B.COLUMN_NAME,',') WITHIN GROUP (ORDER BY B.COLUMN_POSITION) OVER(PARTITION BY A.TABLE_NAME, A.INDEX_NAME) 
FROM USER_INDEXES A, USER_IND_COLUMNS B WHERE A.TABLE_NAME = B.TABLE_NAME AND A.INDEX_NAME = B.INDEX_NAME;
--主键
select cu.COLUMN_NAME  from user_cons_columns cu, user_constraints au 
where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' 
and au.table_name = 'ORDER_ACTIVITIES_TEST' AND au.OWNER ='WZZLSDB'

-- 主键
SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P';
--实际存储空间大小
select table_name  
      ,num_rows , avg_row_len  
  from user_tables
  
 select CREATED  from dba_objects where owner='WZZLSDB' and object_name ='TZ_BZ';

--主键只能有一个
alter table TZ_BZ  add constraint tid primary key(COMP_NAME);

SELECT * FROM TZ_BZ tb 

--分区
select * from DBA_PART_TABLES 
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='ORDER_ACTIVITIES_TEST';
select * from USER_PART_TABLES  
select * from ALL_TAB_PARTITIONS
select * from user_tables a where a.partitioned='YES' 
select column_name from USER_PART_KEY_COLUMNS WHERE name='ORDER_ACTIVITIES_TEST'


--查看tablespace
select * from user_users
--添加分区
ALTER TABLE TZ_BZ ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
--创建分区
CREATE TABLE ORDER_ACTIVITIES_TEST   
(   
    ORDER_ID      INT PRIMARY KEY,   
    ORDER_DATE    DATE,   
    TOTAL_AMOUNT NUMBER,   
    CUSTOTMER_ID NUMBER(7),   
    PAID   INT  
)   
PARTITION  BY  RANGE (PAID)   
(   
      PARTITION  part1 VALUES  LESS  THAN (1000) TABLESPACE  BD,   
      PARTITION  part2 VALUES  LESS  THAN (MAXVALUE) TABLESPACE  BD   
);  

 

补充

--分区字段
select column_name from USER_PART_KEY_COLUMNS WHERE name='${table}'

--元数据信息
select rownum, A.OWNER,A.TABLE_NAME ,A.NUM_ROWS,A.NUM_ROWS * A.avg_row_len AS STORAGE_SIZE  ,
B.CREATED ,B.LAST_DDL_TIME ,C.COMMENTS 
from all_tables A,all_objects B ,all_tab_comments C
where  A.TABLE_NAME = B.object_name AND A.OWNER =B.OWNER 
AND A.TABLE_NAME = C.TABLE_NAME  AND A.OWNER =C.OWNER 
AND A.OWNER  = '${db}' AND A.TABLE_NAME ='${table}'  +
AND rownum=1