探索ORACLE_之表空間02_管理

表空間建立參考語法圖如下連線：

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的只能有一個，並且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 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.

探索ORACLE_之表空間02_管理

相關文章