Resolving Oracle Text Index Error DRG-12255: Invalid SEC_DATATYPE in CTXSYS

When creating domain indexes on text-enabled application columns following a database upgrade or migration, database administrators may encounter an unexpected DRG-12255 error. This issue typically surfaces during the first attempt to build a text index on an instance where internal CTXSYS dictionary metadata contains corrupted datatype definitions.

In this post, we will examine the root cause behind this error, how to identify the metadata discrepancy using a side-by-side environment comparison, and the official Oracle-supported de-installation and re-installation procedure.

Executing a standard domain index creation statement fails with the following stack:

CREATE INDEX app_owner.doc_xml_idx ON app_owner.doc_catalog(xml_data)
INDEXTYPE IS CTXSYS.PATH_SECTION_INDEX
PARAMETERS ('SECTION GROUP CTXSYS.XQUERY_SEC_GROUP');

ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-12255: invalid data type for SDATA section XQUERY_SEC_GROUPTS*
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320

Technical Root Cause Analysis

The underlying issue is tracked under Unpublished Bug 39088026 (CREATING TEXT INDEX DRG-12255: INVALID DATA TYPE FOR SDATA SECTION XQUERY_SEC_GROUPTS*).

During database upgrades (e.g., from Oracle 12c to 19c), post-upgrade dictionary scripts (dr0upgrade.sql / ctxpatch.sql) can leave system section group metadata in CTXSYS.CTX_SECTION_GROUP_SECTIONS in an invalid state.

Specifically, the dictionary metric SEC_DATATYPE expects an internal datatype code of 187. In affected instances, this value is corrupted or improperly initialized during upgrade, and the internal section names fail to inherit the fully qualified CTXSYS. package namespace prefix.

To inspect the metadata and SEC_DATATYPE values for section groups in your database, run the following query as SYSDBA:

SELECT sec_owner,
sec_section_group,
sec_name,
sec_type,
sec_datatype
FROM ctxsys.ctx_section_group_sections
WHERE sec_section_group = 'XQUERY_SEC_GROUP';


Comparing a healthy instance against an affected instance highlights the missing schema prefixes in the SEC_NAME column:

Unaffected Environment:
SEC_OWNER SEC_SECTION_GROUP SEC_TYPE SEC_NAME SEC_TAG SEC_DATA
--------- ------------------------- ------------ ------------------------------ -------------------- ------
CTXSYS XQUERY_SEC_GROUP SDATA CTXSYS.XQUERY_SEC_GROUPDB* db*
CTXSYS XQUERY_SEC_GROUP SDATA CTXSYS.XQUERY_SEC_GROUPNUM* num* NUMBER
CTXSYS XQUERY_SEC_GROUP SDATA CTXSYS.XQUERY_SEC_GROUPTS* ts*

Affected Environment:

SEC_OWNER SEC_SECTION_GROUP SEC_TYPE SEC_NAME SEC_TAG SEC_DATA
----------------- ------------------------- ------------ -------------------- -------------------- --------
CTXSYS XQUERY_SEC_GROUP SDATA XQUERY_SEC_GROUPDB* db*
CTXSYS XQUERY_SEC_GROUP SDATA XQUERY_SEC_GROUPNUM* num* NUMBER
CTXSYS XQUERY_SEC_GROUP SDATA XQUERY_SEC_GROUPTS* ts*

Notice how the affected environment lacks the CTXSYS. prefix in the SEC_NAME column, which triggers the DRG-12255 invalid data type exception when parsed.

Pre-Requisite: Verifying Oracle Text Usage

Before attempting any component-level changes, DBAs must verify whether Oracle Text domain indexes or custom preferences are actively used by application schemas.

Run the following queries as SYSDBA:

-- 1. Check for Active Oracle Text Indexes
SELECT owner, index_name, table_owner, table_name, status
FROM dba_indexes
WHERE ityp_owner = 'CTXSYS'
OR ityp_name IN ('CONTEXT', 'CTXCAT', 'CTXRULE', 'PATH_SECTION_INDEX');

-- 2. Check for Custom User Preferences

SELECT pre_owner, pre_name, pre_class, pre_object
FROM ctxsys.ctx_preferences
WHERE pre_owner NOT IN ('SYS', 'SYSTEM', 'CTXSYS');

-- 3. Summary Check One-Liner

SELECT
(SELECT COUNT(*) FROM dba_indexes WHERE ityp_owner = 'CTXSYS') AS active_text_indexes,
(SELECT COUNT(*) FROM ctxsys.ctx_preferences WHERE pre_owner NOT IN ('SYS', 'SYSTEM', 'CTXSYS')) AS custom_preferences
FROM dual;

