探索ORACLE_之表空间02_管理

晨曦之光 发布于 2012/04/25 15:48
阅读 165
收藏 1

探索ORACLE_之表空间02_管理

表空间创建参考语法图如下连接:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

 

创建表空间的语法是:
CREATE [SMALLFILE|BIGFILE] [PERMANENT|TEMPORARY|UNDO] TABLESPACE
DATAFILE|TEMPFILE datafile_clause[,datafile_clause]
[EXTENT MANAGEMENT LOCAL]
[AUTOALLOCATE|UNIFORM SIZE integer [K|M]]
[SEGMENT SPACE MANAGEMENT AUTO|MANUAL]
[BLOCKSIZE integer[K|M]]
[MININUM EXTENT integer [K|M]]
[LOGGING|NOLOGGING]
[ONLINE|OFFLINE [NORMAL|TEMPORARY|IMMEDIATE]];

其中,datafile_clause子句(数据文件子句)的语法是:
path_filename_suffix' SIZE integer [K|M]
[REUSE]
[AUTOEXTEND OFF|ON]
[NEXT integer [K|M]]
[MAXSIZE UNLIMITED | integer [K|M]]

 

1.    创建管理标准(小文件)表空间

1.1 创建一个标准表空间

11:04:57 SQL> create tablespace test001 datafile'/data2/test001_01.dbf' size 10m;

Tablespace created.

11:13:29 SQL> selecttablespace_name,file_name,blocks,bytes/1024/1024 M from dba_data_files whereTABLESPACE_NAME='TEST001';

 

TABLESPACE_NAME     FILE_NAME                                    BLOCKS          M

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

TEST001             /data2/test001_01.dbf                          1280         10

Elapsed: 00:00:00.03

 

11:17:01 SQL> alter tablespace test001 add datafile'/data2/test001_02.dbf' size 10m;

 

Tablespace altered.

 

1.2为表空间添加数据文件

Elapsed: 00:00:00.89

11:30:05 SQL> selecttablespace_name,file_name,blocks,bytes/1024/1024 M from dba_data_files whereTABLESPACE_NAME='TEST001';

 

TABLESPACE_NAME     FILE_NAME                                    BLOCKS          M

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

TEST001             /data2/test001_01.dbf                          1280        10

TEST001             /data2/test001_02.dbf                          1280         10

 

Elapsed: 00:00:00.02

11:30:12 SQL>

 

1.3修改表空间数据文件为自动扩展

11:42:35 SQL> alter database datafile'/data2/test001_02.dbf' autoextend on next 2m maxsize 200m;

 

Database altered.

 

Elapsed: 00:00:00.03

12:11:33 SQL> selectfile_id,file_name,tablespace_name,blocks,bytes/1024/1024 bytes_M,autoextensiblefrom dba_data_files where TABLESPACE_NAME='TEST001';

 

   FILE_IDFILE_NAME                TABLESPACE_NAME          BLOCKS   BYTES_M AUT

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

         6/data2/test001_01.dbf     TEST001                    1280         10 NO

         7/data2/test001_02.dbf     TEST001                    1280         10 YES

 

Elapsed: 00:00:00.04

12:11:48 SQL>

 

1.4通过指定段的大小来创建表空间

12:21:53 SQL> selectTABLESPACE_NAME,INITIAL_EXTENT/1024,NEXT_EXTENT/1024,EXTENT_MANAGEMENT fromdba_tablespaces;

 

TABLESPACE_NAME     INITIAL_EXTENT/1024 NEXT_EXTENT/1024 EXTENT_MAN

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

SYSTEM                                64                  LOCAL

UNDOTBS1                              64                  LOCAL

SYSAUX                                64                  LOCAL

TEMP                                1024             1024 LOCAL

USERS                                 64                  LOCAL

TEST01                                64                  LOCAL

TEST001                               64                  LOCAL

 

7 rows selected.

 

Elapsed: 00:00:00.03

12:22:17 SQL> create tablespace test002 datafile'/data2/test002_01.dbf' size 10m extent management local uniform size 1m;

 

Tablespace created.

 

Elapsed: 00:00:00.69

