Oracle manages disk space and resources

Oracle manages disk space and resources
1. Recoverable space allocation 1.1 learn about recoverable space allocation

Generally, we initiate a large database operation, such as creating a large table index. If the tablespace is insufficient, the database will terminate the operation.
The recoverable space allocation function suspends such operations and waits for the DBA to process them. After successful processing, the large database operations are automatically restored, which avoids such thorny problems and saves time.

1.2 configure recoverable space allocation

Related Parameters and usage:

-- Parameter specified parameter resumable_timeout -- enable resumablealter system set resumable_timeout = 7200 at the instance level; -- disable resumablealter system set resumable_timeout = 0 at the instance level; -- grant resumable permission to a user to grant resumable to J; -- session-level modification -- enable resumablealter session enable resumable; -- set the suspension duration alter session enable resumable timeout 144000; -- set the name alter session enable resumable name 'create Big Index '; -- disable resumablealter session disable resumable; -- query the details of the pending statement set linesize 140col name for a40col error_msg for a30select USER_ID, SESSION_ID, INSTANCE_ID, STATUS, NAME, ERROR_MSG from future;

Lab-configure the recoverable space allocation for the current session:

-- If the tablespace is insufficient, creating a table will directly fail. SQL> show userUSER is "JINGYU" SQL> create table t_s1 as select * from dba_objects; create table t_s1 as select * from dba_objects * ERROR at line 1: ORA-01652: unable to extend temp segment by 128 in tablespace DBS_D_JINGYU -- If session resumable is enabled, when a table is created, the SQL statement> alter session enable resumable timeout 7200; Session altered is suspended. SQL> create table t_s1 as select * from dba_objects ;... long suspension here -- now we can see the corresponding log information from the alarm log, and the operation is suspended: Wed Jan 13 09:51:55 2016 statement in resumable session 'user JINGYU (99 ), session 62, Instance 1 'was suincluded due to ORA-01652: unable to extend temp segment by 128 in tablespace DBS_D_JINGYU -- extended tablespace SQL> alter Tablespace DBS_D_JINGYU add datafile size 30 M; tablespace altered. -- Now the alarm log information shows that the operation continues: Wed Jan 13 10:01:12 2016 statement in resumable session 'user JINGYU (99), Session 62, Instance 1' was resumedCompleted: alter tablespace DBS_D_JINGYU add datafile size 30 M autoextend on -- check that the pending table creation statement has been executed successfully. SQL> create Table t_s1 as select * from dba_objects; table created. -- disable the resumablealter session disable resumable of the current session;

1.3 Use the recoverable space allocation function

-- The trigger's basic structure is as follows. You need to complete the create or replace trigger resumable_policy 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 ('dba @ company.com ',...); else dbms_resumable.abort (...); end if; end;

2. removable tablespace

Use Data Pump to export the metadata of objects in the tablespace, copy the Data files that constitute the tablespace to the target database, and then import the metadata of the tablespace to the target database.

2.1 configure removable tablespace

To transmit data between platforms, the source and target platforms must be members of the List of platforms supported by Oracle.

2.1.1 determine compatibility requirements

The compatibility of Oracle database functions is controlled by the COMPATIBLE initialization parameter. This parameter enables or disables certain functions in the database. When creating a set of movable tablespaces, Oracle determines the minimum Compatibility Level of the target database and stores the values in the metadata of the movable dataset. From the Oracle Database 11g, regardless of the target platform, the tablespace can always be moved to another Database with the same or higher compatibility level.
In addition, the two databases must use the same character set.

2.1.2 determine the byte sequence requirements

-- Query all supported platforms and 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) large 4 HP-UX IA (64-bit) Big 6 AIX-Based Systems (64-bit) big 9 IBM zSeries Based Linux Big 16 Apple Mac OS Big 18 IBM Power Based Linux Big 5 HP Tru64 UNIX Little 7 Microsoft Windows IA (32-bit) little 8 Microsoft Windows IA (64-bit) Little 10 Linux IA (32-bit) Little 11 Linux IA (64-bit) little 12 Microsoft Windows x86 64-bit Little 13 Linux x86 64-bit Little 15 HP Open VMS Little 17 Solaris Operating System (x86) little 19 hp ia Open VMS Little 20 Solaris Operating System (x86-64) Little 21 Apple Mac OS (x86-64) Little20 rows selected. -- query the byte format of the current platform: select PLATFORM_NAME 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 ------------------------ customized Linux x86 64-bit Little

2.2 Transfer tablespace

Core steps for transferring tablespaces:
1) Table null in the source database is set to read-only mode;
2) extract tablespace metadata from the source database;
3) if the source and destination are in different bytecode formats, convert the tablespace content;
4) copy the tablespace data file and tablespace metadata to the target database;
5) Import tablespace metadata to the target database;
6) The tablespace of the source database and target database is set to read/write mode.

-- Select username, region, region from user_users; -- select tablespace_name from user_tables unionselect tablespace_name from orders unionselect tablespace_name from user_indexes unionselect tablespace_name from orders;

2.2.1 use EM to transmit tablespaces

Web GUI operations,
1. Click data movement-> transfer tablespace-> Generate the table space set that can be transferred, and then generate it step by step as prompted;
2. Click data movement in the destination database-> transfer tablespace-> integrate the existing table space sets that can be transferred, and then integrate them in the destination database step by step as prompted.
An error is reported when I experiment here to generate a table space set that can be transferred: ORA-02097, ORA-19529.

-- According To MOS documentation: Received ORA-19529 Attempting To Do A Transportable Tablespace Export Through OEM (Document ID 2073402.1) ORA-02097: parameter cannot be modified because specified value is invalidORA-19529: pattern/home/kquils/transport/epsdev/o1_mf_ekb_cons_b6qc1hbm _. dbf in initialization parameter db_file_name_convert has an Oracle Managed Files file name. -- cause: EM Cloud Control 12c does not support conversion of OMF files. currently only user managed files are supported using the EM feature. there is an enhancement logged, Bug 21233808-Cloud Control TTS generates invalid db_file_name_convert command ORA-02097 -- Solution: Create RMAN Job in EM Cloud Control Job system, enter command line script commands there. using the RMAN Job in the EM Job system will allow the process to use the database parameters to convert the datafiles rather than using the EM perl scripts that don't support OMF files.

PS: even if there is no problem, EM is not used for the tablespace transmitted in the general production environment, because EM is not installed in many production environments, or it is inconvenient to use EM due to complicated network environment factors. It is more reliable to manually use SQL to transmit tablespaces.

2.2.2 use SQL to transmit tablespaces

Refer:

• Oracle uses SQL to transmit tablespaces

3. Oracle segment contraction

Refer:

• Oracle segment contraction

4. Oracle Database Resource Management

Refer:

• Oracle Database Resource Management

This article permanently updates the link address: