一、创建表空间
SQL>Create smallfile/bigfile tablespace tbs_newstores
DATAFILE '/u01/app/oracle/oracle11g/tbs_newstores.dbf'
size 100m autoextend on next 10m maxsize 300m
logging
extent management local
segment space management auto
default nocompress;
二、更改表空间
(1)重命名表空间和数据文件
## CREATETABLESPACE
SQL>Create smallfile /bigfile tablespace TBS_NEWSTORES;
DATAFILE '/u01/app/oracle/oracle11g/tbs_newstores01.dbf' SIZE 50M,
'/u01/app/oracle/oracle11g/tbs_newstores02.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M;
## RENAMETABLESPACE
SQL>ALTER TABLESPACE TBS_NEWSTORES RENAME to TBS_NEWSTORE;
SQL>SELECT NAME,DATAFILE,STATUS FROM V$DATAFILE
WHERE NAME LIKE'%TBS_NEWSTORES%';
## MAKETABLESPACE OFFLINE
SQL>ALTER TABLESPACE TBS_NEWSTORE OFFLINE;
SQL>SELECT NAME,DATAFILE,STATUS FROM V$DATAFILE
WHERE NAME LIKE'%TBS_NEWSTORES%';
## RENAMEDATAFILE
SQL>! RENAME /u01/app/oracle/oracle11g/tbs_newstores01.dbf tbs_newstore01.dbf
SQL>! RENAME /u01/app/oracle/oracle11g/tbs_newstores02.dbf tbs_newstore02.dbf
SQL>SELECT T.NAME TABLESPACE_NAME, D.NAME DATAFILE_NAME
FROM V$TABLESPACE T JOIN V$DATAFILE D USING(TS#)
WHERE T.NAME=TBS_NEWSTORE;
## RENAMEDATAFILE_NAME
SQL>ALTER DATABASE RENAME FILE '/u01/app/oracle/oracle11g/tbs_newstores01.dbf'
TO '/u01/app/oracle/oracle11g/tbs_newstore01.dbf';
SQL>ALTER DATABASE RENAME FILE '/u01/app/oracle/oracle11g/tbs_newstores02.dbf'
TO '/u01/app/oracle/oracle11g/tbs_newstore02.dbf';
## MAKETABLESPACE ONLINE
SQL> ALTER TABLESPACE TBS_NEWSTORE ONLINE;
(2)表空间脱机和联机
ALTER TABLESPACE tablespacename offline [NORMAL | IMMEDIATE |TEMPORARY]
(3) 将表空间标记为只读
ALTER TABLESPACE tablespacename [READ ONLY | READ WRITE];
(4) 重新调整表空间的大小
ALTER DATABASE DATAFILE filename RESIZE n [M|G|T];
#eg.
SQL> alter database datafile '/oradata/users02.dbf' resize 20m;
--向表空间添加一个50M大小的数据文件
SQL>alter tablespace ts_newstore
Add datafile '/oradata/ts_newstore03.dbf' size 50m ;
--可以添加自动扩展句子,或在后面使用下列命令启动自动扩展
SQL>alter database datafile '/oradata/ts_newstore03.dbf'
Autoextendon next 50m maxsize 2G;
(5) OMF(Oracle-Managed Files)
要启用OMF,必须设置一部分或所有以下参数:
DB_CREATE_FILE_DEST --参数指定所有数据文件的默认位置
DB_CREATE_ONLINE_LOG_DEST_n --指定联机重做日志文件的默认位置
DB_RECOVERY_FILE_DEST --设置归档重做日志文件和备份文件的默认位置
转载于:https://blog.51cto.com/peenboo/1790365