12:23:34 SQL> select TABLESPACE_NAME,INITIAL_EXTENT/1024,NEXT_EXTENT/1024,EXTENT_MANAGEMENTfrom dba_tablespaces;

 

TABLESPACE_NAME     INITIAL_EXTENT/1024 NEXT_EXTENT/1024 EXTENT_MAN

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

SYSTEM                                64                  LOCAL

UNDOTBS1                              64                  LOCAL

SYSAUX                                64                  LOCAL

TEMP                                1024             1024 LOCAL

USERS                                 64                  LOCAL

TEST01                                64                  LOCAL

TEST001                               64                  LOCAL

TEST002                             1024             1024 LOCAL

 

8 rows selected.

 

Elapsed: 00:00:00.01

12:23:39 SQL>

 

2.    创建管理大文件表空间

2.1 查询数据库创建表空间的默认信息,该视图得知为小文件表空间,当然这个是可以修改的:

12:27:23 SQL> select * from database_propertieswhere property_name = 'DEFAULT_TBS_TYPE';

 

PROPERTY_NAME                  PROPERTY_VALUE                           DESCRIPTION

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

DEFAULT_TBS_TYPE               SMALLFILE                                Defaulttablespace type

 

2.2 创建一个大文件表空间:

create bigfile tablespace bigfile001 datafile'/data2/bigfile001.dbf' size 5m;

 

Tablespace created.

 

Elapsed: 00:00:04.37

 

2.3 查看表空间类型,是否为大文件表空间

12:31:24 SQL> SELECT tablespace_name, bigfile fromdba_tablespaces;

 

TABLESPACE_NAME     BIG

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

SYSTEM              NO

UNDOTBS1            NO

SYSAUX              NO

TEMP                NO

USERS               NO

TEST01              NO

TEST001             NO

TEST002             NO

BIGFILE001           YES

 

通过另外一个视图查看

12:32:18 SQL> select * from v$tablespace;

 

       TS#NAME                           INC BIGFLA ENC

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

         0SYSTEM                         YESNO  YES

         1UNDOTBS1                       YESNO  YES

         2SYSAUX                         YES NO  YES

         4USERS                          YESNO  YES

         3TEMP                           NO  NO  YES

         6TEST01                         YESNO  YES

         7TEST001                        YESNO  YES

         8TEST002                        YESNO  YES

        9 BIGFILE001                    YES YES YES

 

2.4 大文件表空间只允许有一个数据文件,所以相对于的文件号也是只有固定的1024

12:34:18 SQL> alter tablespace bigfile001 adddatafile '/data2/bigfile002.dbf' size 4m;

alter tablespace bigfile001 add datafile'/data2/bigfile002.dbf' size 4m

*

ERROR at line 1:

ORA-32771: cannot add file to bigfiletablespace

 

Elapsed: 00:00:00.05

12:34:32 SQL>

 

12:53:05 SQL> selectfile_id,tablespace_name,bytes/1024/1024 bytes_M,autoextensible,relative_fnofrom dba_data_files;

 

   FILE_IDTABLESPACE_NAME         BYTES_M AUTRELATIVE_FNO

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

         4USERS                         5 YES            4

         3SYSAUX                      240 YES            3

         2 UNDOTBS1                     25 YES            2

         1SYSTEM                      480 YES            1

         5TEST01                     3584 NO             5

         6TEST001                      10 NO             6

         7TEST001                      10 YES            7

         8TEST002                      10 NO             8

        9 BIGFILE001                    5YES         1024

 

9 rows selected.

 

Elapsed: 00:00:00.04

 

2.5 这样的话,oracle建议用户将大数据文件表空间设置为可自动扩展,当然在创建的时候也可以指定自动扩展属性:

12:38:37 SQL> alter tablespace BIGFILE001autoextend on;

 

Tablespace altered.

 

12:42:59 SQL> selectfile_id,file_name,tablespace_name,blocks,bytes/1024/1024 bytes_M,autoextensiblefrom dba_data_files where tablespace_name='BIGFILE001';

 

   FILE_IDFILE_NAME                TABLESPACE_NAME         BLOCKS    BYTES_M AUT

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

         9/data2/bigfile001.dbf     BIGFILE001                  640          5 YES

 

Elapsed: 00:00:00.03

12:43:19 SQL>

 

