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;
十五，创建非标准块大小的表空间
假设数据库的标准块为8K，现在要创建一个块大小为16k的表空间 默认情况下创建的表空间使用的数据块大小是8K，数据库默认的数据块大小由db_block_size参数决定。如果我们想要创建的表空间超出这个藩篱，首先需要调整数据库的参数db_nk_cache_size。以创建数据库块大小为16K的表空间为例，展示一下这个调整过程。1.查看当前的数据库大小
sys@ora10g> show parameter db_block_size
NAME TYPE VALUE
--------------------- -------------------- -------------------
db_block_size integer 8192
2.查看参数db_16k_cache_size系统默认值
sys@ora10g> show parameter db_16k_cache_size
NAME TYPE VALUE
--------------------- -------------------- -------------------
db_16k_cache_size big integer 0
该参数默认值为0，表示未启用。
3.创建数据库高速缓存区
sys@ora10g> alter system set db_16k_cache_size=1M scope=both;
System altered.
sys@ora10g> show parameter db_16k_cache_size
NAME TYPE VALUE
-------------------- -------------------- --------------------
db_16k_cache_size big integer 32M
即使修改时指定的是1M，修改之后系统会自动给出一个最小的32M大小。
4.添加数据库块为16K的非标准块表空间
在前期准备工作完成的情况下，我们便可以使用“blocksize 16k”参数完成表空间的创建。
sys@ora10g> create tablespace DATA01 datafile '/u01/app/oracle/oradata/ora10g/DATA01_01.dbf' size 256M uniform. size 4M blocksize 16K;
sys@ora10g> select dbms_metadata.get_ddl('TABLESPACE','DATA01') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','DATA01')
---------------------------------------------------------------------
CREATE TABLESPACE "DATA01" DATAFILE
'/u01/app/oracle/oradata/ora10g/DATA01_01.dbf' SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE 16384
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 4194304 SEGMENT SPACE MANAGEMENT AUTO
5.未对db_16k_cache_size参数进行设置的ORA-29339报错信息
如果在创建表空间之前没有对db_16k_cache_size参数进行调整，将会收到如下ORA-29339报错。
sys@ora10g> create tablespace DATA01 datafile '/u01/app/oracle/oradata/ora10g/DATA01_01.dbf' size 256M uniform. size 4M blocksize 16K;
create tablespace DATA01 datafile '/u01/app/oracle/oradata/ora10g/DATA01_01.dbf' size 256M uniform. size 4M blocksize 16K
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
6.小结
本着特殊问题特殊处理的原则，创建非系统默认的表空间需要事先做一些微小的调整。
另外注意，当同一套数据库系统中存在数据块大小不同的表空间的时候，维护上需要更多的关照。充分评估，不要增加不必要的负担。