表空间创建语句

• 创建表空间  

-- 创建表空间
create tablespace LQ datafile 'E:/oracle_data/LQ_01.dbf' size 2000M autoextend on next 200M maxsize 10000M;
-- 创建表空间（不设置上限）
create tablespace LQ datafile 'E:/oracle_data/LQ_01.dbf'
size 2000M autoextend on next 200M maxsize unlimited
extent management local autoallocate segment space management auto;

• 修改表空间

-- 修改表空间文件大小（支持TEMP临时表空间）
ALTER DATABASE DATAFILE 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf' RESIZE 10000M;
-- 修改表空间文件自增长
ALTER DATABASE DATAFILE 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf' AUTOEXTEND ON NEXT 500M;

• 增加表空间文件

-- 增加表空间文件
ALTER TABLESPACE demospace ADD DATAFILE 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE_02.dbf'
SIZE 2000M autoextend on next 200M maxsize 10000M;

• 缩小表空间文件（先查）【未完待续】

select tablespace_name, file_id, file_name,
--表空间最大值
round(bytes/(1024*1024),0) total_space,
--已使用表空间的最大值
(SELECT MAX(block_id)*8/1024 FROM dba_extents WHERE tablespace_name = 'LQ') max_space 
from dba_data_files f order by tablespace_name;

--1、先处理不连续的表空间
alter tablespace users coalesce;

--2、查询file_id,也可在toad工具tablespace直接看到。
select file#,name from v$datafile;

--3、查该数据文件中数据处在最大位置
select max(block_id) from dba_extents where file_id=上一步查出的id; -- 383497

--4、 查出最大块位置
select 383497*8/1024 from dual; --2996.0703125
--这说明该文件中最大使用块位于2996M与3000M之间，

--5、 修改表空间（也可用toad工具界面修改）
ALTER DATABASE datafile '/opt/oracle/oradata/LQ/temp02.dbf' RESIZE 3000M;

• 创建用户&指定表空间（oracle10g默认是 USERS 表空间）

-- 创建用户时直接指定
create user demo identified by demo default tablespace DEMOSPACE ;

-- 分步指定
create user demo identified by demo ;
alter user demo default tablespace demospace;

-- 同时指定临时表空间
create user demo identified by demo default tablespace demospace temporary tablespace temp;

表空间查询语句

• 查看默认/缺省表空间

-- 查看默认的用户表空间
select * from database_properties where property_name like '%DEF%';

-- 查看所有用户的默认表空间（视情况清理用户及表空间）
select username,default_tablespace from dba_users;

-- 查看当前用户的缺省表空间
select username,default_tablespace from user_users;

• 查看用户表空间占用

-- 查看每个用户占用的表空间
SELECT OWNER as "用户名", sum(BYTES) / 1024 / 1024 / 1024 as "所有表的大小(GB)" FROM DBA_SEGMENTS
WHERE SEGMENT_NAME in (select t2.OBJECT_NAME from dba_objects t2 
where t2.OBJECT_TYPE = 'TABLE') group by OWNER order by 2 desc ;

-- 当前用户每张表占用的表空间
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents 
Group By Segment_Name order by Sum(bytes)/1024/1024 desc ;

-- 查看每个用户的临时表空间
select username,default_tablespace,temporary_tablespace from dba_users ;

• 查看表空间自增长

-- 查看表空间是否允许自动增长
SELECT FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE FROM DBA_DATA_FILES;

-- 查看临时表空间是否允许自动增长
select d.file_name, d.tablespace_name, d.autoextensible from dba_temp_files d;

• 查看表空间文件路径

select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#;

• 查看表空间文件使用率  

select

  b.file_name 物理文件名,

  b.tablespace_name 表空间,

  b.MAXBYTES/1024/1024 最大M,

  b.bytes/1024/1024 当前大小M,

  (b.bytes-sum(nvl(a.bytes,0)))/1024/1024  已使用M,

  substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5)  当前利用率

  --,substr((b.bytes-sum(nvl(a.bytes,0)))/(b.MAXBYTES)*100,1,5)  全部利用率

  from dba_free_space a,dba_data_files b

  where a.file_id=b.file_id

  group by b.tablespace_name,b.file_name,b.bytes,b.MAXBYTES

  order by b.tablespace_name;

