11、oracle 表空间
|
创建和管理表空间 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' size 10m autoextend on next 1m; 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' SIZE 10485760 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' size 10m; create tablespace rrr datafile '/u02/oracle/oradata/orcl/rrr.dbf' size 10m uniform size 1m; select dbms_metadata.get_ddl('TABLESPACE','TTT') from dual; CREATE TABLESPACE "TTT" DATAFILE '/u02/oracle/oradata/orcl/ttt.dbf' SIZE 10485760 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' SIZE 10485760 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 DEFAULT 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,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' size 1m; 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' size 1m; alter tablespace tttt add datafile '/u02/oracle/oradata/orcl/tttt03.dbf' size 1m autoextend on next 1m; 自动扩展 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' size 1m; （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |