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;

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

假设数据库的标准块为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.小结
本着特殊问题特殊处理的原则，创建非系统默认的表空间需要事先做一些微小的调整。
另外注意，当同一套数据库系统中存在数据块大小不同的表空间的时候，维护上需要更多的关照。充分评估，不要增加不必要的负担。

zhuanzai :http://hi.baidu.com/hgliecnu/item/89e9cf49adc5e5a8de2a9f77