Monday, July 19, 2021

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;  

Thanks & Regards
http://oracleracexpert.com






Wednesday, July 14, 2021

DBCA fails with ORA-29516: Aurora assertion failure

When creating 11g database on a new system we encounter below error…

BEGIN
*
ERROR at line 1:
ORA-29516: Aurora assertion failure: Assertion failure at joez.c:3422
Bulk load of method java/lang/Object.<init> failed; insufficient shm-object space
ORA-06512: at line 3

You should look for dbca error logs for detailed error message that you can find under /<ORACLE_BASE>/cfgtoollogs/dbca/<SID>/

We found below error from postDBCreation.log

IF CatbundleCreateDir(:catbundleLogDir) = 0 THEN
*
ERROR at line 71:
ORA-06550: line 71, column 14:
PLS-00201: identifier 'CATBUNDLECREATEDIR' must be declared
ORA-06550: line 71, column 11:
PL/SQL: Statement ignored


This error could be one of the below reasons

1. The file systems or mount point options are in correct
2. Incorrect ulimit sessions for oracle user
3. You system has JAVA_JIT_ENABLED=TRUE

In my case I have below mount options in /etc/fstab
/dev/shm tmpfs defaults,nodev,nosuid,noexec 0 0

After removing “nodev,nosuid,noexec” from /etc/fstab resolved the issue.
/dev/shm tmpfs defaults 0 0

Thanks & Regards
http://oracleracexpert.com