If both counts return 0, no active text indexes or custom preferences exist on the instance.

Solution or Remediation Steps

Because Oracle Development has no ETA on when an official patch or fix will be released, modifying internal CTXSYS metadata tables directly via UPDATE or ALTER statements is strictly unsupported.

If no existing Oracle Text indexes exist on the database instance, the safest and only vendor-supported path forward is to completely de-install and reinstall the Oracle Text

(CTXSYS) component.

Step 1: De-install Oracle Text Component
Connect as SYSDBA and execute the de-installation script (catnoctx.sql):

sqlplus / as sysdba

SPOOL text_deinstall.log
-- Remove Oracle Text schema and dictionary objects
@?/ctx/admin/catnoctx.sql
-- Drop remaining validation procedure
DROP PROCEDURE sys.validate_context;
SPOOL OFF

Step 2: Reinstall Oracle Text Component

Re-create the CTXSYS schema and re-initialize system preferences:

sqlplus / as sysdba

SPOOL text_install.log
-- Install Oracle Text (Default tablespace: SYSAUX, Temp: TEMP)
@?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP LOCK
-- Change CTXSYS password and unlock account
ALTER USER CTXSYS IDENTIFIED BY "<password>" ACCOUNT UNLOCK;
-- Connect as CTXSYS to load default preferences and section groups
CONNECT CTXSYS/<password>
@?/ctx/admin/defaults/drdefus.sql
-- Connect back as SYSDBA to compile invalid objects and validate
CONNECT / as sysdba
@?/rdbms/admin/utlrp.sql
SPOOL OFF

Step 3: Verification

Verify that the CTXSYS component is installed and in VALID status across the database registry:

SELECT comp_id, comp_name, version, status
FROM dba_registry
WHERE comp_id = 'CONTEXT';

Once reinstallation is complete, re-run the CREATE INDEX DDL statement. The metadata entries will correctly include the proper namespace prefixes and datatype values, allowing the text domain index build to complete cleanly.

Thanks & Regards,

https://oracleracexpert.com

Beyond the Basics: Master Repeatable DDL in Oracle Database 23ai

The introduction of the IF [NOT] EXISTS clause in Oracle Database 23ai is more than a syntax update it’s a fundamental shift in how we approach Schema as Code.

In my recent exploration of this feature, I found that while the "Table" examples are the most common, the real power lies in how it simplifies the management of all schema objects. Let's dive deeper into some advanced examples and how they solve daily deployment hurdles.

1. Handling Supporting Objects: Indexes and Sequences

In any production environment, a table rarely exists in a vacuum. You usually have sequences for primary keys and indexes for performance. Before 23ai, if a migration script failed halfway through, you’d have to manually check which indexes were created and which weren't.

Now, you can ensure your entire environment is ready in a single, repeatable block:

-- Safely create a sequence for Employee IDs

SQL> CREATE SEQUENCE IF NOT EXISTS emp_seq START WITH 100 INCREMENT BY 1;

-- Safely create a performance index

SQL>CREATE INDEX IF NOT EXISTS idx_emp_name ON EMPLOYEE(EMP_NAME);

Why this matters: If your deployment tool (like Jenkins or GitLab CI/CD) retries a failed job, these statements won't cause the "Object already exists" error that usually stops a pipeline in its tracks.

2. Simplifying Application Logic: Synonyms

If you manage multi-tenant environments or applications that use synonyms to point to different schema versions, you know the struggle of "cleaning up" old pointers.

-- Ensure the public pointer exists without checking metadata

SQL> CREATE SYNONYM IF NOT EXISTS emp_public FOR HR_DATA.EMPLOYEE;

-- Or, if you are decommissioning a module:

SQL> DROP SYNONYM IF EXISTS old_emp_ref;

3. The "Gotcha" Deep Dive: Understanding Object vs. Attribute

As I mentioned in my recent Oracle Sprint, there is a subtle distinction to keep in mind: Object-level existence vs. Attribute-level existence.

Consider the ALTER TABLE command.

 

• The Success: ALTER TABLE IF EXISTS employee ADD (department_id NUMBER); — This works perfectly because Oracle checks if the table "employee" exists.
  
  
• The Failure: If you run that same command again, it will fail.

The Reason: Even though the table exists, the column "department_id" also now exists. The IF EXISTS clause doesn't currently look inside the table to see if the column is already there. For column-level idempotency, you still need to be strategic with your migration scripts.

4. Avoiding the Conflict: "REPLACE" vs. "IF NOT EXISTS"

This is a frequent point of confusion. You might be tempted to write: CREATE OR REPLACE VIEW IF NOT EXISTS emp_v AS SELECT...

Oracle will throw ORA-11541. * Use OR REPLACE when you want the object to be updated with new logic (common for Views, Procedures, and Functions).
Use IF NOT EXISTS when you want to ensure you don't overwrite something that is already there (common for Tables, Sequences, and Indexes).

Strategic Summary

The "Idempotent DDL" approach in 23ai removes the "fear of the second run." By incorporating these clauses into your standard SQL scripts, you:

1. Reduce Boilerplate: No more 10-line PL/SQL blocks for a 1-line DDL.
2. Increase Pipeline Uptime: Fewer "false positive" failures in CI/CD.
3. Improve Readability: Your intent is clear to any developer reading the code.

Final Thought for the Community

As we move toward more automated, AI-driven database management, these small syntax changes are what make large-scale automation possible. Are you planning to refactor your legacy migration scripts to use these new clauses, or are you saving them for new projects only?

Let's discuss in the comments!

Thanks & Regards

https://oracleracexpert.com

Oracle Agile Application or Quick Search performance issue and how to resolve?

When you come across any performance issue on agile first, you need to verify schema integrity by running agile9_check.sql.

The agile9_check.sql it verifies Database Schema version with Agile Schema version, and it MUST match. Also, it validates schema objects against Agile schema version and report any errors or warnings.

You may see ERRORS/WARNINGS
ERROR: Missing or INVALID index (columns) XXXXXXXXX on table XXXXX.
ERROR: Missing or INVALID FTS CTX indexes XXXXXXX.
WARNING: ACTIVITY_BASELINE_XXXXX does not belong to AGILE DB. Please Drop the Table if not required.

In case of any Errors/Warnings you need we need to fix the issue. If you have created any custom objects you will see WARNING that object doesn't  below to Agile DB and you can ignore. 

Once user fix the schema integrity download listFTSInfo.sql and run the script as Agile user and it will generate listFTSInfo_xxxxxxxxxx.log file. You need to search for "029", where it says "List Index Percent of Fragmentation" where you can see the indexes that has fragmentation.

If you see any fragmentation, it will effect Quick search and stuck threads will be generated.

<[STUCK] ExecuteThread: '19' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "1,247" seconds working on the request "Http Request Information: weblogic.servlet.internal.ServletRequestImpl@2f2fb94[GET /Agile/PCMServlet]
", which is more than the configured time (StuckThreadMaxTime) of "1,200" seconds in "server-failure-trigger". Stack trace:
java.net.SocketInputStream.socketRead0(Native Method)

To resolve the issue, Log into sqlplus as agile database user, and run agile_ctx_recreate.sql found under %Oracle_base%\admin\{SID}\create\{agile_schema_username} to rebuild CTX indexes.

Oracle support recommends stopping Agile application, run agile_ctx_recreate.sql and Start the Application.

It is highly advisable to run complie_Invalid_objects.sql, agile9stats.sql to recompile invalid objects and collect stats to improve the performance.

Refer Oracle Agile support doc for Full Text search (FTS) Enablement, synchronization, Indexing and optimization.

Frequently Asked Questions on Agile Product Lifecycle Management (PLM) Full Text Search (FTS) Enablement, Synchronization, Indexing, and Optimization (Doc ID 1503311.1)

For Agile Stuck thread issue, refer below Oracle support Doc ID

Agile Managed Servers Goes to Warning State, Having Stuck Thread at com.agile.cs.query.QuerySessionBean (Doc ID 2919154.1)

Thanks & Regards

https://oracleracexpert.com, Oracle ACE

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

When users performing the transactions on tables where DOMAIN,SPATIAL indexes are part of it they may receive below error and unable to proceed.

SQL> DELETE FROM USER_URL where CXT_ID=8484884;
ERROR at line 1: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

The main reason for this error is user might have copied the table/index using export/import or different method but the CREATE INDEX and failed.

Find out what caused the failure of CREATE INDEX. In many cases not having enough space on the Tablespace where you are creating the index is the culprit.

SQL> SELECT INDEX_NAME, TABLE_NAME STATUS,DOMIDX_STATUS,DOMIDX_OPSTATUS FROM DBA_INDEXES WHERE DOMIDX_OPSTATUS='FAILED';

