Oracle表空间知识

Oracle表空间知识

一，创建临时表空间

CREATE temporary TABLESPACE TEMP_PNLREPORT tempfile '/oradata2/ORCL/temp_pnlreport.dbf'
SIZE 500m AUTOEXTEND ON next 10m MAXSIZE 2g

二，创建一般表空间

create tablespace tbs_kettle datafile '/oradata/stage/tbs_kettle.dbf' size 500m autoextend on next 10m maxsize 8g,

三，创建用户使用已经创建的表空间个临时表空间

create user kettle identified by kettle123 default tablespace tbs_kettle temporary tablespace temp_kettle;

四，删除表空间

DROP TABLESPACE TBS_LIHG INCLUDING CONTENTS  AND DATAFILES CASCADE CONSTRAINTS

五，查看表空间（包含临时表空间）

SELECT  * FROM dba_tablespaces

六，查看数据问题和临时表空间的数据文件

SELECT * FROM Dba_Data_Files;

SELECT * FROM Dba_Temp_Files;

七，查看和修改数据库默认表空间（即不给用户指定表空间是使用的默认表空间）
select * from DATABASE_PROPERTIES where property_name like 'DEFAULT%'
alter database default tablespace temp_new;

八，查看和修改用户默认表空间（用户指定的默认表空间）
select username,default_tablespace from user_users
alter user lihg default tablespace temp_lihg;

九，移动数据文件的位置，例如，移动表空间 tbs_lihg 中的一个数据文件

ALTER TABLESPACE tbs_lihg OFFLINE;

oracle>$ cp /opt/oracle/oradata/tbs_lihg.dbf /opt/oracle/oradata/mydata/tbs_lihg.dbf

ALTER TABLESPACE tbs_lihg RENAME DATAFILE '/opt/oracle/oradata/tbs_lihg.dbf' TO '/opt/oracle/oradata/mydata/tbs_lihg.dbf';

ALTER TABLESPACE tbs_lihg ONLINE;

十，查看表空间利用率

--查看所有数据文件

SELECT * FROM v$datafile WHERE NAME LIKE '%mudatareport3%'

--查看表空间的 所有数据文件

SELECT * FROM dba_Data_Files WHERE TABLESPACE_name IN ('TBS_MUDATAREPORT3','TMP_MUDATAREPORT3')

--查看表空间的 利用率

select total.tablespace_name,

　　round(total.MB, 2) as Total_MB,

　　round(total.MB - free.MB, 2) as Used_MB,

　　round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct

　　from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB

　　from dba_free_space

　　group by tablespace_name) free,

　　(select tablespace_name, sum(bytes) / 1024 / 1024 as MB

　　from dba_data_files

　　group by tablespace_name) total

　　where free.tablespace_name = total.tablespace_name

     AND total.tablespace_name IN ('TBS_MUDATAREPORT3');

十一，给表空间添加数据文件

---给现有的表空间添加数据文件

alter tablespace TBS_MUDATAREPORT3 

 add datafile '/oradata2/ORCL/pnlreport/tbs_pnlreport.dbf' size 2g autoextend on next 100m

十二,回收表空间碎片

oracle性能如何调整,如何回收表空间碎片

　　几种方法。

　　1、将表空间用compress=Y参数exp出来，将其中表truncate后，将表imp进去。

　　2、使用alter tablespace name coalesce;手动收缩一次。（最常用）

　　3、将表空间的pctincreace参改为大于0,让其自动收缩。一般改为1。

　　alter tablespace name storage (pctincrease 1);（最好不要用）

　　关于表空间pctincrease=1：

　　(1)不将pctincrease设为0，是因为SMON进程将只会定期将PCTINCREASE 不等于0的表空间的空闲碎块合并。如果设为0，将阻止SMON合并空闲碎块合并，这时，必须手工使用ALTER TABLESPACE XXX COALESCE;合并碎块。

　　(2)不将PCTINCREASE设为较大的数，仅仅用于防止新建的表使用大的PCTINCREASE，并造成空间使用的不可控。如果表空间中再也不用建新表，那么该参数只要不等于0，其他取值就没有任何影响了。

另外，PCTINCREASE与表空间的自动增长没有关系，表空间的自动增长是通过AUTOEXTEND ON来控制的，并且它是纯粹线形增长的(即规定增长多少，就永远每次增长多少)，不象表的PCTINCREASE一样会形成几何增长的可能。

回收碎片是指表truncate或者drop后释放出来的空间
不是delete后留下来的空间，smon是不能解决delete所浪费的空间的
Pctincrease不只是针对表空间的，一样可以对表进行设置
不过一般来说，对表设置为0，目前我还没有发现对表设置为非0值有任何好处。

十三,修改临时数据文件的属性

alter database tempfile '/oradata2/ORCL/temp_mudatareport.dbf' online;

十四,查看或修改当前用户的默认表空间

--数据库默认表空间（即不给用户指定表空间是使用的默认表空间）
select * from DATABASE_PROPERTIES where property_name like 'DEFAULT%'
alter database default tablespace temp_new;

--用户默认表空间（用户指定的默认表空间）
select username,default_tablespace from user_users
alter user lihg default tablespace temp_lihg;

十五，创建非标准块大小的表空间