DBMS_SPACE package enhancements in Oracle 23ai

The DBMS_SPACE package used to analyze segment growth and space requirements in the database. From Oracle 23ai onwards you can use this package to shrink a big file tablespace to reclaim unused space. In case if there are any unsupported objects you can identify in analyze phase.

First analyze big tablespace to see how much space can be reclaimed before performing the shrink. You need to provide tablespace name and shrink and shrink mode to perform this task.

• TS_MODE_ANALYZE – This mode analyzes the tablespace
• TS_MODE_SHRINK – This mode is default, and it shrinks tablespace to reclaim space
• TS_MODE_SHRINK_FORCE - This mode moves objects online and in case of failure it will attempt to move offline.

You should set “serveroutput” g on to show the results on the terminal
SQL> set serveroutput on;

Analyze how much space can be reclaimed using TS_MODE_ANALYZE mode

SQL> execute DBMS_SPACE.SHRINK_TABLESPACE (‘USER_TBS1’, SHRINK_MODE => DBMS_SPACE.TS_MODE_ANALYZE);
PL/SQL procedure successfully completed.

Here is the simplified version of the output

ANALYZE RESULTS
Total Movable Objects: 2
Total Movable Size(GB): 1.32
Original Datafile Size(GB): 5
Suggested Target Size(GB): 2.59
Process Time: +00 00:00:00.701704

Now you can perform the actual shrink operation to reclaim the space, note that DBMS_SPACE.TS_MODE_SHRINK is used by default if you don’t mention explicitly. If you don’t mention any TARGET_SIZE by default it will use DBMS_SPACE.TS_TARGET_MAX_SHRINK

SQL> execute DBMS_SPACE.SHRINK_TABLESPACE (‘USER_TBS1’);
PL/SQL procedure successfully completed.

Or

SQL> execute DBMS_SPACE.SHRINK_TABLESPACE (‘USER_TBS1’, SHRINK_MODE => DBMS_SPACE.TS_MODE_SHRINK);
PL/SQL procedure successfully completed.

Here is the simplified version of the output

SHRINK RESULTS
Total Moved Objects: 2
Total Moved Size(GB): 1.32
Original Datafile Size(GB): 5
New Datafile Size(GB): 1.44
Process Time: +00 00:00:21.501692
PL/SQL procedure successfully completed.

Note that you may not be able to reduce exact size that you got from analyze.
You can run below query to get the size of the tablespace.

SQL> SELECT tablespace_name, sum(bytes)/1024/1024/1024 as ”Tablespace Size (GB)”
FROM dba_data_files
GROUP BY tablespace_name;

You can run below query to check the segment size and you can add where condition for specific segment name or type

SQL> SELECT segment_name, segment_type, bytes/1024 size_kb
FROM user_segments;

DBMS_SPACE package has below subprograms and used for various operations: -

ASA_RECOMMENDATIONS Function - This function returns recommendations
SPACE_USAGE Procedure - This procedure shows the usage of data blocks under high water mark and auto segment space management
UNUSED_SPACE Procedure - This procedure shows unused space in an object
SHRINK_TABLESPACE Procedure - This procedure used to resize or analyze big file tablespace
OBJECT_DEPENDENT_SEGMENTS Function - This function returns the list of segments associated with an object
OBJECT_GROWTH_TREND Function - This function provides space usage of object at a specific point-in-time
FREE_BLOCKS Procedure - This procedure provides information about free blocks
ISDATAFILEDROPPABLE_NAME Procedure - This procedure checks datafile is droppable
CREATE_INDEX_COST Procedure - This procedure determines cost of creating an index
CREATE_TABLE_COST Procedure - This procedure determines the size of the table

The Shrink Tablespace simplifies the procedure to reclaim the free space from datafiles by organizing objects.

Thanks & Regards,

https://oracleracexpert.com

Automatic Temporary Tablespace and undo tablespace Shrink in Oracle21c

Automatic Temporary Tablespace Shrink