INDEX_NAME     TABLE_NAME STATUS   DOMIDX_STATUS  DOMIDX_OPSTATUS
------------------ --------------- ---------- --------------------- -------------------------  
USER_URL_IDX  USER_URL      VALID    VALID                    FAILED


When verified I found that schema refreshed recently, and index not created successfully.

ORA-31693: Table data object "FIN"."USER_URL" failed to load/unload and is being skipped due to error:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

If you need workaround to proceed with transactions immediately, then drop the index.
The permanent fix will be drop and recreate index successfully.

To avoid this type of issues, make sure you perform following checks.

1. Before refresh verify the tablespace or file systems has enough space and add required space
2. After import check the data pump or import log file and fix all failures
3. Run utlrp.sql to recompile all invalid objects.

Thanks & Regards

https://oracleracexpert.com

Webinar: Oracle Data pump enhancements in Oracle 21c

This Webinar helps you to understand Oracle data pump new features in Oracle 21c and take an advantage of these features.

Date and time: Dec 21st 2022 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• Introduction to Oracle Data Pump
• CHECKSUM, CHECKSUM_ALGORITHM
• VERIFY_ONLY and VERIFY_CHECKSUM
• INCLUDE and EXCLUDE in the Same Operation
• Index Compression
• Transportable Tablespace Enhancements
• JSON Data Type Support
• Export/Import from Cloud Object Store
• Q&A

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com

Please use Zoom Link to join the Webinar.  

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For presentation link Click here 

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

Automatic Indexing in Oracle 19c & 21c

The DBA’s are responsible for Index management, which includes monitor index, add, change and remove based upon workload and ad-hoc manner. It will be difficult to decide type of index, when to create, change or drop and measure the impact, so there will be both positive and negative effects.

In Oracle 19c, the automatic indexing feature introduced and it automatically creates, rebuilds, and drops indexes to improve the performance based upon application workload and changes. This helps to optimize the database and improve performance without any user intervention.

The DBMS_AUTO_INDEX package is used for managing the automatic indexing and user can find out the current automatic index configuration by querying CDB_AUTO_INDEX_CONFIG view.

• CONFIGURE Procedure - Configures automatic indexing.
• DROP_AUTO_INDEXES Procedure - Drop the automatically created indexes manually by overriding the retention parameter.
• DROP_SECONDARY_INDEXES Procedure -Deletes all the indexes, except the ones used for constraints
• REPORT_ACTIVITY Function - Generate Report of the automatic indexing operations
• REPORT_LAST_ACTIVITY Function - Generate Report of the last automatic indexing operation

AUTO_INDEX_MODE : The automatic indexing is controlled using the AUTO_INDEX_MODE property, It defines different modes of operation.

• REPORT ONLY: Turn on automatic indexing and new indexes are invisible and not available for SQL operations
• IMPLEMENT: Turn on automatic indexing and new indexes are visible available for SQL operations
• OFF: Turn OFF automatic indexing but does not disable existing auto indexes.

SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');

AUTO_INDEX_SCHEMA Procedure - When automatic indexing enabled all schemas will be used for auto indexes and you can exclude or include any schemas by using AUTO_INDEX_SCHEMA parameter

The below examples adds the SALES schema to AUTO INDEXES exclusion list

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_SCHEMA',
parameter_value => 'SALES',
allow => FALSE);
end;

The below examples removes the SALES schema from AUTO INDEXES exclusion list

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_SCHEMA',
parameter_value => 'SALES',
allow => NULL);
end;

The below example removes all the schemas from AUTO INDEXES exclusion list.

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_SCHEMA',
parameter_value => NULL,
allow => TRUE);
end;

You can also define retention period that you want auto indexes. The below example sets the retention period of auto indexes to 30 days.

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_RETENTION_FOR_AUTO',
parameter_value => '30');
end;

By default automatic indexes created in default permanent tablespace and you can change this by using AUTO_INDEX_DEFAULT_TABLESPACE

SQL> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEX_TBS');

You can also allocate percentage of the tablespace can be used for auto indexes. In below example you can use 10%

SQL> exec dbms_auto_index.configure(‘AUTO_INDEX_SPACE_BUDGET’, ‘10’);


DROP_AUTO_INDEXES Procedure: Using this procedure you can Drop the automatically created indexes manually by overriding the retention parameter.

The below example will drop a single index in SALES schema and it allow recreate. If you do not want to recreate set the value to FALSE

begin

dbms_auto_index.drop_auto_indexes(

owner => ‘SALES’,

index_name => '”SYS_AI_45rfg54nxvjcty”',

allow_recreate => TRUE);

end;

/

The below example will drop all auto indexes owned by SALES and will not allow recreate as allow_recreate set to FALSE

begin

dbms_auto_index.drop_auto_indexes(

owner => 'SALES',

index_name => NULL

allow_recreate => FALSE);

end;

/

DROP_SECONDARY_INDEXES Procedure – This procedure deletes all the indexes, except the ones used for constraints

The below example deletes all auto indexes except the ones used for constraints

begin
dbms_auto_index.drop_secondary_indexes;
end;

The below example deletes all auto indexes from the REGION table in the SALES schema except the ones used for constraints

begin
dbms_auto_index.drop_secondary_indexes('SALES', 'REGION');
end;

The below example deletes all auto indexes in the SALES schema except the ones used for constraints

begin
dbms_auto_index.drop_secondary_indexes('SALES');
end;

REPORT_ACTIVITY Function – By using this function you can generate Report of the automatic indexing operations

The below example generates automatic indexing operations report executed in the last 24 hours.

declare
act_report clob := null;
begin
act_report := dbms_auto_index.report_activity();
end;

or

SQL> select dbms_auto_index.report_activity() from dual;

The below example generates HTML automatic indexing operations report executed in the last 6 hours activity.

SQL> select dbms_auto_index.report_activity(activity_start => systimestamp-0.25, activity_end => systimestamp-1, type => 'HTML') from dual;

REPORT_LAST_ACTIVITY Function -The below example generates report of the last automatic indexing executed in a database.

declare
act_report clob := null;
begin
act_report := dbms_auto_index.report_last_activity();
end;

or

SQL>  select dbms_auto_index.report_last_activity() from dual;

The Oracle 21c comes with an enhancement in Automatic indexing, which helps to reduce the over head of the cursor invalidations during automatic index creation and also added new enhancements to improve query performance.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Oracle Data pump enhancements in Oracle 21c

Oracle 21c offering new enhancements for Oracle data pump and take an advantage of these features

• CHECKSUM, CHECKSUM_ALGORITHM
• VERIFY_ONLY and VERIFY_CHECKSUM
• INCLUDE and EXCLUDE in the Same Operation
• Index Compression
• Transportable Tablespace Enhancements
• JSON Data Type Support

CHECKSUM, CHECKSUM_ALGORITHM
These parameters enables the export to perform checksum validation for each of the dump files, you can enable using CHECKSUM_ALGORITHM or CHECKSUM. In order to use these the COMPATIBLE parameter must be set to 21.0 or higher

$ expdp \'/ AS SYSDBA\' dumpfile=test.dmp schemas=testuser DIRECTORY=test_dir LOGFILE=test.log CHECKSUM=YES checksum_algorithm=SHA256

VERIFY_ONLY and VERIFY_CHECKSUM
The VERIFY_ONLY , VERIFY_CHECKSUM parameter uses the checksum to validate dump files during the import. The VERIFY_CHECKSUM and VERIFY_ONLY parameters are mutually exclusive

$ impdp \'/ AS SYSDBA\' dumpfile=test.dmp DIRECTORY=test_dir LOGFILE=test.log verify_checksum=yes

INCLUDE and EXCLUDE in the Same Operation
From Oracle 21c, the Include and exclude objects within the same export or import job

$ expdp \'/ AS SYSDBA\' dumpfile=test.dmp schemas=testuser DIRECTORY=test_dir LOGFILE=test.log include=table exclude=statistics

Index Compression
In Oracle database 21c, we can compress indexes while importing using the INDEX_COMPRESSION_CLAUSE and the TRANSFORM parameter

$ impdp \'/ AS SYSDBA\' dumpfile=test.dmp FULL=Y DIRECTORY=test_dir TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"COMPRESS BASIC\" TRANSFORM=INDEX_COMPRESSION_CLAUSE:\"COMPRESS ADVANCED LOW\" EXCLUDE=CONSTRAINT

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" FULL=Y DIRECTORY=test_dir TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"COMPRESS BASIC\" TRANSFORM=INDEX_COMPRESSION_CLAUSE:\"COMPRESS ADVANCED LOW\" EXCLUDE=CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."EMP" 188.08 KB 107 rows
. . imported "TEST"."DEPT" 90.50 KB 90 rows
. . imported "TEST"."SAL" 40.50 KB 38 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Fri Dec 10 09:22:27 2021 elapsed 0 00:00:32

Transportable Tablespace Enhancements
Before Oracle 21c any failure you cannot able to resume transportable tablespace jobs , but now Oracle Data Pump Resumes Transportable Tablespace Jobs and also Parallelizes Metadata Operations using PARALLEL parameter

$ expdp \'/ AS SYSDBA\' dumpfile=test.dmp TRANSPORT_TABLESPACES=testusr DIRECTORY=test_dir TRANSPORT_FULL_CHECK=YES LOGFILE=test.log REUSE_DUMPFILES=YES PARALLEL=2

Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" dumpfile=test.dmp TRANSPORT_TABLESPACES= testusr TRANSPORT_FULL_CHECK=YES LOGFILE=test.log REUSE_DUMPFILES=YES PARALLEL=2
ORA-39396: Warning: exporting encrypted data using transportable option without password
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX/DOMAIN_INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

JSON Data Type Support
Oracle Data Pump enables export and import of Oracle Database native JSON objects, We can also export and import of tables containing the JSON Datatype in full, tablespaces and table modes using Transportable Tablespace

Oracle 21c also offers export/import from Oracle autonomous Database and Cloud Object stores

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

Automatic Indexing in Oracle 19c using DBMS_AUTO_INDEX

Oracle 19c introduced new features automatic indexing, this feature creates and drops without dba intervention and takes care of index management

The DBMS_AUTO_INDEX package used to manage the automatic indexing feature. This feature evaluates the need for new indexes, creates when needed and drops then when they are no longer needed. It supports both single and multi-column index

Enable/Disable AUTO Index

To enable automatic indexing run below command
SQL> dbms_auto_index.configure ('AUTO_INDEX_MODE', 'IMPLEMENT');

To turn off or disable automatic indexing run below command
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');

To turn on automatic indexing, but new indexes remain invisible run below command
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');

Note: The invisible Index feature introduced in Oracle 11g, these indexes are maintained like other indexes but ignored by the optimizer unless the he OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE

For ex:- SQL> create index INDX_01 on EMP (emp_name) invisible;

Auto Index Tablespace Management

The automatic indexes by default created in the default permanent tablespace, but you can you can specify a tablespace using the AUTO_INDEX_DEFAULT_TABLESPACE property

SQL> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','TBS_AUTO_INDX');

Run below command with NULL to return using the default permanent tablespace.
SQL> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

User can specify threshold in percentage using below command. In this case only 60% of the tablespace is used for Auto Index
SQL> exec dbms_auto_index.configure ('AUTO_INDEX_SPACE_BUDGET', '60');

The autocreated indexes will be named with prefix SYS_AI. To find out these indexes there is a called “AUTO” added in DBA_INDEXES where you will see the value as “YES”

Automatic Index Management at Schema Level

You can control the SCHEMAS that’s should participate in Automatic Indexing feature by using the AUTO_INDEX_SCHEMA property.

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', ‘EMP’, allow => TRUE);

If ALLOW parameter is set to TRUE, then specified schema will be added to inclusion list and you can clear the inclusion list using below command. 

SQL>  exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);

User can manually add the SCHEMAS to the exclusion list by setting ALLOW parameter to FALSE. SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE);

User can clear the exclusion list by running below command 

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => FALSE);

Users can also use AUTO_INDEX_EXCLUDE_SCHEMA property to exclude specific schema.
SQL> exec dbms_auto_index.configure ('AUTO_INDEX_EXCLUDE_SCHEMA', ‘EMP’);

The manually created unused indexes never deleted by the automatic indexing process and they can be deleted using AUTO_INDEX_RETENTION_FOR_MANUAL. But user needs to mention after how many days the unused manual indexes can be dropped.

SQL> exec dbms_auto_index.configure ('AUTO_INDEX_RETENTION_FOR_AUTO', '50')

Views: Oracle offers several Views associated with automatic Indexing features

 
DBA_AUTO_INDEX_CONFIG - configuration settings related to automatic indexes
DBA_AUTO_INDEX_EXECUTIONS - The history of Automatic Indexing task executions
DBA_AUTO_INDEX_STATISTICS - Statistics related to automatic indexes
DBA_AUTO_INDEX_VERIFICATIONS- stats about PLAN_HASH_VALUE, AUTO_INDEX_BUFFER_GETS
DBA_AUTO_INDEX_IND_ACTIONS - Actions performed on automatic indexes
DBA_AUTO_INDEX_SQL_ACTIONS - Actions performed on SQL statements to verify automatic indexes

Thanks & Regards

http://oracleracexpert.com, Oracle ACE