Oracle表空间管理

• Oracle表空间管理

1. 创建表空间并指定用户
   请查看此文章：创建表空间并指定用户

2. 查看表空间大小：

SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g",
round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%"
FROM (SELECT tablespace_name,SUM(bytes) free FROM
DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY 4;

3. 查看表空间存放位置

select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;
或
select t1.name tablespace,t2.name filepath from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

4. 查看表结构

先调整sqlplus的输出格式
set line 200 pages 50000 wrap on long 999999 serveroutput on
查询表的表结构
select dbms_metadata.get_ddl('TABLE','<表名>') from dual;