Posts

Shrinking Smallfile Tablespaces in Oracle 23ai(23.7)

By -
  As you may know, starting from Oracle 23ai, we can shrink permanent tablespaces. However, that was limited to bigfile tablespaces, and shrinking smallfile tablespaces was not possible. Although, by default, tablespaces are created as bigfile tablespace. Now, in  Oracle 23.7 ,  shrinking smallfile tablespaces  is possible and we can shrink these tablespaces using the dbms_space.tablespace_shrink procedure. This procedure executes familiar commands such as ‘alter table move online’ and ‘alter index rebuild’ to shrink smallfile tablespaces. PROCEDURE SHRINK_TABLESPACE Argument Name Type In / Out Default ? - - - - - - - - - - - - - - - - - TS_NAME VARCHAR2 IN SHRINK_MODE NUMBER IN DEFAULT TARGET_SIZE NUMBER IN DEFAULT SHRINK_RESULT CLOB OUT ITERATIONS NUMBER IN DEFAULT Test Scenario To demonstrate this feature, let’s create a smallfile tablespace and populate it with some tables , drop a few tables and finally shrink the tablespace. Step 1: Create...
Post a Comment
Read more

Key Features No Longer Supported in Oracle 23ai

By -
Image
  Oracle regularly introduces numerous features in each version while deprecating or desupporting certain older ones. “Deprecate” means a feature will no longer receive improvements and will eventually become desupported in future versions. “Desupport” indicates that bug fixes will no longer be provided, and in some cases, the feature may become completely unusable. Below, we review some notable features that Oracle 23ai no longer supports: Database Upgrade Assistant (DBUA) and Manual Upgrades From version 23ai, Oracle recommends using  AutoUpgrade  for database upgrades. The DBUA tool is no longer supported on Linux environments, nor are manual upgrade scripts such as  catupgrd ,  dbupgrade , and  catctl . However, DBUA remains usable in Windows environments. exp/imp Tools Oracle states in its documentation that the  exp/imp  tools are no longer supported. Instead, DBAs are encouraged to use  DataPump . Despite this, exp/imp tools are still ...
Post a Comment
Read more

DBMS_DEVELOPER.GET_METADATA in Oracle 23ai(23.7)

By -
  In an Oracle database, to extract object metadata, we can use the DBMS_METADATA package. It provides many useful procedures and functions, such as GET_DDL, GET_DEPENDENT_DDL, and SET_TRANSFORM_PARAM. Additionally, some tools, like SQLcl, provide their own methods for extracting metadata — for example, the DDL command. Starting with version 23.7, Oracle introduced another method to extract object metadata. In this version, we can use  DBMS_DEVELOPER  alongside DBMS_METADATA. DBMS_DEVELOPER is user-friendly, and generates output in JSON format. The DBMS_DEVELOPER package currently supports only TABLE, INDEX, and VIEW, while DBMS_METADATA is much more powerful and supports nearly all object types. DBMS_DEVELOPER has only one function, called  GET_METADATA : SQL > DESC DBMS_DEVELOPER FUNCTION GET_METADATA RETURNS JSON Argument Name Type In / Out Default ? - - - - - - - - - - - - - - - - - - - - - NAME VARCHAR2 IN SCHEMA VARCHAR2 IN DEFAULT ...
Post a Comment
Read more