-- 查看表空间使用率

SELECT D.TABLESPACE_NAME,

SPACE || 'M' "SUM_SPACE(M)",

SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",

ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'

"USED_RATE(%)",

FREE_SPACE || 'M' "FREE_SPACE(M)" , '_DATA'

FROM ( SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,

SUM (BLOCKS) BLOCKS

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) D,

( SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

UNION ALL -- 如果有临时表空间

SELECT D.TABLESPACE_NAME,

SPACE || 'M' "SUM_SPACE(M)",

USED_SPACE || 'M' "USED_SPACE(M)",

ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",

NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" , '_TEMP'

FROM ( SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,

SUM (BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D,

( SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,

ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

ORDER BY 1;

-- SQL2（临时表空间）

SELECT FILE# AS FILE_NUMBER

,NAME AS NAME

,CREATION_TIME AS CREATION_TIME

,BLOCK_SIZE AS BLOCK_SIZE

,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"

,CREATE_BYTES/1024/1024/1024 AS "INIT_SIZE(G)"

,STATUS AS STATUS

,ENABLED AS ENABLED

FROM V$TEMPFILE;

表空间删除语句（适用临时表空间）

注意：不能删除当前用户的默认表空间，否则会报ORA-12906错误。如果需要删除某一个默认的临时表空间，则必须先创建一个临时表空间，然后指定新创建的表空间为默认表空间，然后删除原来的临时表空间。

-- 删除用户、表空间

drop user ×× cascade ;

说明： 删除了user，只是删除了该user下的schema objects，不会删除相应的tablespace

-- 删除空的表空间，但是不包含物理文件

drop tablespace tablespace_name;

-- 删除非空表空间，但是不包含物理文件

drop tablespace tablespace_name including contents;

-- 删除空的表空间，包含物理文件

drop tablespace tablespace_name including datafiles;

-- 删除非空表空间，包含物理文件

drop tablespace tablespace_name including contents and datafiles;

-- 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS

drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

删除数据文件语法（不要使用，危险！！！）：

ALTER TABLESPACE 表空间名 DROP DATAFILE 数据文件名;

alter tablespace USERS drop datafile 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf' ;

-- 查看一下当前这个数据文件的状态

select * from dba_data_files t where t.file_name = 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf' ;

-- 修改表空间状态

alter database datafile ‘F:\TEST\MYSPACE3.DBF’ online ;

-- 移动重命名文件

例如，我需要将/home/oracle/oradata/orcl/temp01.dbf 这个文件重命名为/home/oracle/oradata/orcl/temp02.dbf

-- 将临时表空间的临时文件脱机；
ALTER DATABASE TEMPFILE '/home/oracle/oradata/orcl/temp01.dbf' OFFLINE;
-- 移动或重命名相关的临时文件；
mv /home/oracle/oradata/orcl/temp01.dbf /home/oracle/oradata/orcl/temp01.dbf
-- 使用脚本ALTER DATABASE RENAME FILE；
ALTER DATABASE RENAME FILE '/home/oracle/oradata/orcl/temp01.dbf' TO '/u01/app/oracle/oradata/GSP/temp02.dbf';
-- 将临时表空间的临时文件联机；
ALTER DATABASE TEMPFILE '/home/oracle/oradata/orcl/temp02.dbf' ONLINE;
Database altered.

压缩临时表空间

create temporary tablespace TEMP2 TEMPFILE 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/temp02.dbf'

SIZE 64M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --创建中转临时表空间

alter database default temporary tablespace temp2; --改变缺省临时表空间 为刚刚创建的新临时表空间temp2

drop tablespace temp including contents and datafiles; --删除原来临时表空间

create temporary tablespace TEMP TEMPFILE 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/temp01.dbf'

SIZE 128M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --重新创建临时表空间

alter database default temporary tablespace temp; --重置缺省临时表空间为新建的temp表空间

drop tablespace temp2 including contents and datafiles;--删除中转用临时表空间

alter user roll temporary tablespace temp;