Temporary tablespaces are used for storing temporary data and Users will notice high temporary tablespace usage when using sorts, hash joins and query transformations and DBA needs to manually size the temporary tablespace.

The automatic temporary tablespace helps to take care of below operations automatically

• Shrink temporary tablespace to reclaim unused space
• Grow temporary tablespace based upon high temp usage

You can run below query to identify the free space

SQL> SELECT * FROM dba_temp_free_space;

Or

SQL> SELECT TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 "TABLESPACE_SIZE", FREE_SPACE/1024/1024 "FREE_SPACE" from dba_temp_free_space;

Automatic Undo  Tablespace Shrink

Oracle Database creates and manages the information needed to roll back, or undo, changes before they are committed. These undo records are stored in the undo segments in an undo tabs pace.

An undo tablespace is used for undo management, it helps to undo data or rollback any transaction. In 11g Oracle introduced automatic undo management it helps to manage undo segments in a database. The UNDO_MANAGEMENT parameter is set to AUTO or null enables automatic undo management and UNDO_TABLESPACE specifies the name of the undo tablespace.

Use UNDO_RETENTION initialization parameter to specify minimum undo retention period in seconds and you will see better results with a fixed-size undo tablespace when using Automatic tuning of undo retention. The Undo Advisor can help you estimate the capacity and you can access through Oracle Enterprise Manager or using DBMS_ADVISOR or DBMS_UNDO_ADV package.

The undo tablespace can grow large and the easy way to reclaim the space from undo tablespace is to create a new undo tablespace and set the database with new undo tablespace and drop the old undo tablespace.

SQL> CREATE UNDO TABLESPACE UNDO2 DATAFILE '/u01/oradata/ORCL/undo02.dbf' SIZE 5G AUTOEXTEND ON ;
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDO2;
SQL> DROP TABLESPACE UNDO1 INCLUDING CONTENTS AND DATAFILES;

Oracle 21c introduces automated way to recover the space, this feature shrinks the undo tablespace by dropping the expired segments and extents, also it performs the data file shrink if possible. Note that data file shrink is based upon allocated extents.

This feature takes care of below operations automatically

• Recovery space from transactions that are not active
• Allow large transactions to run successfully by recovering space from expired undo

Please see the example for using DBMS_ADVISOR for UNDO.

DECLARE
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1);
DBMS_ADVISOR.EXECUTE_TASK(tname);
END;
/

Where

TARGET_OBJECTS is the undo tablespace of the system
START_SNAPSHOT Starting snapshot in the AWR to perform analysis
END_SNAPSHOT Ending snapshot in the AWR to perform analysis
BEGIN_TIME_SEC The beginning time of the period and now.
END_TIME_SEC The ending time of the period and now

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE

Generate Table, Index or tablespace DDLs in oracle

When migrating database from one server to another you need Tablespace creation DDL. Also it is very useful when copying specific table or index or exp/imp from one database to databases.

Here are few ways to generate the tablespace script from source database.

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;
SQL>SPOOL TBS_DDL.SQL
SQL>SELECT DBMS_METADATA.GET_DDL('TABLESPACE',DBA_TABLESPACES.TABLESPACE_NAME) FROM DBA_TABLESPACES;
SQL>SPOOL OFF

You can see below command if you want the TABLESPACE creation script specific to one Table space.

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;

You can use below command to generate specific TABLE, INDEX Script from a user

Syntax:-
select dbms_metadata.get_ddl('TABLE','<TABEL NAME>','<SCHEMA>') from dual;
select dbms_metadata.get_ddl('INDEX','<INDEX NAME>','<SCHEMA>') from dual;

Ex:-
select dbms_metadata.get_ddl('TABLE','EMP_SAL','EMP') from dual;
select dbms_metadata.get_ddl('INDEX','EMPNO_IDX','EMP') from dual;

You can use below command to all TABLE, INDEX Script from a user. First, connect to user

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME)

