oracle10g_create tablespace_测试_1

SQL> create user test identified by system account unlock;

User created.

SQL> grant resource,connect to test;

Grant succeeded.

SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m;

Tablespace created.

SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)


--显式创建的表空间不是自动扩展的
SQL> select file_name,tablespace_name,bytes/1024/1024 mb,autoextensible from dba_data_files where tablespace_name='TEST';

FILE_NAME
--------------------------------------------------
TABLESPACE_NAME                        MB AUT
------------------------------ ---------- ---
/oracle/db/test01.dbf
TEST                                   10 NO


FILE_NAME                      TABLESPACE         MB AUT
------------------------------ ---------- ---------- ---
/oracle/db/test01.dbf          TEST               10 NO

SQL> select dbms_metadata.get_ddl('TABLESPACE','TEST') from dual;---查看表空间的定义语句


SQL> set long 99999
SQL> /

DBMS_METADATA.GET_DDL('TABLESPACE','TEST')
--------------------------------------------------------------------------------

  CREATE TABLESPACE "TEST" DATAFILE
  '/oracle/db/test01.dbf' SIZE 10485760
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO



SQL> conn test/system      --如果一般用户要创建/删除表空间,要给权限create|drop tablespace         
Connected.
SQL> create tablespace own datafile '/oracle/db/own01.dbf' size 10m;
create tablespace own datafile '/oracle/db/own01.dbf' size 10m
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn /as sysdba
Connected.
SQL> grant create tablespace to test;

Grant succeeded.

SQL> conn test/system
Connected.
SQL> create tablespace own datafile '/oracle/db/own01.dbf' size 10m;

Tablespace created.

SQL> drop tablespace own including contents and datafiles;
drop tablespace own including contents and datafiles
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn /as sysdba
Connected.
SQL> grant drop tablespace to test;

Grant succeeded.

SQL> conn test/system
Connected.
SQL> drop tablespace own including contents and datafiles;--删除表空间及其数据文件

Tablespace dropped.

SQL> conn /as sysdba
Connected.
SQL> desc dba_tablespaces;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 MIN_EXTLEN                                         NUMBER
 STATUS                                             VARCHAR2(9)
 CONTENTS                                           VARCHAR2(9)
 LOGGING                                            VARCHAR2(9)
 FORCE_LOGGING                                      VARCHAR2(3)
 EXTENT_MANAGEMENT                                  VARCHAR2(10)
 ALLOCATION_TYPE                                    VARCHAR2(9)
 PLUGGED_IN                                         VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
 DEF_TAB_COMPRESSION                                VARCHAR2(8)
 RETENTION                                          VARCHAR2(11)
 BIGFILE                                            VARCHAR2(3)


