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

+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

+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;

原文链接：https://www.f2er.com/oracle/208987.html

猜你在找的Oracle相关文章