Working with Tablespaces

Tablespaces and the Structure of the Database

       People who have worked with computers for any period of time are familiar with the concept of a
file; it’s a place on disk where information is stored, and it has a name. Its size is usually not fixed: If
you add information to the file, it can grow larger and take up more disk space, up to the maximum
available. This process is managed by the operating system, and often involves distributing the
information in the file over several smaller sections of the disk that are not physically near each
other. The operating system handles the logical connection of these smaller sections without your
being aware of it at all. To you, the file looks like a single whole.
Oracle uses files as a part of its organizational scheme, but its logical structure goes beyond
the concept of a file. A datafile is an operating system file used to store Oracle data. Each datafile
is assigned to a tablespace—a logical division within the database. Tablespaces commonly include
SYSTEM (for Oracle’s internal data dictionary), SYSAUX (for auxiliary internal objects), USERS (for
user objects), and others for application tables, indexes, and additional database structures.
The datafiles can have a fixed size or can be allowed to extend themselves automatically
when they are filled, up to a defined limit. To add more space to a tablespace, you can manually
extend your datafiles or add new datafiles. New rows can then be added to existing tables, and
those tables may then have rows in multiple datafiles.
Each table has a single area of disk space, called a segment, set aside for it in the tablespace.
Each segment, in turn, has an initial area of disk space, called the initial extent, set aside for it in
the tablespace. Once the segment has used up this space, the next extent, another single area of
disk space, is set aside for it. When it has used this up as well, yet another next extent is set aside.
This process continues with every table until the whole tablespace is full. At that point, someone
has to add a new file to the tablespace or extend the tablespace’s files before any more growth in
the tables can take place.
Every database also contains a SYSTEM tablespace, which contains the data dictionary as well
as the names and locations of all the tablespaces, tables, indexes, and clusters for this database. The
objects within the SYSTEM tablespace are owned by the SYS and SYSTEM users; no other users
should own objects in this tablespace because they may impact the rest of the database.

                 You can rename a tablespace via the alter tablespace command.

     You can query the USER_TABLESPACES data dictionary view to see the tablespaces available in
the database. Tablespaces are created by privileged users, via the create tablespace command.
The simplest syntax is:

create tablespace tablespace_name
datafile datafile_name filesize;

     Database administrators specify the tablespace space-allocation method when creating the
tablespace. The following example creates a tablespace whose initial space allocation is a single
100MB file, but that file can automatically extend itself as needed:

CREATE TABLESPACE USERS_2
DATAFILE '/U01/ORADATA/USERS_2_01.DBF'
SIZE 100M AUTOEXTEND ON;

    The Contents column of USER_TABLESPACES shows the type of objects supported in each
tablespace. The following shows a sample listing from an Oracle installation:

select Tablespace_Name, Contents from USER_TABLESPACES;

       The SYSTEM, SYSAUX, and USERS tablespaces support permanent objects—tables, indexes,
and other user objects. The TEMP tablespace supports only temporary segments—segments created
and managed by Oracle to support sorting operations. The UNDOTBS1 tablespace supports undo
segment management.

     When you create a table without specifying a tablespace, the table will be stored in yourdefault tablespace. You can see that setting via the USER_USERS data dictionary view:

SELECT DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM USER_USERS;

SYS.USER_USERS：Information about the current user

SELECT * FROM USER_USERS;

    In this example, the default tablespace is USERS and the temporary tablespace is TEMP. Those
settings can be changed via the alter user command.

    Although USERS is your default tablespace, you may not have any quota on the tablespace.
To see how much space you have been granted in a tablespace, query USER_TS_QUOTAS, as
shown in the following listing:(QUOTA-限额)

USER_TS_QUOTAS:Tablespace quotas for the user

select * from USER_TS_QUOTAS;

    The space in tablespaces can be locally managed. As an alternative, the space management
can be dictionary managed (the space management records are maintained in the data dictionary).
In general, locally managed tablespaces are simpler to administer and should be strongly favored.
You can display the extent management setting via the Extent_Management column of USER_
TABLESPACES:

select Tablespace_Name, Extent_Management from USER_TABLESPACES;

    In a locally managed tablespace, the space map for the tablespace is maintained in the
headers of the tablespace’s datafiles. To see the datafiles allocated to a tablespace, database
administrators can query the DBA_DATA_FILES data dictionary view; there is no equivalent
view for nonprivileged users.
   DBA_DATA_FILES :Information about database data files

select * from dba_data_files

     The default values for storage are operating system specific. You can query USER_TABLESPACES
to see the block size and the defaults for the initial extent size, next extent size, minimum number
of extents, maximum number of extents, and pctincrease settings for objects:

