management tablespaces

首先说下多表空间的好处:

1 从数据字典中分离用户数据,减少IO争用

2 从应用中分离数据,避免多个应用受单一表空间的影响。

3 数据文件分布在不同磁盘的表空间中,减少IO争用

4 提供更好的可用性,某个表空间的OFFLINE不影响别的表空间的应用

5 Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage.

6 备份单独表空间

表空间有如下集中类型:

SYSTEM,系统表空间时每个数据必须有的

普通的表空间。。比如说 USERS

特殊表空间 比如说UNDO TABLESPACE ,TEMPORARY TABLESPACE,

下面首先说下本地表空间管理:

本地表空间的分区信息是用位图管理的,它比字典管理有如下优点:

1  调整空间比较快,分区位图信息存储在文件首部

2 增强性能

3Readable standby databases are allowed, because locally managed temporary tablespaces do not generate any undo or redo.

4,空间分配简单容易

5减少对数据字典的依赖,

如何创建表空间:

CREATE TABLESPACE lmtbsb DATAFILE ‘/u02/oracle/data/lmtbsb01.dbf’ SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE (每次增大64K)默认

CREATE TABLESPACE lmtbsb DATAFILE ‘/u02/oracle/data/lmtbsb01.dbf’ SIZE 50M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K(指定扩展分区大小);

在本地表空间中,有两种段管理 方式,手动和自动。

手动就是 要调整freelists;

自动用位图

使用段管理的创建表空间的语句如下:

CREATE TABLESPACE lmtbsb DATAFILE ‘/u02/oracle/data/lmtbsb01.dbf’ SIZE 50M
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;

  • If you set extent management to LOCAL UNIFORM, then you must ensure that each extent contains at least 5 database blocks.
  • If you set extent management to LOCAL AUTOALLOCATE, and if the database block size is 16K or greater, then Oracle manages segment space by creating extents with a minimum size of 5 blocks rounded up to 64K.

 对表空间的一些常见操作:

往表空间中添加数据文件:

ALTER TABLESPACE lmtbsb
   ADD DATAFILE ‘/u02/oracle/data/lmtbsb02.dbf’ SIZE 1M;

修改表空间状态ONLINE/OFFLINE

修改表空的读写状态

重命名一个数据文件

下面介绍下大表空间bigfile tablespace(好过4G)

优点:

1 存储数据大,假如8K BLOCKS,数据文件最大可以到32TB ,BLOCKS 32的 可以达到128TB

2 大表空间可以减少数据库中的数据文件个数,

3简化管理

以下是特殊的

Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace.

Notes:

  • Bigfile tablespaces are intended to be used with Automatic Storage Management (ASM) or other logical volume managers that supports striping or RAID, and dynamically extensible logical volumes.
  • Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel query execution and RMAN backup parallelization.
  • Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity. Refer to your operating system specific documentation for information about maximum supported file sizes.

创建大表空间的语句:

CREATE BIGFILE TABLESPACE bigtbs
    DATAFILE ‘/u02/oracle/data/bigtbs01.dbf’ SIZE 50G
不用特别指出。本地管理和自动段管理,如果你指定别的会报语法错误。

调整表空间大小:

ALTER TABLESPACE bigtbs RESIZE 80G;

ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;

关于表空间的一些视图:

  • DBA_TABLESPACES

SQL> desc dba_tablespaces;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -----------------------

 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
 MAX_SIZE                                           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)
 PREDICATE_EVALUATION                               VARCHAR2(7)
 ENCRYPTED                                          VARCHAR2(3)
 COMPRESS_FOR                                       VARCHAR2(18)

  • USER_TABLESPACES
  • V$TABLESPACE

SQL> desc v$tablespace;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------

 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 * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         2 UNDOTBS1                       YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 EXAMPLE                        YES NO  YES
         7 P1                             YES NO  YES
         8 P2                             YES NO  YES
         9 P3                             YES NO  YES
        10 P4                             YES NO  YES
        11 DEMO                           YES NO  YES

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
        12 BOSON                          YES NO  YES
        13 DEMOO                          YES NO  YES
        15 TEMP2                          NO  NO  YES
        16 TEMP3                          NO  NO  YES

已选择15行。