--查看表空间相关属性:块大小,是否大文件(大文件表空间就是一个表空间只能有一个数据文件,此文件大小可达
 -- 数据文件或者文件为128 terabytes (TB) 对于块大小为 32K blocks ,对于块大小为8k数据文件或临时文件大小为32TB
 --我们一般的业务不可能用哪么大,当然默认是smallfile,它最大文件数为1022
 

SQL> select tablespace_name,block_size,logging,allocation_type,bigfile from dba_tablespaces where tablespace_name='TEST';

TABLESPACE BLOCK_SIZE LOGGING   ALLOCATIO BIG
---------- ---------- --------- --------- ---
TEST             8192 LOGGING   SYSTEM    NO

SQL> create bigfile tablespace bigtbs datafile '/oracle/db/bigtbs01.dbf' size 10m;

Tablespace created.

SQL> select tablespace_name,block_size,logging,allocation_type,bigfile from dba_tablespaces where tablespace_name='BIGTBS';

TABLESPACE BLOCK_SIZE LOGGING   ALLOCATIO BIG
---------- ---------- --------- --------- ---
BIGTBS           8192 LOGGING   SYSTEM    YES

SQL> drop tablespace bigtbs including contents and datafiles;

Tablespace dropped.

SQL> create smallfile tablespace smalltbs datafile '/oracle/db/smalltbs01.dbf' size 10m;

Tablespace created.

SQL> select tablespace_name,block_size,logging,allocation_type,bigfile from dba_tablespaces where tablespace_name='SMALLTBS';

TABLESPACE BLOCK_SIZE LOGGING   ALLOCATIO BIG
---------- ---------- --------- --------- ---
SMALLTBS         8192 LOGGING   SYSTEM    NO

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TEST';---查看表空间是否在线,下线(就是此表空间可否读写)

TABLESPACE STATUS
---------- ---------
TEST       ONLINE

SQL> alter tablespace test offline;--此表空间下线

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TEST';

TABLESPACE STATUS
---------- ---------
TEST       OFFLINE

SQL> alter tablespace test online;                                                  

Tablespace altered.

SQL> select tablespace_name,min_extents,max_extents from dba_tablespaces where tablespace_name='TEST';

TABLESPACE MIN_EXTENTS MAX_EXTENTS
---------- ----------- -----------
TEST                 1  2147483645

SQL> show user  
USER is "SYS"
SQL> create tablespace minextent datafile '/oracle/db/minextent01.dbf' size 10m  minimum extent 10 nologging offline
  2  default compress;

Tablespace created.

SQL> select tablespace_name,min_extents,max_extents from dba_tablespaces where tablespace_name='MINEXTENT';

TABLESPACE MIN_EXTENTS MAX_EXTENTS
---------- ----------- -----------
MINEXTENT            1  2147483645


---表空间所属表是否压缩
SQL> select tablespace_name,logging,status,min_extlen,def_tab_compression from dba_tablespaces where tablespace_name='MINEXTENT';

TABLESPACE LOGGING   STATUS    MIN_EXTLEN DEF_TAB_
---------- --------- --------- ---------- --------
MINEXTENT  NOLOGGING OFFLINE        65536 ENABLED



SQL> alter tablespace minextent default nocompress;

Tablespace altered.

SQL>  select tablespace_name,logging,status,min_extlen,def_tab_compression from dba_tablespaces where tablespace_name='MINEXTENT';

TABLESPACE LOGGING   STATUS    MIN_EXTLEN DEF_TAB_
---------- --------- --------- ---------- --------
MINEXTENT  NOLOGGING OFFLINE        65536 DISABLED

SQL> alter tablespace minextent online;

Tablespace altered.

SQL> select tablespace_name,extent_management,segment_space_management from dba_tablespaces where tablespace_name='MINEXTENT';

TABLESPACE EXTENT_MAN SEGMEN
---------- ---------- ------
MINEXTENT  LOCAL      AUTO

SQL> alter tablespace minextent extent management dictionary;--10g不能用dictionary管理方式了,太多的字典表竞争
alter tablespace minextent extent management dictionary
                           *
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option


SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.



SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary segment space management manual;
create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary segment space management manual
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace


SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary;
create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace


SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management local uniform. size 1m;

Tablespace created.



SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m flashback off;--开启此表空间是否可以闪回,10g新特性

Tablespace created.

SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL> desc v$tablespace;---闪回列,对应上面
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TS#                                                NUMBER
 NAME                                               VARCHAR2(30)
 INCLUDED_IN_DATABASE_BACKUP                        VARCHAR2(3)
 BIGFILE                                            VARCHAR2(3)
 FLASHBACK_ON                                       VARCHAR2(3)
 ENCRYPT_IN_BACKUP                                  VARCHAR2(3)

SQL> select name,included_in_database_backup,bigfile,flashback_on from v$tablespace where name='TEST';

NAME                           INC BIG FLA
------------------------------ --- --- ---
TEST                           YES NO  NO

SQL> alter tablespace test flashback on;
alter tablespace test flashback on
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1279964 bytes
Variable Size              79693860 bytes
Database Buffers          230686720 bytes
Redo Buffers                2912256 bytes
Database mounted.
SQL> alter tablespace test flashback on;--变更表空间为闪回,在database mount状态下

Tablespace altered.

SQL> alter database open;

Database altered.

SQL> select name,included_in_database_backup,bigfile,flashback_on from v$tablespace where name='TEST';

NAME                           INC BIG FLA
------------------------------ --- --- ---
TEST                           YES NO  YES

 

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

转载于:http://blog.itpub.net/9240380/viewspace-614492/