select Tablespace_Name,
Block_Size,
Initial_Extent,
Next_Extent,
Min_Extents,
Max_Extents,
Pct_Increase
from USER_TABLESPACES;

  To override the default settings, you can use the storage clause when creating a table or index.See the entry for the storage clause in the Alphabetical Reference for the full syntax. In general,you should work with the database administrators to establish appropriate defaults for the locallymanaged tablespaces and then rely on that sizing for your objects. Unless there are objects withextraordinary space requirements, you should avoid setting custom sizes for each of your tablesand indexes. If you do not specify a storage clause when you create an object, it will use thedefault for its tablespace. If you do not specify a tablespace clause, the object will be stored in your default tablespace.

RECYCLEBIN Space in Tablespaces
         1、RECYCLEBIN :User view of his recyclebin .

         2、DBA_RECYCLEBIN Description of the Recyclebin view accessible to the user

 

     Dropped objects do not release their allocated space unless you specify the purge clause when
dropping them. By default, the objects maintain their space, allowing you to later recover them
via the flashback table to before drop command.

      The space currently used by dropped objects is recorded in each user’s RECYCLEBIN view (or,
for the DBA, the DBA_RECYCLEBIN view). You can see how many bytes you are presently using
in each tablespace, and you can use the purge command to purge old entries from the RECYCLEBIN.
Here’s an example:

 

select Space --number of blocks still allocated
from RECYCLEBIN
where Ts_Name = 'USERS';

comment on column USER_RECYCLEBIN.SPACE is 'Number of blocks used by this object';

(支持八个空间段)

表空间的类别:

1、Read-Only Tablespaces

2、nologging Tablespaces

3、Temporary Tablespaces

4、Tablespaces for System-Managed Undo

5、Bigfile Tablespaces

6、Encrypted Tablespaces

nologging Tablespaces
    You can disable the creation of redo log entries for specific objects. By default, Oracle generates
log entries for all transactions. If you wish to bypass that functionality—for instance, if you are
loading data and you can completely re-create all the transactions—you can specify that the
loaded object or the tablespace be maintained in nologging mode.
You can see the current logging status for tablespaces by querying the Logging column of
USER_TABLESPACES.

SELECT t.LOGGING FROM User_Tablespaces t

Temporary Tablespaces
     When you execute a command that performs a sorting or grouping operation, Oracle may create a
temporary segment to manage the data. The temporary segment is created in a temporary tablespace,
and the user executing the command does not have to manage that data. Oracle will dynamically
create the temporary segment and will release its space when the instance is shut down and
restarted. If there is not enough temporary space available and the temporary tablespace datafiles
cannot auto-extend, the command will fail. Each user in the database has an associated temporary
tablespace(查询user_users视图，找到它的默认表空间和默认的临时空间)—there may be just one such tablespace for all users to share. A default temporary tablespace is set at the database level so all new users will have the same temporary tablespace unless a different one is specified during the create user or alter user command(默认所有用户使用同一个临时表空间).You can create multiple temporary tablespaces and group them. Assign the temporary tablespaces to tablespace groups via the tablespace group clause of the create temporary tablespaceor alter tablespace command. You can then specify the group as a user’s default tablespace.Tablespace groups can help to support parallel operations involving sorts.

Tablespaces for System-Managed Undo
     You can use Automatic Undo Management (AUM) to place all undo data in a single tablespace.
When you create an undo tablespace, Oracle manages the storage, retention, and space utilization
for your rollback data via system-managed undo (SMU). When a retention time is set (in the
database’s initialization parameter file), Oracle will make a best effort to retain all committed
undo data in the database for the specified number of seconds. With that setting, any query taking
less than the retention time should not result in an error as long as the undo tablespace has been
sized properly. While the database is running, DBAs can change the UNDO_RETENTION
parameter value via the alter system command.
You can guarantee undo data is retained, even at the expense of current transactions in the
database. When you create the undo tablespace, specify retention guarantee as part of your
create database or create undo tablespace command. Use care with this setting, because it may
force transactions to fail in order to guarantee the retention of old undo data in the undo tablespace.

Supporting Flashback Database
      You can use the flashback database command to revert an entire database to a prior point in time.
DBAs can configure tablespaces to be excluded from this option—the alter tablespace flashback
off command tells Oracle to exclude that tablespace’s transaction from the data written to the
flashback database area.

    

--建立表空间
CREATE TABLESPACE USERS_2
DATAFILE '&1/a.DBF'
SIZE 100M AUTOEXTEND ON;
或者
CREATE TABLESPACE USERS_2
DATAFILE '&1/a.DBF'
SIZE 100M AUTOEXTEND ON NEXT 10m;
(表示按照每次10M的速度递增)

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

1、查询表空间的信息

SELECT * FROM user_tablespaces;

2、查询表空间所属的物理位置(本地磁盘)

SELECT * FROM DBA_DATA_FILES

3、查询当前用户所属的表空间(user_users视图为当前用户的基本信息)

SELECT * FROM user_users;

4、用户表空间的限制(MAX_BYTES和MAX_BLOCKS值为负数(-1)，代表无限制)

SELECT * FROM user_ts_quotas;