Oracle manages disk space and resources

• 1. Recoverable space allocation
• 2. Removable table space
• 3.Oracle Segment Shrinkage function
• 4.Oracle Database Resource Management
• Reference

1. Recoverable space allocation 1.1 understanding recoverable space allocation

In general, we issue a large database operation, such as creating a large table index, and if the table space is insufficient, the database will eventually terminate the operation.
The recoverable spatial allocation feature can cause such operations to hang, wait for the DBA to handle it, and then recover the large database operations after successful processing, thus avoiding such tricky problems and saving time.

1.2 Configuring recoverable space allocation

Related parameters and usage:

--参数resumable_timeoutshow parameter resumable_timeout--实例级别启用resumablealter system set resumable_timeout = 7200;--实例级别禁用resumablealter system set resumable_timeout = 0;--赋权resumable权限给某个用户grant resumable to jingyu;--会话级别修改--启用resumablealter session enable resumable;--设定挂起时长alter session enable resumable timeout 144000;--设定名称alter session enable resumable name ‘Create Big Index‘;--禁用resumablealter session disable resumable;--查询DBA_RESUMABLE了解挂起语句的详细信息set linesize 140col name for a40col error_msg for a30select USER_ID, SESSION_ID, INSTANCE_ID, STATUS, NAME, ERROR_MSG from dba_resumable;

Experiment-Configure a recoverable space allocation using the current session:

--If the table space is insufficient, creating the table will fail directly sql> show Useruser is "Jingyu" sql> create TABLE T_S1 as SELECT * from Dba_objects;create table t_s 1 AS SELECT * from Dba_objects*error at line 1:ora-01652:unable to extend temp segment by-in Tablespace dbs_d_jingyu- -If session resumable is enabled, then create table will suspend operation Sql> alter session enable resumable timeout 7200; Session altered. Sql> CREATE TABLE T_s1 as SELECT * from Dba_objects;  Here for a long time-we can see the corresponding log information from the alarm log, the operation is suspended: Wed Jan 09:51:55 2016statement in resumable session ' User Jingyu, session 62, Instance 1 ' is suspended due to ora-01652:unable to extend temp segment by tablespace dbs_d_jingyu--expansion table Space sql& Gt Alter tablespace Dbs_d_jingyu add datafile size 30M; Tablespace altered.--at this time the alarm log information can see the operation continue: Wed Jan 10:01:12 2016statement in resumable session ' User Jingyu ', session 6 2, Instance 1 ' was Resumedcompleted:alter tablespace dbs_d_jingyu add datafile size 30M autoextend on--then go see just the pending build statement has been executed successfully Sql> CREATE TABLE T_s1 as SELECT * from Dba_objects; Table created.--disables the resumablealter session disable resumable for current sessions;

1.3 Using the Recoverable space allocation feature

--触发器基本结构如下，需要根据具体需求来完善create or replace trigger resumable_notify  after suspend on databasedeclare  -- variables, if requiredbegin  -- check DBA_RESUMABLE for user ID, type of  -- object, then send e-mail  dbms_resumable.space_error_info(. . .);  if object_type = ‘TABLE‘ and object_owner = ‘HR‘ then    -- give DBA 2 hours to resolve    dbms_resumable.set_timeout(7200);    utl_mail.send(‘[email protected]‘,. . .);  else    dbms_resumable.abort(. . .);  end if;end;

2. Removable table space

Use data pump to export the metadata of the objects in the table space, copy the data files that make up the tablespace to the destination database, and then import the tablespace's metadata into the destination database.

2.1 Configuring a removable table space

To transfer data between platforms, the source platform and destination platform must be members of the list of platforms supported by Oracle.

2.1.1 Determining compatibility requirements

The functional compatibility of the Oracle database is controlled by the compatible initialization parameter, which enables or disables certain features in the database. When you create a collection of removable tablespaces, Oracle determines the minimum compatibility level for the target database and stores the corresponding values in the metadata of the removable dataset. Starting with Oracle Database 11g, table spaces can always be moved to another database with the same or higher compatibility level, regardless of the target platform.
In addition, two databases must use the same character set.