SQL> select tablespace_name,block_size,initial_extent,extent_management from dba
_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT EXTENT_MAN
—————————— ———- ————– ———-
SYSTEM                               8192          65536 LOCAL
SYSAUX                               8192          65536 LOCAL
UNDOTBS1                             8192          65536 LOCAL
TEMP                                 8192        1048576 LOCAL
USERS                                8192          65536 LOCAL
EXAMPLE                              8192          65536 LOCAL
P1                                   8192          65536 LOCAL
P2                                   8192          65536 LOCAL
P3                                   8192          65536 LOCAL
P4                                   8192          65536 LOCAL
DEMO                                 8192          65536 LOCAL

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT EXTENT_MAN
—————————— ———- ————– ———-
BOSON                                8192          65536 LOCAL
DEMOO                                8192          65536 LOCAL
TEMP2                                8192        1048576 LOCAL
TEMP3                                8192        1048576 LOCAL

临时表空间

CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf'       SIZE 20M REUSE     
 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

不能使用autoallocate

You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace.

 
特殊的几个操作
ALTER TABLESPACE lmtemp    ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;
  ALTER TABLESPACE lmtemp TEMPFILE OFFLINE; ALTER TABLESPACE lmtemp TEMPFILE ONLINE;
调整临时表空间大小:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
删除临时表空间大小
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP     INCLUDING DATAFILES;

关于临时表空间组看以前的文章。

下面介绍一下创建非缺省BLOCKSIZE的表空间

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K     BLOCKSIZE 8K;

关于表空间的ONLINE OFFLINE

Taking Tablespaces Offline

You may want to take a tablespace offline for any of the following reasons:

  • To make a portion of the database unavailable while allowing normal access to the remainder of the database
  • To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)
  • To make an application and its group of tables temporarily unavailable while updating or maintaining the application
  • To rename or relocate tablespace datafilesSee “Renaming and Relocating Datafiles” for details.

When a tablespace is taken offline, the database takes all the associated files offline.

You cannot take the following tablespaces offline:

  • SYSTEM
  • The undo tablespace
  • Temporary tablespaces

Renaming Tablespaces

Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace. For example, the following statement renames the users tablespace:

ALTER TABLESPACE users RENAME TO usersts;
 
The following affect the operation of this statement:
  • The COMPATIBLE parameter must be set to 10.0 or higher.
  • If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX tablespace,
  • then it will not be renamed and an error is raised.
  • If any datafile in the tablespace is offline, or if the tablespace is offline,
  • then the tablespace is not renamed and an error is raised.
  • If the tablespace is read only, then datafile headers are not updated.
  • This should not be regarded as corruption; instead, it causes a message to be written to the alert log indicating
  • that datafile headers have not been renamed. The data dictionary and control file are updated.
  • If the tablespace is the default temporary tablespace,
  • then the corresponding entry in the database properties table is updated and the
  • DATABASE_PROPERTIES view shows the new name.
  • If the tablespace is an undo tablespace and if the following conditions are met,
  • then the tablespace name is changed to the new tablespace name in the server parameter file (SPFILE).
    • The server parameter file was used to start up the database.
    • The tablespace name is specified as the UNDO_TABLESPACE for any instance.
    If a traditional initialization parameter file (PFILE) is being used then a message is written to the
alert log stating that the initialization parameter file must be manually changed.
 删除临时表空间
DROP TABLESPACE users INCLUDING CONTENTS;
You cannot drop a tablespace that contains any active segments. For example,  

if a table in the tablespace is currently being used or the tablespace contains undo data needed to
 roll back uncommitted transactions, you cannot drop the tablespace.  

The tablespace can be online or offline, but it is best to take the tablespace offline before dropping it.
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES
 11.jpg
 
 

Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap)

The TABLESPACE_VERIFY procedure discovers that a segment has allocated blocks that are marked free in the bitmap,

but no overlap between segments is reported.

In this scenario, perform the following tasks:

  1. Call the SEGMENT_DUMP procedure to dump the ranges that the administrator allocated to the segment.
  2. For each range, call the TABLESPACE_FIX_BITMAPS procedure with the TABLESPACE_EXTENT_MAKE_USED option to mark the space as used.
  3. Call TABLESPACE_REBUILD_QUOTAS to rebuild quotas.


Scenario 2: Dropping a Corrupted Segment

You cannot drop a segment because the bitmap has segment blocks marked "free". The system has automatically marked the segment corrupted. In this scenario, perform the following tasks:
  1. Call the SEGMENT_VERIFY procedure with the SEGMENT_VERIFY_EXTENTS_GLOBAL option. If no overlaps are reported, then proceed with steps 2 through 5.
  2. Call the SEGMENT_DUMP procedure to dump the DBA ranges allocated to the segment.
  3. For each range, call TABLESPACE_FIX_BITMAPS with the TABLESPACE_EXTENT_MAKE_FREE option to mark the space as free.
  4. Call SEGMENT_DROP_CORRUPT to drop the SEG$ entry.
  5. Call TABLESPACE_REBUILD_QUOTAS to rebuild quotas.


Scenario 3: Fixing Bitmap Where Overlap is Reported

The TABLESPACE_VERIFY procedure reports some overlapping. Some of the real data must be sacrificed based on previous internal errors. After choosing the object to be sacrificed, in this case say, table t1, perform the following tasks:
  1. Make a list of all objects that t1 overlaps.
  2. Drop table t1. If necessary, follow up by calling the SEGMENT_DROP_CORRUPT procedure.
  3. Call the SEGMENT_VERIFY procedure on all objects that t1 overlapped. If necessary, call the TABLESPACE_FIX_BITMAPS procedure
  4. to mark appropriate bitmap blocks as used.
  5. Rerun the TABLESPACE_VERIFY procedure to verify that the problem is resolved.


Scenario 4: Correcting Media Corruption of Bitmap Blocks

A set of bitmap blocks has media corruption. In this scenario, perform the following tasks:
  1. Call the TABLESPACE_REBUILD_BITMAPS procedure, either on all bitmap blocks, or on a single block if only one is corrupt.
  2. Call the TABLESPACE_REBUILD_QUOTAS procedure to rebuild quotas.
  3. Call the TABLESPACE_VERIFY procedure to verify that the bitmaps are consistent.

Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace

Use the TABLESPACE_MIGRATE_TO_LOCAL procedure to migrate a dictionary-managed tablespace to a locally managed tablespace.
 This operation is done online, but space management operations are blocked until the migration has been completed.
This means that you can read or modify data while the migration is in progress, but if you are loading a large amount of
 data that requires the allocation of additional extents, then the operation may be blocked.
Assume that the database block size is 2K and the existing extent sizes in tablespace tbs_1 are 10, 50,
and 10,000 blocks (used, used, and free). The MINIMUM EXTENT value is 20K (10 blocks).
Allow the system to choose the bitmap allocation unit. The value of 10 blocks is chosen,
because it is the highest common denominator and does not exceed MINIMUM EXTENT.
The statement to convert tbs_1 to a locally managed tablespace is as follows:
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('tbs_1');
 
下面介绍下关于表空间传输:
 
 
 
  • Exporting and importing partitions in data warehousing tables
  • Publishing structured data on CDs
  • Copying multiple read-only versions of a tablespace on multiple databases
  • Archiving historical data
  • Performing tablespace point-in-time-recovery (TSPITR)
 
 
下面时一些通过验证的平台::
 
 
SQL> COLUMN PLATFORM_NAME FORMAT A32
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT
----------- -------------------------------- --------------
          1 Solaris[tm] OE (32-bit)          Big
          2 Solaris[tm] OE (64-bit)          Big
          7 Microsoft Windows IA (32-bit)    Little
         10 Linux IA (32-bit)                Little
          6 AIX-Based Systems (64-bit)       Big
          3 HP-UX (64-bit)                   Big
          5 HP Tru64 UNIX                    Little
          4 HP-UX IA (64-bit)                Big
         11 Linux IA (64-bit)                Little
         15 HP Open VMS                      Little
          8 Microsoft Windows IA (64-bit)    Little
PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT
----------- -------------------------------- --------------
          9 IBM zSeries Based Linux          Big
         13 Linux 64-bit for AMD             Little
         16 Apple Mac OS                     Big
         12 Microsoft Windows 64-bit for AMD Little
         17 Solaris Operating System (x86)   Little
         18 IBM Power Based Linux            Big
         19 HP IA Open VMS                   Little
         20 Solaris Operating System (AMD64) Little
已选择19行。
 
 
如果源库和目标哭的系统平台:ENDIAN_FORMAT不一样 ,则传输之前需要转换。
 
 
还有如下的要求 :

Limitations on Transportable Tablespace Use

