单实例和RAC下创建表空间的小区别

单实例和RAC下创建表空间的小区别

单实例下：
SQL> select instance_number ,instance_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 orcl

SQL> show parameter db_c

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 0
db_create_file_dest                  string       ------------------------指定数据文件默认存放位置
db_create_online_log_dest_1          string ------------------------指定redo和控制文件默认存放位置
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/pmsdb/oracle/oradata/orcl/system01.dbf
/pmsdb/oracle/oradata/orcl/sysaux101.dbf
/pmsdb/oracle/oradata/orcl/undotbs101.dbf
/pmsdb/oracle/oradata/orcl/users101.dbf
/pmsdb/oracle/oradata/orcl/user102.dbf
/pmsdb/oracle/oradata/orcl/mao01.dbf
/pmsdb/oracle/oradata/orcl/mao02.dbf

7 rows selected.

SQL> create tablespace tbs;
create tablespace tbs
                    *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause

SQL> alter system set db_create_file_dest='/pmsdb/oracle/oradata/orcl/';

System altered.

SQL>  create tablespace tbs;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/pmsdb/oracle/oradata/orcl/system01.dbf
/pmsdb/oracle/oradata/orcl/sysaux101.dbf
/pmsdb/oracle/oradata/orcl/undotbs101.dbf
/pmsdb/oracle/oradata/orcl/users101.dbf
/pmsdb/oracle/oradata/orcl/user102.dbf
/pmsdb/oracle/oradata/orcl/mao01.dbf
/pmsdb/oracle/oradata/orcl/mao02.dbf
/pmsdb/oracle/oradata/orcl/PTLD1/datafile/o1_mf_tbs_7hqrlckm_.dbf

8 rows selected.

SQL> set linesize 200
SQL> col name for a50
SQL> select name,bytes/1024/1024 from v$datafile;

NAME                                               BYTES/1024/1024
-------------------------------------------------- ---------------
/pmsdb/oracle/oradata/orcl/system01.dbf                        990
/pmsdb/oracle/oradata/orcl/sysaux101.dbf                       860
/pmsdb/oracle/oradata/orcl/undotbs101.dbf                      440
/pmsdb/oracle/oradata/orcl/users101.dbf                    1116.25
/pmsdb/oracle/oradata/orcl/user102.dbf                          10
/pmsdb/oracle/oradata/orcl/mao01.dbf                           100
/pmsdb/oracle/oradata/orcl/mao02.dbf                           100
/pmsdb/oracle/oradata/orcl/PTLD1/datafile/o1_mf_tb             100
s_7hqrlckm_.dbf

8 rows selected.

RAC下：

SQL> select instance_number,instance_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- --------------------------------
              2 ORA11G2

SQL> show parameter db_create

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_create_file_dest                  string
+DATA_ORA11G
db_create_online_log_dest_1          string

db_create_online_log_dest_2          string

db_create_online_log_dest_3          string

db_create_online_log_dest_4          string

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------

db_create_online_log_dest_5          string

 

由此可见，在rac里，参数db_create_file_dest默认已经被指定好了，而在单实例中，参数db_create_file_dest 是需要手动指定的。因为这就解释了rac中创建表空间时不需要写目录和名字，原来在单实例中也是可以的。

 

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

转载于:http://blog.itpub.net/24500180/viewspace-714136/