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

Rename LOB Segments, partitions, sub partitions in Oracle 23ai

From Oracle 23ai, you can rename LOB (Large Object) segments, partitions and sub partitions using “ALTER TABLE RENAME LOG” statement. In previous versions of the database to rename LOB segment you need to move it using “ALTER TABLE MOVE” statement.

When renaming a segment make sure the segment is unique within the database, in case of any conflicts you will receive ORA-64233 or ORA-63223 error messages. To make sure segment available in the database you can query ALL_LOBS, ALL_PARTITIONS, ALL_LOG_SUBPARTITIONS.

You can use below syntax/example to rename LOB segment

ALTER TABLE <TABLE_NAME> RENAME LOB (<column_name>) <segment_name> to <new_segment_name>;

SQL> SELECT TABLE_NAME, COLUMN_NAME, SEGMENT_NAME FROM USER_LOBS WHERE TABLE_NAME = 'LOB_TAB1’;

TABLE_NAME COLUMN_NAME SEGMENT_NAME
-------------------- ----------------------- -------------------------
LOB_TAB1        LOB_COL1            LOB_SEG1

SQL> ALTER TABLE lob_tab1 RENAME LOB (lob_col1) log_seg1 TO log_seg1_new;

SQL> SELECT TABLE_NAME, COLUMN_NAME, SEGMENT_NAME FROM USER_LOBS WHERE TABLE_NAME = 'LOB_TAB1’;

TABLE_NAME COLUMN_NAME SEGMENT_NAME
-------------------- ----------------------- --------------------
LOB_TAB1        LOB_COL1            LOB_SEG1_NEW


You can also rename partition, sub partition segments using below examples

For Partition,
ALTER TABLE lob_tab2 RENAME LOB(lob_col2) PARTITION lob_seg2 TO lob_seg2_new;

For Sub partition,
ALTER TABLE lob_tab3 RENAME LOB(LOB_COL3) SUBPARTITION lob_seg3 TO lob_seg3_new;

The new capabilities simplify the management of LOBs, partitions by reducing the overhead in the Oracle Database.

Thanks & Regards,

https://oracleracexpert.com

Webinar: Unlocking Oracle Database 23c’s Advanced Security Features

 Join this webinar to learn about Oracle Database 23c's most recent security improvements. We'll go over new features including enhanced authentication techniques and encryption, demonstrating how they help safeguard your data and bolster security against changing threats. This webinar is Ideal for database administrators and IT specialists wishing to increase database security.

Date and time: Dec 13th 2024, 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• SQL Firewall
• Audit
• Authentication
• Authorization
• Encryption
• Autonomous Database
• Other

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com
Here is the Webinar Meeting Link

Click here to view the Presentation

Thanks & Regards,
http://www.oracleracexpert.com

ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^212","kglseshtTable")

We recently encountered the following error in 19c, which typically occurs when the database needs additional shared memory. In most of the cases setting MAX_SGA_SIZE to a higher value will resolve the issue.

Below are some possible cause

•  Insufficient memory allocated via initialization parameters
•  Fragmentation in app design
•  Auto tuning issues
•  A Bug causing the issue
•  Memory leaks

ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^334","kglseshtTable")
< ORA-00604: error occurred at recursive SQL level 1 < ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","unknown object","KGLH0^f185eace","kglHeapInitialize:temp")
< ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^394","kglseshtTable")


The error message will provide the amount of memory unavailable, memory pool facing the issue and failed allocation details

 

I would highly suggest running below query to get the optimal value for SGA_TARGET
Select * from V$SGA_TARGET_ADVICE

Note that in order to initialization parameters to take into effect we need to bounce the instance.

AGLT>oerr ora 04031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.
// parameter MAX_SGA_SIZE.

Refer below links for Oracle support notes

• This Oracle support note provides information about ORA-04031 related bugs and which release they were fixed

OERR: ORA-4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" (Doc ID 4031.1)

• This Oracle note provides detailed troubleshooting and diagnosing details

Troubleshooting and Diagnosing ORA-4031 Error [Video] (Doc ID 396940.1)

• This Oracle note provides detailed understanding and tuning of the of the shared pool

NOTE:62143.1 - Troubleshooting: Understanding and Tuning the Shared Pool

Thanks & Regards,

https://oracleracexpert.com

Annotations in Oracle 23ai

Oracle Database23ai introduced new feature annotations, using this feature user can add additional comments to objects and allow storing database object metadata. It helps to share the metadata across applications.

You can add annotations to any supported schema objects with CREATE privilege and you can add or drop annotations using ALTER privilege, the object can have more than one annotation as well. The supported schema objects include tables, indexes, views, materialized views. An annotation will consist of a name and an optional value, which can be entered as freeform text fields.

You can drop the annotations, or it will be dropped when a schema object is dropped. You can query USER|ALL|DBA_ANNOTATIONS_USAGE to view annotations for an object.

Annotation has three clauses i.e ADD, DROP or REPLACE

ADD – create annotations for an existing object.
DROP – removes the annotation from an object.
REPLACE – change annotation_value. This clause used with ALTER statements.

In below example, we’re adding annotation both table and columns

CREATE TABLE emp (
emp_id number ANNOTATIONS (ColumnInfo ‘Employee ID’),
emp_name varchar2(30) ANNOTATIONS (ColumInfo ‘Employee Name’),
dept varchar2(20) )
ANNOTATIONS ( TableInfo ‘Employee Table’);

SQL> SELECT ANNOTATION_NAME, ANNOTATION_VALUE from USER_ANNOTATIONS_USAGE WHERE Object_Name ='EMP' AND Object_Type = 'TABLE' AND Column_Name IS NULL;

ANNOTATION_NAME ANNOTATION_VALUE
-------------------- --------------------
TABLEINFO Employee Table

You can drop annotation from an existing table using below example
SQL> ALTER TABLE emp ANNOTATIONS (DROP TableInfo);

You can add annotation to the table using below example, note that ADD keyword is optional.
SQL> ALTER TABLE emp ANNOTATIONS (ADD TableInfo ‘Employee Table’);

SQL> SELECT ANNOTATION_NAME, ANNOTATION_VALUE from USER_ANNOTATIONS_USAGE WHERE Object_Name ='EMP' AND Object_Type = 'TABLE' AND Column_Name IS NOT NULL;

ANNOTATION_NAME ANNOTATION_VALUE
-------------------- --------------------
EMP_ID Employee ID
EMP_NAME Employee Name

In below example you can add, replace and drop annotations at column level. Note that ADD keyword is optional.

SQL> ALTER TABLE EMP modify dept ANNOTATIONS (ADD ColumnInfo ‘Dept Name’);
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (REPLACE ColumnInfo ‘Deptartment Name’);
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (DROP ColumnInfo);

Thanks & Regards,

https://oracleracexpert.com