2.1.2 Determining byte order Requirements

--Query all supporting platforms for the removable tablespace and its endian_format:sql> select platform_id, Platform_name, Endian_format from V$transportable_ Platform ORDER by 3, 1; platform_id platform_name endian_format------------------------------------------------------                  ---------------------------1 Solaris[tm] OE (32-bit) Big 2 solaris[tm] OE (64-bit)                        Big 3 HP-UX (64-BIT) Big 4 HP-UX IA (64-bit)         Big 6 aix-based Systems (64-bit) Big 9 IBM zSeries Based Linux Big Apple Mac OS Big IBM Power Based Linux Big 5 H P Tru64 UNIX Little 7 Microsoft Windows IA (32-bit) Little 8 Micro Soft Windows ia (64-bit) Little Linux ia (32-bit) Little one Linux IA     (64-bit)                   Little Microsoft Windows x86 64-bit Little Linux x86 64-bit            Little HP Open VMS Little, Solaris Operating System (x86)        Little HP IA Open VMS Little Solaris Operating System (x86-64) Little Apple Mac OS (x86-64) Little20 rows selected.--querying the byte-order format of the current platform select Platform_nam E My_platform, Endian_format my_endian_format from V$transportable_platform join V$database using (platform_name); Sql> Select Platform_name my_platform, Endian_format My_endian_format 2 from V$transportable_platform 3 join v$ Database using (platform_name); My_platform My_endian_format------------------------------------------------------------------------Li Nux x86 64-bit Little

2.2 Transport Table Space

Transfer table Space Core steps:
1) The table space on the source database is set to read-only mode;
2) Extract table space metadata on the source database;
3) If the source and destination byte order format is different, convert the table space content;
4) Copy table spatial data files and table space metadata to the destination database;
5) The Destination database imports table space metadata;
6) The table space of the source and destination databases is set to read-write mode.

--用户的默认表空间和默认临时表空间select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from user_users;--用户用到的表空间select tablespace_name from user_tables unionselect tablespace_name from user_tab_partitions unionselect tablespace_name from user_indexes unionselect tablespace_name from user_ind_partitions;

2.2.1 Using the EM transport table space

Web graphical interface operation,
1. Click the data movement---transport table space, generate a transportable set of table spaces, and then follow the prompts to generate the step-by-step;
2. Integrate the existing transportable table space set in the destination database, click Data Movement, transport table space, and then follow the prompts to integrate in the destination database.
Here I experiment in generating a transportable tablespace set times wrong: ora-02097,ora-19529.

 --according to the MOS document: Received ORA-19529 attempting to do A transportable tablespace Export Through OEM (document ID 2073402.1) OR A-02097:parameter cannot be modified because specified value is Invalidora-19529:pattern/home/kqualls/transport/epsdev /O1_MF_EKB_CONS_B6QC1HBM_.DBF in initialization parameter Db_file_name_convert have an Oracle Managed Files file name.--Reason : EM Cloud Control 12c does not support conversion of OMF files.   Currently only user managed files is supported using the EM feature. There is an enhancement logged, Bug 21233808-cloud Control TTS generates invalid Db_file_name_convert command ora-02097- -Workaround: Create RMAN job in EM Cloud Control Job system, enter command line script commands there. Using the RMAN job in the EM job system would allow the process to use the database parameters to convert the datafiles rat Her than using the-EM Perl scripts that don ' t support OMF files.  

PS: Even if there is no problem, the general production environment transport table Space also does not adopt EM, because many production environments are not installed EM, or complex network environment factors cause inconvenient use of EM. or manually using SQL to transfer table space is more reliable.

2.2.2 Using SQL to transport table spaces

Reference:

• Oracle uses SQL to transport table space

3.Oracle Segment Shrinkage function

Reference:

• Oracle Segment Shrink Feature

4.Oracle Database Resource Management

Reference:

• Oracle Database Resource Management

Reference

• The OCP Certification Exam Guide (1z0-053) [M]. Tsinghua University Press, 2010.

Oracle manages disk space and resources