2.6 那么它可以自动扩展到多大呢?

在这里引入一个参数: db_block_size
12:45:07 SQL> show parameter db_block_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
 
单个文件最大限制的对应关系
文件系统(块)       文件大小限制       文件系统大小限制
ext2/3 (2K)      256G             8T
ext2/3 (4K)      2T               16T
ext2/3 (8K)      64T              32T
ReiserFS 3.6      1E               16T
 
检查操作系统的文件块大小,这个大小是在创建文件系统的时候可以指定:
[root@ora10g admin]# tune2fs -l /dev/sdb |grep Block
Block count:              2097152
Block size:               4096
Blocks per group:         32768
由以上得出,我们可创建的单个文件的大小为2TB,同时我们上面创建的那个大文件表空间可自动扩展的最大大小同样也为2TB。
 
超过操作系统规定的单个文件大小将报如下错误:
12:56:12 SQL> create bigfile tablespace bigfile002 datafile '/data2/bigfile002.dbf' size 3T reuse;
create bigfile tablespace bigfile002 datafile '/data2/bigfile002.dbf' size 3T reuse
*
ERROR at line 1:
ORA-01119: error in creating database file '/data2/bigfile002.dbf'
ORA-27044: unable to write the header block of file
Linux Error: 27: File too large
Additional information: 4
 
我们创建2T以下的表空间是可以创建的,没有报错:
13:03:55 SQL> create bigfile tablespace bigfile002 datafile '/data2/bigfile002.dbf' size 1900G reuse;

Tablespace created.

 
 
 

3.    创建管理临时表空间

3.1 查看默认的临时表空间

13:19:00 SQL> select * fromdatabase_properties where property_name='DEFAULT_TEMP_TABLESPACE';

 

PROPERTY_NAME                  PROPERTY_VALUE  DESCRIPTION

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

DEFAULT_TEMP_TABLESPACE        TEMP            Name of default temporarytablespace

 

Elapsed: 00:00:00.01

 

3.2 创建临时表空间,并切换到新的临时表空间

13:21:01 SQL> create temporarytablespace temp01 tempfile '/data2/temp01_01.dbf' size 10m;

 

Tablespace created.

 

13:24:13 SQL> select file_id,tablespace_name,file_name,bytes/1024/1024byte_M from dba_temp_files;

 

  FILE_ID TABLESPACE_NAME FILE_NAME                          BYTE_M

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

        1 TEMP           /oradata/wwl/temp01.dbf                20

         2 TEMP01          /data2/temp01_01.dbf                   10 ----新创建的临时表空间

 

Elapsed: 00:00:00.07

 

注意:

新创建的临时表空间可以作为全局临时表空间,同时也可以做为单个用户的临时表空间,甚至可以作为一个临时表空间组中的一个临时表空间成员

 

3.3 切换全局临时表空间

13:24:32 SQL> alter database defaulttemporary tablespace temp01;

 

Database altered.

 

Elapsed: 00:00:00.18

13:33:01 SQL>  select * from database_properties whereproperty_name='DEFAULT_TEMP_TABLESPACE';

 

PROPERTY_NAME                  PROPERTY_VALUE  DESCRIPTION

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

DEFAULT_TEMP_TABLESPACE        TEMP01          Name of default temporary tablespace

 

3.4 创建新用户指定非默认临时表空间

13:35:22 SQL> create user test03identified by oracle temporary tablespace TEMP;

 

User created.

13:37:23 SQL> selectusername,temporary_tablespace,ACCOUNT_STATUS from dba_users whereACCOUNT_STATUS='OPEN';

 

USERNAME  TEMPORARY_TABLE ACCOUNT_STATUS

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

SYSTEM    TEMP01          OPEN

SYS       TEMP01          OPEN

TEST03     TEMP            OPEN

TEST01    TEMP01          OPEN

 

3.5 通过临时表空间组对临时表空间进行管理,可以实现临时表空间负载

创建临时表空间组

 

13:37:48 SQL> alter tablespace temptablespace group tmpgroup;

 

Tablespace altered.

 

Elapsed: 00:00:00.08

13:41:23 SQL> alter tablespace temp01tablespace group tmpgroup;

 