Be aware of the following limitations as you plan to transport tablespaces:
  • The source and target database must use the same character set and national character set.
  • You cannot transport a tablespace to a target database in which a tablespace with the same name already exists.
  • However, you can rename either the tablespace to be transported or the destination tablespace
  • before the transport operation.
  • Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables)
  • are not transportable unless all of the underlying or contained objects are in the tablespace set.
  • Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes,
  • but you must use the IMP and EXP utilities, not Data Pump. When using EXP,
  • ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).
 
 
 
 Transport Scenario Minimum Compatibility Setting
Source Database Target Database
Databases on the same platform
 8.0
 8.0
 
Tablespace with different database block size than the target database
 9.0
 9.0
 
Databases on different platforms
 10.0
 10.0
 
 
以下做个简单事例:
 
 一、首先要查看数据库的系统平台的endian是否一样,如果不一样需要转换,一样就没什么了。
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
  2       FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
  3       WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME                    ENDIAN_FORMAT
-------------------------------- --------------
Microsoft Windows IA (32-bit)    Little
 不一样。转换步骤:
 
If sales_1 and sales_2 are being transported to a different platform, and the endianness of the platforms is different, and if you want to convert before transporting the tablespace set,
 then convert the datafiles composing the sales_1 and sales_2 tablespaces:
  1. From SQL*Plus, return to the host system:
    SQL> HOST
  2. The RMAN CONVERT command is used to do the conversion. Start RMAN and connect to the target database:
    $ RMAN TARGET /  Recovery Manager: Release 10.1.0.0.0   Copyright (c) 1995, 2003, Oracle Corporation.  All rights reserved.  connected to target database: salesdb (DBID=3295731590)
  3. Convert the datafiles into a temporary location on the source platform. In this example, assume that the temporary location, directory /temp, has already been created. The converted datafiles are assigned names by the system.
    RMAN> CONVERT TABLESPACE sales_1,sales_2
  4.  2> TO PLATFORM 'Microsoft Windows NT'
  5.  3> FORMAT '/temp/%U';
  6.   Starting backup at 08-APR-03 using target database control file instead of recovery catalog allocated channel:
  7. ORA_DISK_1 channel ORA_DISK_1: sid=11 devtype=DISK channel ORA_DISK_1: starting datafile conversion input datafile fno=00005
  8. name=/u01/oracle/oradata/salesdb/sales_101.dbf converted datafile=/temp/data_D-10_I-3295731590_TS-ADMIN_TBS_FNO-5_05ek24v5 channel ORA_DISK_1:
  9. datafile conversion complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile conversion input datafile fno=00004 name=/u01/oracle/oradata/salesdb/sales_101.dbf
  10. converted datafile=/temp/data_D-10_I-3295731590_TS-EXAMPLE_FNO-4_06ek24vl channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45 Finished backup at 08-APR-03

     

    See Also:

    Oracle Database Backup and Recovery Reference for a description of the RMAN CONVERT command

  11. Exit Recovery Manager:
    RMAN> exit
 
 二、选择要传输的表空间
 
  • An index inside the set of tablespaces is for a table outside of the set of tablespaces.

     

    Note: It is not a violation if a corresponding index for a table is outside of the set of tablespaces.

  • A partitioned table is partially contained in the set of tablespaces. The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables.
  • A referential integrity constraint points to a table across a set boundary. When transporting a set of tablespaces, you can choose to include referential integrity constraints. However, doing so can affect whether or not a set of tablespaces is self-contained. If you decide not to transport constraints, then the constraints are not considered as pointers.
  • A table inside the set of tablespaces contains a LOB column that points to LOBs outside the set of tablespaces.
  • An XML DB schema (*.xsd) that was registered by user A imports a global schema that was registered by user B, and the following is true: the default tablespace for user A is tablespace A, the default tablespace for user B is tablespace B, and only tablespace A is included in the set of tablespaces.