FROM USER_TABLES U;

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)

FROM USER_INDEXES U;  

In case if you need a View DDL, you can run below command;
SQL>select DBMS_METADATA.GET_DDL('VIEW',<View_Name>’) from dual;
or
SQL>select DBMS_METADATA.GET_DDL('VIEW','<view_name>','<schema_name>') from DUAL;

You can run below command to generate the DDL statements of a SCHEMA
set pages 30000
set linesize 1000
set lines 500

SQL> SELECT DBMS_METADATA.GET_DDL('USER','<schema_name>') FROM dual;

You can run below command to generate DDL statements for more than one schema

SQL> SELECT DBMS_METADATA.GET_DDL('USER',U.USERNAME) FROM DBA_USERS U WHERE USERNAME IN ('USER1','USER2');

You can run below command to generate DDL statement of the System Grant/ role granted/object granted to a schema owner

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema_name>') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema_name>') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',<Schema_Name>’) from dual;

You can run below command to generate DDL statement of the role
SQL> SELECT DBMS_METADATA.GET_DDL('ROLE','RESOURCE') from dual;

You can also use SQL Developer to get the DDL script for a specific object.

You can refer oracle documentation for more details on DBMS_METADATA

Thanks & Regards

http://oracleracexpert.com

How to recover Undo or Rollback Tablespace

While handing with undo tablespace you need to be extra cautious due to active transactions in the undo segments. You need to follow different approach depend upon scenario.

Scenario 1: Undo/rollback datafile damaged or dropped accidently when database is up
In this scenario the lost or damaged datafile may contain the active transactions and you cannot able to offline or drop the undo/rollback datafile.

• Startup mount
SQL> STARTUP MOUNT

• Check the status of the datfile
SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;
If the datafile is offline you must bring the datafile online before you recover

• Restore and Recover the datafile
$ rman TARGET / CATALOG rman/*****@rman
RMAN> Restore datafile 'fullpath_and_filename'
RMAN> Recover datafile 'fullpath_and_filename'

• Open the database.
SQL> ALTER DATABASE OPEN;

Scenario 2: Undo/rollback datafile damaged or dropped accidently when database is down

• If using automatic UNDO_MANAGMENT then comment out the parameter in init.ora file. If using rollback segments then comment out ROLLBACK_SEMGNETS parameter

• Mount the database in restricted mode
SQL> STARTUP RESTRICT MOUNT

• Offline the undo or rollback datafile and drop the file
SQL> ALTER DATABASE DATAFILE 'fullpath_and_filename' OFFLINE DROP;

• Open the database and drop the UNDO tablespace or the tablespace which contains the rollback segments
SQL> ALTER DATABASE OPEN
SQL> DROP TABLESPACE tablespace_name INCLUDING CONTENTS;

• Recreate the undo tablespace. If you are using rollback segments, recreate the rollback segment tablespace and rollback segments. Make sure to bring the rollback segments online.

If using Undo tablespace
SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '/oradata/undotbs2_01.dbf' SIZE 100M REUSE AUTOEXTEND ON;

If using rollback segment tablespace
SQL> CREATE TABLESPACE rollback_tbs DATAFILE '/oradata/rollback_tbs01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL;
SQL> CREATE ROLLBACK SEGMENT rollback1 TABLESPACE rollback_tbs;
SQL> ALTER ROLLBACK SEGMENT rollback1 ONLINE;

• Modify the parameter file settings
If using UNDO tablespace
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=new_undo_tablespace_name

If you are using the rollback segment tablespace, include the rollback segments that you created in previous step in ROLLBACK_SEMGNETS parameter in init.ora file

• Take the database out of restricted mode.
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Oracle 11g introduced a new feature called RMAN UNDO Bypass. The RMAN backup command no longer backs up the UNDO data that is not needed for recovery. Prior to Oracle 11g, all UNDO transactions that were already committed also backed up. This backup undo optimization minimizes the backup time and storage.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com