Tablespace altered.

 

Elapsed: 00:00:00.05

 

3.5.1 查询临时表空间组中的表空间

13:41:44 SQL> select * fromdba_tablespace_groups;

 

GROUP_NAME                     TABLESPACE_NAME

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

TMPGROUP                       TEMP

TMPGROUP                       TEMP01

 

Elapsed: 00:00:00.02

 

3.5.2 将临时表空间组设为数据库默认临时表空间

13:41:59 SQL> alter database defaulttemporary tablespace tmpgroup;

 

Database altered.

 

3.5.3 查询当前默认临时表空间

13:44:03 SQL> select * fromdatabase_properties where property_name='DEFAULT_TEMP_TABLESPACE';

 

PROPERTY_NAME                  PROPERTY_VALUE  DESCRIPTION

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

DEFAULT_TEMP_TABLESPACE        TMPGROUP        Name of default temporary tablespace

 

Elapsed: 00:00:00.02

 

4.    创建管理Undo表空间:  由于undo表空间的管理非常复杂,在这里不做详细论述,将在后期的体系架构里面做详细说明。

在单个数据库中undo表空间可以有多个,但处于active的只能有一个,并且active的undo表空间是不能offlin和drop。如果未创建undo表空间oracle将使用system undo segment。

 

4.1 查看默认undo表空间   --可以看到当前undotbs1为active表空间。

13:44:05 SQL> show parameter undo

 

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

13:50:36 SQL>

 

4.2 创建新的undo表空间并做切换。

4.2.1 创建新的UNDO表空间

13:50:36 SQL> create undo tablespaceundotbs2 datafile '/data2/undotbs2_01.dbf' size 10m;

 

Tablespace created.

 

13:54:26 SQL> selectTABLESPACE_NAME,STATUS,CONTENTS from dba_tablespaces where CONTENTS='UNDO';

 

TABLESPACE_NAME STATUS    CONTENTS

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

UNDOTBS1        ONLINE    UNDO

UNDOTBS2        ONLINE    UNDO

                                                               

4.2.2 切换undo表空间

13:54:49 SQL> alter system setundo_tablespace='UNDOTBS2';

 

System altered.

 

Elapsed: 00:00:00.29

13:55:48 SQL> show parameter undo;

 

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS2

13:55:57 SQL>

 

4.2.3 删除非默认undo表空间:

18:39:25 SQL> select * from v$tablespaceorder by name;

 

      TS# NAME            INC BIG FLAENC

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

        9 BIGFILE001      YES YES YES

        2 SYSAUX          YES NO  YES

        0 SYSTEM          YES NO  YES

        3 TEMP            NO NO  YES

       11 TEMP01          NO  NO  YES

        7 TEST001         YES NO  YES

        8 TEST002         YES NO  YES

        6 TEST01          YES NO  YES

         1 UNDOTBS1        YES NO YES

         5 UNDOTBS2        YES NO YES

        4 USERS           YES NO  YES

 

18:40:03 SQL> drop tablespace UNDOTBS1including contents and datafiles;

 

Tablespace dropped.

 

Elapsed: 00:00:02.23

18:42:37 SQL> select * from v$tablespaceorder by name;

 

      TS# NAME            INC BIG FLAENC

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

        9 BIGFILE001      YES YES YES

        2 SYSAUX          YES NO  YES

        0 SYSTEM          YES NO  YES

        3 TEMP            NO  NO  YES

       11 TEMP01          NO  NO  YES

        7 TEST001         YES NO  YES

        8 TEST002         YES NO  YES

        6 TEST01          YES NO  YES

         5 UNDOTBS2        YES NO YES

        4 USERS           YES NO  YES

 

10 rows selected.

 

Elapsed: 00:00:00.02

18:42:43 SQL>

 

4.2.4 删除当前undo表空间则报错

18:42:43 SQL> drop tablespace UNDOTBS2including contents and datafiles;

drop tablespace UNDOTBS2 including contentsand datafiles

*

ERROR at line 1:

ORA-30013: undo tablespace'UNDOTBS2' is currently in use

 

11 rows selected.

 

 

 

 


原文链接:http://blog.csdn.net/wuweilong/article/details/7026946
加载中
返回顶部
顶部