Category Archives: OBIA

Generate DDL script for all tables of a schema in SQL Developer

Hello Friends,

I am back after a long vacay !! and thought to add some pointers to my blog. I have searched a lot about this topic and found least information. So here is step by step process to Generate DDL scripts for all tables in a schema in SQL developer, without much work.

If you want only DDL scripts together in 1 view, you can use below query to run in SQL developer schema, where you want output:

SELECT DBMS_METADATA.GET_DDL(‘TABLE’, TABLE_NAME) FROM USER_TABLES;

Next way to get separate DDL scripts is to :

  1. Go to FILE -> DATA MODELLER -> EXPORT -> DDL FILE
  2. New pop up window appear
  3. i1Click on Generate button
  4. New pop window appears
  5. Now click on “Generate DDL scripts in Separate Files”, on screen at bottom right.i2
  6. Now go to tab “Include TABLE DDL scripts
  7. Select table you want to have DDL scripts
  8. click ok. It will ask for location, where to store. Just set all this and you are good to go.

 

Happy Working 🙂

 

 

 

 

ETL ODI Error |ORA-01792: MAXIMUM NUMBER OF COLUMNS IN A TABLE OR VIEW IS 1000

Are you seeing the error “ORA-01792: MAXIMUM NUMBER OF COLUMNS IN A TABLE OR VIEW IS 1000” when the original query does not select as many columns.

Cause
——–

The problem matches Unpublished bugs as per the Oracle documents:

Bug 19653859 – CI BACKPORT OF BUG 19509982 FOR INCLUSION IN DATABASE BP 12.1.0.2.2
Bug 19509982 – DISABLE FIX FOR RAISING ORA-1792 BY DEFAULT

Solutions:

  1. Oracle suggests to alter the query and/or view definitions to avoid the error.
  2. However in cases where the SQL cannot be adjusted then the checking can be disabled by:
    SQL> alter system set “_fix_control”=’17376322:OFF’;
  3. Alternatively, an interim patch(patch 19509982) can be applied to disable the error by default.

References:

•Bug 19509982 Disable raising of ORA-1792 by default

You can click here to read more

 

 

Examples:

SQL> alter session set “_fix_control”=’17376322:OFF’;

or at system level :

SQL> alter system set “_fix_control”=’17376322:OFF’;

OR

Apply Patch 19509982 if available for your DBVersion and Platform

To download the patch, please follow the steps below:
1) Go To MOS
2) Click on Patches & Updates
3) In the Patch Search Section, enter your patch number <19509982> in the Patch Name or Number field.
4) Click Search
5) Click the patch that meets your DbVersion and platform
6) Read any applicable notes before downloading, then click the download button.

 

Information courtesy : Google/Oracle Docs
%d bloggers like this: