11. Oracle Table Space

Creating and managing Table spaces

1. Create a table Space example

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

View Real table space creation statements;

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. Delete Table space

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; Be careful

The table space is deleted, and the data file is

[email protected] orcl]$ LL

Total 1590248

-RW-R-----1 Oracle DBA 9748480 05:11 CONTROL01.CTL

-RW-R-----1 Oracle DBA 9748480 05:11 CONTROL02.CTL

-RW-R-----1 Oracle DBA 52429312 03:16 Redo01.log

-RW-R-----1 Oracle DBA 52429312 03:16 Redo02.log

-RW-R-----1 Oracle DBA 52429312 05:11 Redo03.log

-RW-R-----1 Oracle DBA 10493952 04:41 RRR.DBF

-RW-R-----1 Oracle DBA 555753472 05:05 SYSAUX01.DBF

-RW-R-----1 Oracle DBA 775954432 05:08 SYSTEM01.DBF

-RW-R-----1 Oracle DBA 30416896 03:16 TEMP01.DBF

-RW-R-----1 Oracle DBA 10493952 03:50 TEST01.DBF

-RW-R-----1 Oracle DBA 10493952 05:07 TTT.DBF

-RW-R-----1 Oracle DBA 73408512 05:05 UNDOTBS01.DBF

-RW-R-----1 Oracle DBA 13115392 03:16 USERS01.DBF

See what tables are under the table space (RRR case sensitivity)

Select Owner,table_name from Dba_tables where tablespace_name= ' RRR ';

To view the user's default tablespace:

Select Default_tablespace from dba_users where username= ' SCOTT ';

Delete a table space while deleting a data file

Drop tablespace RRR including contents and datafiles; Be careful

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.

Data file information

SELECT * from Dba_data_files;

Associated data files and tablespace 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

The following are the results of 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. Enlarge the table space

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

Workaround:

Add data files

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; Auto Scale

Select Tablespace_name,file_name,bytes/1024/1024| | ' M ' from dba_data_files where tablespace_name= ' tttt ';

Or

ALTER DATABASE datafile '/U02/ORACLE/ORADATA/ORCL/TTTT02.DBF ' resize 100m;

Alter tablespace tttt drop datafile '/u02/oracle/oradata/orcl/tttt02.dbf ' size 1m;

This article is from the "Liang blog" blog, make sure to keep this source http://7038006.blog.51cto.com/7028006/1932102

11. Oracle Table Space