ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 探索ORACLE_之表空间02_管理

探索ORACLE_之表空间02_管理

原创 Linux操作系统 作者:wuweilong 时间:2011-11-30 14:00:05 0 删除 编辑

探索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> select tablespace_name,file_name,blocks,bytes/1024/1024 M from dba_data_files where TABLESPACE_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> select tablespace_name,file_name,blocks,bytes/1024/1024 M from dba_data_files where TABLESPACE_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> select file_id,file_name,tablespace_name,blocks,bytes/1024/1024 bytes_M,autoextensible from dba_data_files where TABLESPACE_NAME='TEST001';

 

   FILE_ID FILE_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> select TABLESPACE_NAME,INITIAL_EXTENT/1024,NEXT_EXTENT/1024,EXTENT_MANAGEMENT from 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

 

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_MANAGEMENT from 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_properties where property_name = 'DEFAULT_TBS_TYPE';

 

PROPERTY_NAME                  PROPERTY_VALUE                           DESCRIPTION

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

DEFAULT_TBS_TYPE               SMALLFILE                                Default tablespace 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 from dba_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 BIG FLA ENC

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

         0 SYSTEM                         YES NO  YES

         1 UNDOTBS1                       YES NO  YES

         2 SYSAUX                         YES NO  YES

         4 USERS                          YES NO  YES

         3 TEMP                           NO  NO  YES

         6 TEST01                         YES NO  YES

         7 TEST001                        YES NO  YES

         8 TEST002                        YES NO  YES

         9 BIGFILE001                     YES YES YES

 

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

12:34:18 SQL> alter tablespace bigfile001 add datafile '/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 bigfile tablespace

 

Elapsed: 00:00:00.05

12:34:32 SQL>

 

12:53:05 SQL> select file_id,tablespace_name,bytes/1024/1024 bytes_M,autoextensible,relative_fno from dba_data_files;

 

   FILE_ID TABLESPACE_NAME         BYTES_M AUT RELATIVE_FNO

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

         4 USERS                         5 YES            4

         3 SYSAUX                      240 YES            3

         2 UNDOTBS1                     25 YES            2

         1 SYSTEM                      480 YES            1

         5 TEST01                     3584 NO             5

         6 TEST001                      10 NO             6

         7 TEST001                      10 YES            7

         8 TEST002                      10 NO             8

         9 BIGFILE001                    5 YES         1024

 

9 rows selected.

 

Elapsed: 00:00:00.04

 

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

12:38:37 SQL> alter tablespace BIGFILE001 autoextend on;

 

Tablespace altered.

 

12:42:59 SQL> select file_id,file_name,tablespace_name,blocks,bytes/1024/1024 bytes_M,autoextensible from dba_data_files where tablespace_name='BIGFILE001';

 

   FILE_ID FILE_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 * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

 

PROPERTY_NAME                  PROPERTY_VALUE  DESCRIPTION

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

DEFAULT_TEMP_TABLESPACE        TEMP            Name of default temporary tablespace

 

Elapsed: 00:00:00.01

 

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

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

 

Tablespace created.

 

13:24:13 SQL> select file_id,tablespace_name,file_name,bytes/1024/1024 byte_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 default temporary tablespace temp01;

 

Database altered.

 

Elapsed: 00:00:00.18

13:33:01 SQL>  select * from database_properties where property_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 test03 identified by oracle temporary tablespace TEMP;

 

User created.

13:37:23 SQL> select username,temporary_tablespace,ACCOUNT_STATUS from dba_users where ACCOUNT_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 temp tablespace group tmpgroup;

 

Tablespace altered.

 

Elapsed: 00:00:00.08

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

 

Tablespace altered.

 

Elapsed: 00:00:00.05

 

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

13:41:44 SQL> select * from dba_tablespace_groups;

 

GROUP_NAME                     TABLESPACE_NAME

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

TMPGROUP                       TEMP

TMPGROUP                       TEMP01

 

Elapsed: 00:00:00.02

 

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

13:41:59 SQL> alter database default temporary tablespace tmpgroup;

 

Database altered.

 

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

13:44:03 SQL> select * from database_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的只能有一个,并且activeundo表空间是不能offlindrop。如果未创建undo表空间oracle将使用system undo segment

 

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

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 tablespace undotbs2 datafile '/data2/undotbs2_01.dbf' size 10m;

 

Tablespace created.

 

13:54:26 SQL> select TABLESPACE_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 set undo_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$tablespace order by name;

 

       TS# NAME            INC BIG FLA ENC

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

         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 UNDOTBS1 including contents and datafiles;

 

Tablespace dropped.

 

Elapsed: 00:00:02.23

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

 

       TS# NAME            INC BIG FLA ENC

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

         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 UNDOTBS2 including contents and datafiles;

drop tablespace UNDOTBS2 including contents and datafiles

*

ERROR at line 1:

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

 

11 rows selected.

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20674423/viewspace-712426/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2010-01-08

  • 博文量
    249
  • 访问量
    1963742