创建和管理表空间

创建和管理表空间

Database Administration>>Administrator’s Guide >>14 Managing Tablespaces>>Creating Tablespaces

1.创建临时表空间组，并设置为默认临时表空间

SYSTEM@PROD3 > select file_name,tablespace_name from dba_temp_files;


FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/home/oracle/app/oradata/PROD3/temp01.dbf                    TEMPTS1

SYSTEM@PROD3 > create temporary tablespace temp1
  2  tempfile '/home/oracle/app/oradata/PROD3/temp1.dbf' size 20m 
  3  autoextend on tablespace group tmp_grp;

Tablespace created.

SYSTEM@PROD3 >  create temporary tablespace temp2
  2  tempfile '/home/oracle/app/oradata/PROD3/temp2.dbf' size 20m 
  3  autoextend on tablespace group tmp_grp;

Tablespace created.

SYSTEM@PROD3 > alter database default temporary tablespace tmp_grp;

Database altered.

SYSTEM@PROD3 >  select * from dba_tablespace_groups;


GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TMP_GRP                        TEMP1
TMP_GRP                        TEMP2

SYSTEM@PROD3 > col PROPERTY_VALUE for a30;
SYSTEM@PROD3 > select PROPERTY_NAME,PROPERTY_VALUE from database_properties;


PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
DICT.BASE                      2
DEFAULT_TEMP_TABLESPACE        TMP_GRP

2.创建bigfile tablespace 

SYS@PROD3 > select file_name,tablespace_name from dba_data_files;


FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/home/oracle/app/oradata/PROD3/system01.dbf                  SYSTEM
/home/oracle/app/oradata/PROD3/sysaux01.dbf                  SYSAUX
/home/oracle/app/oradata/PROD3/undotbs01.dbf                 UNDOTBS
/home/oracle/app/oradata/PROD3/users01.dbf                   USERS

SYS@PROD3 > create bigfile tablespace example
  2  datafile '/home/oracle/app/oradata/PROD3/example01.dbf' size 400m 
  3  autoextend on maxsize 4t
  4  uniform size 1m;


Tablespace created.

drop tablespace example including contents and datafiles;

这些都蛮简单相信不会难倒大家。

--原创文章，切勿转载