Oracle查询表空间大小及每个表所占空间的大小语句示例

SELECT

     tablespace_name,

     sum ( bytes ) / 1024 / 1024

FROM

     dba_data_files

GROUP BY

     tablespace_name;

SELECT

     tablespace_name,

     file_id,

     file_name,

     round( bytes / ( 1024 * 1024 ), 0 ) total_space

FROM

     dba_data_files

ORDER BY

     tablespace_name;

SELECT

     a.tablespace_name,

     total,

     free ,

     total - free AS used,

     substr( free / total * 100, 1, 5 ) AS "FREE%" ,

     substr( ( total - free ) / total * 100, 1, 5 ) AS "USED%"

FROM

     ( SELECT tablespace_name, sum ( bytes ) / 1024 / 1024 AS total FROM dba_data_files GROUP BY tablespace_name) a,

     ( SELECT tablespace_name, sum ( bytes ) / 1024 / 1024 AS free FROM dba_free_space GROUP BY tablespace_name) b

WHERE

     a.tablespace_name = b.tablespace_name

ORDER BY

     a.tablespace_name;

SELECT

     t.segment_name,

     t.segment_type,

     sum ( t.bytes / 1024 / 1024 ) "占用空间(M)"

FROM

     dba_segments t

WHERE

     t.segment_type = 'TABLE'

     AND t.segment_name = 'TABLE_NAME'

GROUP BY

     OWNER,

     t.segment_name,

     t.segment_type;

create user csy identified by csy default tablespace ANIMAL;

select tablespace_name from user_tablespaces;

create user csy identified by csy default tablespace ANIMAL;

grant connect , resource, dba to csy;

create table dog

(

     name varchar (12),

     age varchar (12)

)

tablespace animal;

SELECT

     tablespace_name, table_name

FROM

     user_tables

WHERE

     tablespace_name = 'ANIMAL' ;

drop tablespace ANIMAL including contents and datafiles;

drop user user_name cascade ;