11、oracle 表空间

wangyan188

　　创建和管理表空间
　　1、创建表空间例子
　　select name from v$datafile;
　　SQL> select name from v$datafile;
　　NAME
　　--------------------------------------------------------------------------------
　　/u02/oracle/oradata/orcl/system01.dbf
　　/u02/oracle/oradata/orcl/sysaux01.dbf
　　/u02/oracle/oradata/orcl/undotbs01.dbf
　　/u02/oracle/oradata/orcl/users01.dbf

　　create tablespace test datafile '/u02/oracle/oradata/orcl/test01.dbf'>　　select name from v$datafile;
　　set long 1000
　　set pages 1000
　　set lines 120
　　查看真正的表空间创建语句；
　　select dbms_metadata.get_ddl('TABLESPACE','TEST') from dual;
　　CREATE TABLESPACE "TEST" DATAFILE

　　'/u02/oracle/oradata/orcl/test01.dbf'>　　AUTOEXTEND ON NEXT 1048576 MAXSIZE 32767M
　　LOGGING ONLINE PERMANENT BLOCKSIZE 8192
　　EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
　　NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO

　　create tablespace ttt datafile '/u02/oracle/oradata/orcl/ttt.dbf'>
　　create tablespace rrr datafile '/u02/oracle/oradata/orcl/rrr.dbf'>　　select dbms_metadata.get_ddl('TABLESPACE','TTT') from dual;
　　CREATE TABLESPACE "TTT" DATAFILE

　　'/u02/oracle/oradata/orcl/ttt.dbf'>　　LOGGING ONLINE PERMANENT BLOCKSIZE 8192
　　EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
　　NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
　　select dbms_metadata.get_ddl('TABLESPACE','RRR') from dual;
　　CREATE TABLESPACE "RRR" DATAFILE

　　'/u02/oracle/oradata/orcl/rrr.dbf'>　　LOGGING ONLINE PERMANENT BLOCKSIZE 8192

