创建和管理表空间

来源：互联网发布：网络推广都做些什么编辑：程序博客网时间：2024/04/30 12:04

创建和管理表空间

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;

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

--原创文章，切勿转载

1 0