管理表空间 -----摘自《Oracle10g 宝典》

 

■查看排序段的使用情况

  SQL> select * from v$sort_segment;

  

■查看使用排序段的会话和用户信息

  SQL> select * from v$sort_usage;

■查询数据字典视图得到撤销表空间中回退段的信息

  SQL> select * from dba_rollback_segs;

■创建大文件表空间

  SQL> create bigfile tablespace bigfilespace01 

      datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/bigfilespace01.dbf' size 25G;                                     

■创建小文件表空间

  SQL> create smallfile tablespace smallfilespace01

      datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/smallfilespace01.dbf' size 25G;

■创建不同类型的表空间的SQL语句

  - CREATE TABLESPACE  创建(永久表空空)

  - CREATE BIGFILE TABLESPACE 创建大文件表空间

  - CREATE TEMPORARY TABLESPACE 创建临时表空间

  - CREATE UNDO TABLESPACE      创建撤销表空间

■使用UNIFORM区分配方式的表空间

  SQL> create tablespace myts01

  2  datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_1.dbf' size 2m 

  3  uniform size 128k;                                                                             

  优点:在表空间中不会产生磁盘碎片,可以节约磁盘空间

■使用AUTOALLOCATE区分配方式的表空间

  SQL> create tablespace myts02

  2  datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts02_1.dbf' size 2m           

  3  autoallocate; 

  缺点:在表空间中会产生磁盘碎片,进而造成一些磁盘空间的浪费

■使用AUTO段管理方式的表空间

  SQL> create tablespace myts03

  2  datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts03_1.dbf' size 2m       

  3  uniform size 128K 

  4  segment space management AUTO;

■创建大文件表空间

  SQL> create bigfile tablespace mybigts05

  2  datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts05_1.dbf' size 2m;

  该表空间管理方式为本地管理(只有本地管理才支持大文件表空间,最高可达8EB),并且区大小由系统自动分配

■创建临时表空间

  SQL> create smallfile temporary tablespace mytmpts01 

  2  tempfile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/mytempts01_1.dbf' size 4m

  3  extent management local

  4  uniform size 1k;

■建立大文件临时表空间

  SQL> create bigfile temporary tablespace mybigtempts01

  2  tempfile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/mybigtempts01_1.dbf' size 4m

  3  extent management local

  4  uniform size 1k;

■创建撤销表空间

  SQL> CREATE SMALLFILE UNDO TABLESPACE myundots01             

  2  datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myundots01_1.dbf' size 4m           

  3  /

  创建UNDO表空间时只能指定DATAFILE和EXTENT MANAGEMENT LOCAL选项,而不能指定其他选项,否则会有错误提示.

  UNDO表空间采用本地管理方式,区的大小自动分配.

■向表空间中添加数据文件

  SQL> alter tablespace myts01

  2  add datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_2.dbf' size 3m;                        

■向临时表空间添加数据文件

  SQL> alter tablespace mytmpts01 

  2  add tempfile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/mytmpts01_2.dbf' size 4m;

■改变数据文件

  SQL> alter database

  2  datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_2.dbf' 

  3  resize 4m;                          

■充许数据文件自动扩展

  SQL> alter database 

  2  datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_2.dbf'  

  3  autoextend on next 1m maxsize 20m;            

■使表空间脱机

  SQL> alter tablespace myts01 offline;

■使表空间联机

  SQL> alter tablespace myts01 offline;

■使表空间只读 

  SQL> alter tablespace myts01 read only;

  处于只读状态时,仍然充许执行DROP操作删除该表空间上的对像,例如:

  SQL> drop table personnel;                                                                                         

  表已删除。

■使表空间可读写

  SQL> alter tablespace myts01 read write;

■修改表空间的名称

  SQL> alter tablespace myts03 rename to myts03_new;

  提示:修改表空间名称是Oracle 10g特有的,在此之前不能实现该功能.

■设置数据库默认表空间

  SQL> alter database default tablespace myts01;

■设置数据库默认临时表空间 

  SQL> alter database default temporary tablespace mytmpts01;

■删除myts03表空间,包括该表空间中的内容

  SQL> drop tablespace myts03 including contents;

  这条语句只会从数据字典中删除表空间信息,而其相关的数据文件仍在于磁盘上

■删除myts03表空间,包括该表空间中的内容及数据

  SQL> drop tablespace myts04 including contents and datafiles;

  删除表空间的同时删除与它相关的数据文件

■获得数据库中各个表空间的名称、区管理方式、区分配方式、段管理方式、类型等信息

SQL> select tablespace_name,extent_management,allocation_type,segment_space_management,contents from dba_tablespaces;              

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO SEGMEN CONTENTS

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

SYSTEM                         LOCAL      SYSTEM    MANUAL PERMANENT

UNDOTBS1                       LOCAL      SYSTEM    MANUAL UNDO

SYSAUX                         LOCAL      SYSTEM    AUTO   PERMANENT

TEMP                           LOCAL      UNIFORM   MANUAL TEMPORARY

USERS                          LOCAL      SYSTEM    AUTO   PERMANENT

BIGFILESPACE01                 LOCAL      SYSTEM    AUTO   PERMANENT

SMALLFILESPACE01               LOCAL      SYSTEM    MANUAL PERMANENT

MYTS01                         LOCAL      UNIFORM   MANUAL PERMANENT

MYTS02                         LOCAL      SYSTEM    MANUAL PERMANENT

MYBIGTS05                      LOCAL      SYSTEM    AUTO   PERMANENT

MYBIGTEMPTS01                  LOCAL      UNIFORM   MANUAL TEMPORARY

MYUNDOTS01                     LOCAL      SYSTEM    MANUAL UNDO