　　EXTENT MANAGEMENT LOCAL UNIFORM>　　NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
　　create table t tablespace ttt  as select * from scott.emp;
　　create table r tablespace rrr  as select * from scott.emp;
　　insert into t select * from t;
　　insert into r select * from r;
　　col SEGMENT_NAME for a20;
　　select SEGMENT_NAME,FILE_ID,EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME in ('T','R');
　　2、删除表空间
　　drop tablespace ttt;
　　SQL> drop tablespace ttt;
　　drop tablespace ttt
　　*
　　ERROR at line 1:
　　ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
　　drop tablespace ttt including contents; //小心
　　删除了表空间，数据文件还在
　　[oracle@oracle250 orcl]$ ll
　　total 1590248
　　-rw-r----- 1 oracle dba   9748480 May 29 05:11 control01.ctl
　　-rw-r----- 1 oracle dba   9748480 May 29 05:11 control02.ctl
　　-rw-r----- 1 oracle dba  52429312 May 29 03:16 redo01.log
　　-rw-r----- 1 oracle dba  52429312 May 29 03:16 redo02.log
　　-rw-r----- 1 oracle dba  52429312 May 29 05:11 redo03.log
　　-rw-r----- 1 oracle dba  10493952 May 29 04:41 rrr.dbf
　　-rw-r----- 1 oracle dba 555753472 May 29 05:05 sysaux01.dbf
　　-rw-r----- 1 oracle dba 775954432 May 29 05:08 system01.dbf
　　-rw-r----- 1 oracle dba  30416896 May 29 03:16 temp01.dbf
　　-rw-r----- 1 oracle dba  10493952 May 29 03:50 test01.dbf
　　-rw-r----- 1 oracle dba  10493952 May 29 05:07 ttt.dbf
　　-rw-r----- 1 oracle dba  73408512 May 29 05:05 undotbs01.dbf
　　-rw-r----- 1 oracle dba  13115392 May 29 03:16 users01.dbf
　　查看表空间下面有哪些表（RRR大小写区分）
　　select owner,table_name from dba_tables where tablespace_name='RRR';
　　查看用户的默认表空间：
　　select default_tablespace from dba_users where username='SCOTT';
　　删除表空间，同时删除数据文件
　　drop tablespace rrr including contents and datafiles; //小心
　　desc dba_tablespaces;
　　select TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces order by 1;
　　SQL> select TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT from dba_tab
　　lespaces order by 1;
　　TABLESPACE_NAME        EXTENT_MAN ALLOCATIO SEGMEN
　　------------------------------ ---------- --------- ------
　　RRR       LOCAL  UNIFORM   AUTO
　　SYSAUX       LOCAL  SYSTEM    AUTO
　　SYSTEM       LOCAL  SYSTEM    MANUAL
　　TEMP       LOCAL  UNIFORM   MANUAL
　　TEST       LOCAL  SYSTEM    AUTO
　　UNDOTBS1       LOCAL  SYSTEM    MANUAL
　　USERS       LOCAL  SYSTEM    AUTO
　　7 rows selected.
　　数据文件信息
　　select * from dba_data_files;
　　关联数据文件及表空间文件
　　col FILE_NAME for a50;
　　col TABLESPACE_NAME for a10;
　　select b.FILE_NAME,a.TABLESPACE_NAME,a.EXTENT_MANAGEMENT,a.ALLOCATION_TYPE,a.SEGMENT_SPACE_MANAGEMENT from dba_tablespaces  a ,dba_data_files b  where a.TABLESPACE_NAME=b. TABLESPACE_NAME order by 1;
　　SQL> col FILE_NAME for a50;
　　SQL> /
　　FILE_NAME   TABLESPACE EXTENT_MAN ALLOCATIO SEGMEN
　　-------------------------------------------------- ---------- ---------- --------- ------
　　/u02/oracle/oradata/orcl/rrr.dbf   RRR      LOCAL UNIFORM   AUTO
　　/u02/oracle/oradata/orcl/sysaux01.dbf   SYSAUX     LOCAL SYSTEM    AUTO
　　/u02/oracle/oradata/orcl/system01.dbf   SYSTEM     LOCAL SYSTEM    MANUAL
　　/u02/oracle/oradata/orcl/test01.dbf   TEST       LOCAL SYSTEM    AUTO
　　/u02/oracle/oradata/orcl/undotbs01.dbf   UNDOTBS1   LOCAL SYSTEM    MANUAL
　　/u02/oracle/oradata/orcl/users01.dbf   USERS      LOCAL SYSTEM    AUTO
　　下面是ASM的结果：
　　col FILE_NAME for a40;
　　col TABLESPACE_NAME for a10;
　　FILE_NAME TABLESPACE EXTENT_MAN ALLOCATIO SEGMEN
　　---------------------------------------- ---------- ---------- --------- ------
　　+DATA/orcl/datafile/example.265.94330143 EXAMPLE    LOCAL      SYSTEM AUTO
　　3
　　+DATA/orcl/datafile/sysaux.257.943301251 SYSAUX     LOCAL      SYSTEM AUTO
　　+DATA/orcl/datafile/system.256.943301251 SYSTEM     LOCAL      SYSTEM MANUAL
　　+DATA/orcl/datafile/tbs.dbf         TBS_16K    LOCAL      SYSTEM AUTO
　　+DATA/orcl/datafile/undotbs1.258.9433012 UNDOTBS1   LOCAL      SYSTEM MANUAL
　　51
　　+DATA/orcl/datafile/users.259.943301251  USERS    LOCAL      SYSTEM AUTO
　　3、扩大表空间

　　create tablespace tttt datafile '/u02/oracle/oradata/orcl/tttt.dbf'>　　create table t tablespace tttt as select * from scott.emp;
　　insert into t select * from t;
　　SQL> /
　　insert into t select * from t
　　*
　　ERROR at line 1:
　　ORA-01653: unable to extend table SYS.T by 8 in tablespace TTTT
　　解决方法：
　　增加数据文件

　　alter tablespace tttt add datafile '/u02/oracle/oradata/orcl/tttt02.dbf'>
　　alter tablespace tttt add datafile '/u02/oracle/oradata/orcl/tttt03.dbf'>　　select tablespace_name,file_name,bytes/1024/1024||'M' from dba_data_files where tablespace_name='TTTT';
　　或者
　　alter database datafile '/u02/oracle/oradata/orcl/tttt02.dbf' resize 100m;

　　alter tablespace tttt drop datafile '/u02/oracle/oradata/orcl/tttt02.dbf'>