oracle 11G 读书笔记之 tablespace

To create a bigfile tablespace, specify the BIGFILE keyword of the CREATE TABLESPACE
statement (CREATE BIGFILE TABLESPACE ...). Oracle Database automatically creates a
locally managed tablespace with automatic segment space management. You can, but
need not, specify EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO in this
statement. However, the database returns an error if you specify EXTENT MANAGEMENT
DICTIONARY or SEGMENT SPACE MANAGEMENT MANUAL. The remaining syntax of the
statement is the same as for the CREATE TABLESPACE statement, but you can only
specify one datafile. For example:
CREATE BIGFILE TABLESPACE bigtbs
DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G

The following statement indicates that all tables created in the tablespace are to use
OLTP compression, unless otherwise specified:
CREATE TABLESPACE ... DEFAULT COMPRESS FOR OLTP ...

CREATE TABLESPACE securespace
DATAFILE '/u01/app/oracle/oradata/orcl/secure01.dbf' SIZE 100M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);

The following statement creates the same tablespace with the AES256 algorithm:

■ AES256
■ AES192
■ AES128
■ 3DES168

CREATE TABLESPACE securespace
DATAFILE '/u01/app/oracle/oradata/orcl/secure01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);


When recovering a database with encrypted tablespaces (for example after a
SHUTDOWN ABORT or a catastrophic error that brings down the database instance),
Creating Tablespaces
14-10 Oracle Database Administrator's Guide
you must open the Oracle wallet after database mount and before database open,
so the recovery process can decrypt data blocks and redo.

temporary tablespace group
create temporary tablespace lmtemp2 tempfile 'F:\oracle\product\10.2.0\oradata\orcl\lmtemp01.dbf' size 50M tablespace group group1;

alter tablespace temp tablespace group group1;

CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf' SIZE 25M
TABLESPACE GROUP group1;

The following statement also adds a tablespace to an existing group, but in this case
because tablespace lmtemp2 already belongs to group1, it is in effect moved from
group1 to group2:
ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;
Now group2 contains both lmtemp and lmtemp2, while group1 consists of only tmtemp3.
You can remove a tablespace from a group as shown in the following statement:
ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';
Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer
any members of group1, this results in the implicit deletion of group1.

Use the ALTER DATABASE...DEFAULT TEMPORARY TABLESPACE statement to assign a
tablespace group as the default temporary tablespace for the database. For example:
ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;

If you transport a tablespace that is in FORCE LOGGING mode to another database, the
new tablespace will not maintain the FORCE LOGGING mode.

-----------------------------------------------------------rename datafile or reallocate datafile---------------------------------
SYS@ORCL:XIAOEP> alter tablespace test offline;

表空间已更改。

SYS@ORCL:XIAOEP> alter tablespace test rename datafile 'F:\oracle\product\10.2.0
\oradata\orcl\test01.dbf' to 'F:\oracle\product\10.2.0\oradata\test01.dbf';

表空间已更改。

SYS@ORCL:XIAOEP> alter tablespace test online;

表空间已更改。

SYS@ORCL:XIAOEP> alter tablespace test offline;


SYS@ORCL:XIAOEP> alter tablespace test rename datafile 'F:\oracle\product\10.2.0
\oradata\test01.dbf' to 'F:\oracle\product\10.2.0\oradata\orcl\test01.dbf';

表空间已更改。

SYS@ORCL:XIAOEP> alter tablespace test online;
alter tablespace test online
*
第 1 行出现错误:
ORA-01113: 文件 11 需要介质恢复
ORA-01110: 数据文件 11: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST01.DBF'

--这是因为没有copy最新的数据文件到新位置

SYS@ORCL:XIAOEP> alter tablespace test online;

表空间已更改。

----------------------------------------------------------------------------------------------------------------------------------