三、首先用EXP或EXDUMP导出数据结构信息

  1. Make all tablespaces in the set you are copying read-only.
    SQL> ALTER TABLESPACE sales_1 READ ONLY;  Tablespace altered.  SQL> ALTER TABLESPACE sales_2 READ ONLY;  Tablespace altered.
    1. Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.
    2. Note:

    3. If any of the tablespaces have XMLTypes, you must use EXP instead of Data Pump. Ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).

    4. SQL> HOST  $ EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir          TRANSPORT_TABLESPACES = sales_1,sales_2
    5. You must always specify TRANSPORT_TABLESPACES, which determines the mode of the export operation. In this example:
    6. The DUMPFILE parameter specifies the name of the structural information export file to be created, expdat.dmp.
    • The DIRECTORY parameter specifies the default directory object that points to the operating system or Automatic Storage Management location of the dump file. You must create the DIRECTORY object before invoking Data Pump, and you must grant the READ and WRITE object privileges on the directory to PUBLIC. See Oracle Database SQL Reference for information on the CREATE DIRECTORY command.
    • Triggers and indexes are included in the export operation by default.
    1. If you want to perform a transport tablespace operation with a strict containment check, use the TRANSPORT_FULL_CHECK parameter, as shown in the following example:
    2. EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir        TRANSPORT_TABLESPACES=sales_1,sales_2 TRANSPORT_FULL_CHECK=Y
    3. In this example, the Data Pump export utility verifies that there are no dependencies between the objects inside the transportable set and objects outside the transportable set. If the tablespace set being transported is not self-contained, then the export fails and indicates that the transportable set is not self-contained. You must then return to Step 1 to resolve all violations.
    4. Notes:

    5. The Data Pump utility is used to export only data dictionary structural information (metadata) for the tablespaces. No actual data is unloaded, so this operation goes relatively quickly even for large tablespace sets.

    四、把数据文件和导出的文件拷贝到目标系统上

    五、导入数据

    Import the tablespace metadata using the Data Pump Import utility, impdp:

    Note:

    If any of the tablespaces contain XMLTypes, you must use IMP instead of Data Pump.

    IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir    TRANSPORT_DATAFILES=    /salesdb/sales_101.dbf,    /salesdb/sales_201.dbf    REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)

    其实关键就是导入前的 准备工作,比如源库系统endian,源库的字符集,国际字符集,还有特殊数据处理XMLDB啥的。

    和表空间有关的视图:

    12.jpg

    Example 1: Listing Tablespaces and Default Storage Parameters

    To list the names and default storage parameters of all tablespaces in a database, use the following query on the DBA_TABLESPACES view:

    SELECT TABLESPACE_NAME "TABLESPACE",    INITIAL_EXTENT "INITIAL_EXT",    NEXT_EXTENT "NEXT_EXT",    MIN_EXTENTS "MIN_EXT",    MAX_EXTENTS "MAX_EXT",    PCT_INCREASE    FROM DBA_TABLESPACES;  TABLESPACE  INITIAL_EXT  NEXT_EXT  MIN_EXT   MAX_EXT    PCT_INCREASE   ----------  -----------  --------  -------   -------    ------------  RBS             1048576   1048576        2        40               0 SYSTEM           106496    106496        1        99               1 TEMP             106496    106496        1        99               0 TESTTBS           57344     16384        2        10               1 USERS             57344     57344        1        99               1

    Example 2: Listing the Datafiles and Associated Tablespaces of a Database

    To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES view:

    SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME    FROM DBA_DATA_FILES;  FILE_NAME                                      BLOCKS  TABLESPACE_NAME ------------                               ----------  ------------------- /U02/ORACLE/IDDB3/DBF/RBS01.DBF                  1536  RBS /U02/ORACLE/IDDB3/DBF/SYSTEM01.DBF               6586  SYSTEM /U02/ORACLE/IDDB3/DBF/TEMP01.DBF                 6400  TEMP /U02/ORACLE/IDDB3/DBF/TESTTBS01.DBF              6400  TESTTBS /U02/ORACLE/IDDB3/DBF/USERS01.DBF                 384  USERS

    Example 3: Displaying Statistics for Free Space (Extents) of Each Tablespace

    To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:

    SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,    COUNT(*)    "PIECES",    MAX(blocks) "MAXIMUM",    MIN(blocks) "MINIMUM",    AVG(blocks) "AVERAGE",    SUM(blocks) "TOTAL"    FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME, FILE_ID;  TABLESPACE    FILE_ID  PIECES   MAXIMUM    MINIMUM  AVERAGE    TOTAL ----------    -------  ------   -------    -------  -------   ------ RBS                 2       1       955        955      955      955 SYSTEM              1       1       119        119      119      119 TEMP                4       1      6399       6399     6399     6399 TESTTBS             5       5      6364          3     1278     6390 USERS               3       1       363        363      363      363



    评论暂缺

    (Required)
    (Required, will not be published)