MYTMPTS01                      LOCAL      UNIFORM   MANUAL TEMPORARY 

■查询数据库中所有表空间的名称和默认的存储参数

SQL> select tablespace_name,initial_extent,next_extent,min_extents,max_extents,pct_increase from dba_tablespaces;             

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE

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

SYSTEM                                  65536                       1  2147483645

UNDOTBS1                                65536                       1  2147483645

SYSAUX                                  65536                       1  2147483645

TEMP                                  1048576     1048576           1                        0

USERS                                   65536                       1  2147483645

BIGFILESPACE01                          65536                       1  2147483645

SMALLFILESPACE01                        65536                       1  2147483645

MYTS01                                 131072      131072           1  2147483645            0

MYTS02                                  65536                       1  2147483645

MYBIGTS05                               65536                       1  2147483645

MYBIGTEMPTS01                           16384       16384           1                        0

MYUNDOTS01                              65536                       1  2147483645

MYTMPTS01                               16384       16384           1                        0

■查询数据库中数据文件的位置、名称、大小及其所属的表空间

SQL> col file_name for a75 

SQL> select file_name,blocks,tablespace_name from dba_data_files;                                                                   

FILE_NAME                                                                       BLOCKS TABLESPACE_NAME

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

/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/users01.dbf                     640 USERS

/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/sysaux01.dbf                  35840 SYSAUX

/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/undotbs01.dbf                  3200 UNDOTBS1

/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/system01.dbf                  57600 SYSTEM

/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/bigfilespace01.dbf             3200 BIGFILESPACE01

/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/smallfilespace01.dbf           3200 SMALLFILESPACE01

/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_1.dbf                    256 MYTS01

/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts02_1.dbf                    256 MYTS02

/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts05_1.dbf                    256 MYBIGTS05

/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myundots01_1.dbf                512 MYUNDOTS01

/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_2.dbf                    512 MYTS01

■生成数据库中每个表空间的空闲盘区的信息

SQL> select tablespace_name,file_id,max(blocks) maximum, 

     count(*) pieces,

     min(blocks) minimum,  

     avg(blocks) average,  

     sum(blocks) total           

     from dba_free_space              

     group by tablespace_name,file_id;             

TABLESPACE_NAME                   FILE_ID    MAXIMUM     PIECES    MINIMUM    AVERAGE      TOTAL

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

USERS                                   4        584          1        584        584        584

MYTS01                                  7        208          2         16        112        224

MYTS01                                 12        496          1        496        496        496

MYTS02                                  8        248          1        248        248        248

SYSAUX                                  3        248         19          8 45.0526316        856

SYSTEM                                  1       1272          1       1272       1272       1272

UNDOTBS1                                2       1656         11          8 204.363636       2248

MYBIGTS05                              10        240          1        240        240        240

MYUNDOTS01                             11        344          1        344        344        344

BIGFILESPACE01                          5       3184          1       3184       3184       3184

SMALLFILESPACE01                        6       3192          1       3192       3192       3192

pices列显示该表空间文件中的空闲空间盘区数；

maximun和minimum列显示数据库块中最大和最小的空间区域；

average列显示一个空闲空间盘区以块计的平均大小；

total列显示每个表空间文件中空闲空间盘区以块计的数量

■查询表空间的空闲空间大小

SQL> select tablespace_name,sum(bytes) free_space from dba_free_space         

  2  group by tablespace_name;                 

TABLESPACE_NAME                FREE_SPACE

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

BIGFILESPACE01                   26083328

MYBIGTS05                         1966080

MYTS01                            5898240

MYTS02                            2031616

MYUNDOTS01                        2818048

SMALLFILESPACE01                 26148864

SYSAUX                            7012352

SYSTEM                           10420224

UNDOTBS1                         18415616

USERS                             4784128

■创建临时表空间组

  SQL> alter tablespace mybigtempts01 tablespace group mytmptsgp01; 

  SQL> alter tablespace mytmpts01 tablespace group mytmptsgp01;

  必须先创建临时表空间,然后才能创建临时表空间组.

  SQL> ALTER TABLESPACE 临时表空间名 TABLESPACE GROUP 临时表空间组名

■查询表空间信息

  V$TABLESPACE  来自控制文件的所有表空间名称和编号

  DBA_TABLESPACE 所有(或用户可访问的)表空间的信息

  USER_TABLESPACE

  DBA_SEGMENTS   所有(或用户可访问的)表空间中的段的信息

  USER_SEGMENTS

  DBA_EXTENTS  所有(或用户可访问的)表空间中的数据盘区的信息

  USER_EXTENTS

  DBA_FREE_SPACE 所有(或用户可访问的)表空间中的空闲盘区的信息

  USER_FREE_SPACE

  V$DATAFILE  所有数据文件的信息,包括所属表空间的名称和编号

  V$TEMPFILE  所有临时文件的信息,包括所属表空间的名称和编号

  DBA_DATA_FILES 数据文件及其所属的表空间的信息

  DBA_TEMP_FILES 临时文件及其所属的临时表空间的信息

  V$TEMP_EXTENT_MAX 本地管理的临时表空间中的所有盘区的信息

  V$TEMP_EXTENT_POOL 本地管理的临时表空间的缓存信息,使用的临时表空间的状态信息

  V$TEMP_SPACE_HEADER 每个临时文件的已用/空闲空间信息

  DBA_USERS  所有用户默认的和临时的表空间的信息

  DBA_TS_QUOTAS  所有用户的表空间配额信息

  V$SORT_SEGMENT 例程的每个排序段的信息.该视图只在表空间为TEMPORARY类型时更新

  V$SORT_USER  用户使用的临时排序段的信息