Oracle Tablespace Management

Oracle Tablespace Management

1. View tablespace information

SQL> SELECT tablespace_name, status, contents, logging, extent_management, allocation_type,

 segment_space_management from dba_tablespaces;

TABLESPACE_NAME STATUS     CONTENTS   LOGGING            EXTENT_MANAGEMENT    ALLOCATION_TYPE    SEGMENT_SPAC
--------------- ---------- ---------- ------------------ -------------------- ------------------ ------------
SYSTEM          ONLINE     PERMANENT  LOGGING            LOCAL                SYSTEM             MANUAL
SYSAUX          ONLINE     PERMANENT  LOGGING            LOCAL                SYSTEM             AUTO
UNDOTBS1        ONLINE     UNDO       LOGGING            LOCAL                SYSTEM             MANUAL
TEMP            ONLINE     TEMPORARY  NOLOGGING          LOCAL                UNIFORM            MANUAL
USERS           ONLINE     PERMANENT  LOGGING            LOCAL                SYSTEM             AUTO

SQL> SELECT file_name, file_id, tablespace_name from dba_data_files;

FILE_NAME                                             FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ---------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system          1 SYSTEM
_kq1w7f1j_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux          3 SYSAUX
_kq1w8j7q_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_          7 USERS
kq1w90kc_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotb          4 UNDOTBS1
s1_kq1w8zf8_.dbf

FILE_NAME                                             FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ---------------

2. Use OMF to close the table space

OMF(Oracle Managed Files): ORACLE data file hosting service

2.1 Close OMF

Because OMF has been opened, close OMF first. To close it, just set the DB_CREATE_FILE_DEST parameter to empty.

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='';

System altered.

SQL> CREATE TABLESPACE tbs_1;
CREATE TABLESPACE tbs_1
                      *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause

You can see that the creation of the table space failed (note: you only need to specify the name to open the OMF to create the table space)

2.2 Open OMF

Just empty the DB_CREATE_FILE_DEST parameter

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/';

System altered.

SQL>

2.2.1 Create a table space using OMF

create tablespace

SQL> CREATE TABLESPACE "USER1";

Tablespace created.

SQL>

view tablespace

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;

FILE_NAME                                                         TABLESPACE_NAME
----------------------------------------------------------------- ---------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_kq1w7f1j_.dbf  SYSTEM
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_kq1w8j7q_.dbf  SYSAUX
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_kq1w8zf8_.db UNDOTBS1
f

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_kq1w90kc_.dbf   USERS
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_user1_kqn9dkxd_.dbf   USER1

SQL>

3. Manage tablespace

3.1 Create a table space

SQL> CREATE SMALLFILE  # Specify whether it is a small file or a large file
  2  TABLESPACE "USER1"   #tablespace name
  3  DATAFILE '/u01/app/oracle/oradata/orcl/user1.dbf' # Indicate the storage location of the data file
  4  SIZE 100M # data file size
  5  AUTOEXTEND ON NEXT 100M # How much table space to automatically grow each time
  6  LOGGING # Whether to enable logging
  7  DEFAULT NOCOMPRESS # no compression by default
  8  ONLINE # is online
  9  EXTENT MANAGEMENT LOCAL AUTOALLOCATE #automatic zone allocation
  10 SEGMENT SPACE MANAGEMENT AUTO; # Whether to automatically segment management

Tablespace created.

SQL>

  • Data file: smallfile, small file; bigfile large file

  • Table space type: default persistent (permanent), and temporary table space (Temporary), undo table space undo;

  • Whether online: Online and Offlie

  • Whether to enable logging: LOGGING and NOLOGGING, if enabled, changes to the data object will be saved to the redo log, otherwise not saved;

    If SQL*Loader is used to Insert data without enabling it, the operation will not be recorded in the redo log, so once the data is lost, it cannot be recovered.

    When unlogged objects are created, they must be backed up in order to be recoverable.

  • Whether automatic area allocation: divided into automatic and unified.

3.2 Modify tablespace

3.2.1 Tablespace Offline

When working offline, the following options are available

OK: If there are no error conditions in any of the tablespace's datafiles, the tablespace can be taken offline normally.

  • Oracle Database ensures that all data is written to disk by checkpointing all datafiles for a tablespace when they are taken offline.

Temporary: A tablespace can be temporarily taken offline even if one or more of its files has errors.

  • Oracle Database checkpoints datafiles that have not been offlined already.

  • If you use the Temporary clause, but no files go offline. Then the tablespace can be brought back online without the need for media recovery.

  • If one or more of the tablespace's files is temporarily taken offline by you due to a write error, the tablespace needs to be recovered before it can be brought back online.

Immediately: The tablespace can be taken offline immediately without Oracle Database needing to checkpoint any datafiles.

  • When you specify immediate, the tablespace's media must be recovered before the tablespace can be brought online.

  • Offline Now cannot be used if the database is running in NOARCHIVELOG mode.

3.2.2 Add data files to tablespace

SQL> ALTER TABLESPACE "USER1" 
ADD DATAFILE '/u01/app/oracle/oradata/orcl/user_2.dbf' size 100M;

Tablespace altered.

SQL>

view data file

SQL> SELECT NAME FROM V$DATAFILE;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_kq1w7f1j_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_kq1w8j7q_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_kq1w8zf8_.dbf
/u01/app/oracle/oradata/orcl/user1.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_kq1w90kc_.dbf
/u01/app/oracle/oradata/orcl/user_2.dbf

6 rows selected.

SQL>

3.2.3 Modify tablespace data files

Modify data file size

SQL> ALTER DATABASE DATAFILE
  '/u01/app/oracle/oradata/orcl/user_2.dbf' RESIZE 120M;

Database altered.

SQL>

Modify the data file to grow automatically

SQL> ALTER DATABASE DATAFILE 
'/u01/app/oracle/oradata/orcl/user_2.dbf'
 AUTOEXTEND ON NEXT 50M MAXSIZE 200M;

Database altered.

SQL>

3.3 Delete tablespace

For dangerous operations, you can delete the tablespace first, save the data files for a period of time, and then delete them after confirming that they are correct.

Syntax: SQL> DROP TABLESPACE dataspace_name [ INCLUDING CONTENTS [ AND DATAFILES ] [ CASCADE CONSTRAINTS ] ];

SQL> DROP TABLESPACE "USER1" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Tablespace dropped.

SQL>

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

TABLESPACE_NAME
---------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

SQL>

Tags: Linux Oracle

Posted by ballhogjoni on Mon, 21 Nov 2022 21:41:42 +1030

Popular Tags

©2023 Programs Wiki