oracle架构级别常用sql汇总

1.查询表上创建的分区

select partition_name, tablespace_name, segment_type from user_segments where segment_name=upper('INQUIRY_HST_R2R');

 

2.查询数据库索引类型及占用大小

select segment_name, segment_type, bytes from user_segments where segment_type='INDEX';

 

3.查询一个表占了多大的空间

select trunc(bytes/(1024*1024)) table_size_M from user_segments where segment_name=upper('INQUIRY_HST_R2R');

 

4.得到一个表的建表语句

SQL> set long 4000;
SQL> set pagesize 300;
SQL> select dbms_metadata.get_ddl('TABLE', 'INQUIRY_HST_R2R') from dual;