Oracle - How to generate script from sql developer

Question

How to take script for schema of the tables  stored procedures of Oracle through SQL Developer tool  SQLPLUS command line interface

User · Answer

This worked for me   PL SQL Developer -  Tools -  Export User Objects  Select checkboxes  Include privilege and Include storage  Select your file name  Hit export   You can later use generated export file to create table in another schema

User · Answer

The basic answer appears to be  use the dbms metadata package    The axuilliary question is      But what if I want to generate a script for all the tables at a time    And the answer  presumably  is to interrogate the system catalog for the names and owners of all the tables   SELECT dbms metadata get ddl  TABLE   s tabname  s tabowner    FROM system catalog describing tables AS s  WHERE    any conditions that are needed      I m not sufficiently familiar with Oracle to know the system catalog   In Informix  which I do know  assuming that there was a procedure dbms metadata get ddl  the query would be   SELECT dbms metadata get ddl  TABLE   s tabname  s owner    FROM  informix  systables AS s  WHERE tabid  gt   100 AND tabtype    T     In Informix  tabids less than 100 are reserved for the system catalog  and non-tables  views  synonyms  sequences and a few other esoteric things  are excluded by requiring the right  tabtype

User · Answer

In Oracle the location that contains information about all database objects including tables and stored procedures is called the Data Dictionary  It is a collection of views that provides you with access to the metadata that defines the database  You can query the Data Dictionary views for a list of desired database objects and then use the functions available in dbms metadata package to get the DDL for each object  Alternative is to investigate the support in dbms metadata to export DDLs for a collection of objects   For a few pointers  for example to get a list of tables you can use the following Data Dictionary views   user tables contains all tables owned by the user all tables contains all tables that are accessible by the user and so on

User · Answer

SQL Developer -  Tools -  Database Export

User · Answer

step 1  select   from  lt tablename gt    step 2  just right click on your output t e data  then go to last option export it will give u some extension then click on your required extension then apply u will get new file including data

User · Answer

use the dbms metadata package  as described here

User · Answer

If you want to see DDL for the objects  you can use  select dbms metadata get ddl  OBJECT TYPE   OBJECT NAME   OBJECT OWNER      from dual     For example this will give you the DDL script for emp table   select dbms metadata get ddl  TABLE   EMP   HR      from dual     You may need to set the long type format to big number  For packages  you need to access dba source  user source  all source tables  You can query for object name and type to see what code is stored

User · Answer

Oracle SQL Developer   View   DBA   Select your connection   Expand   Security   Users   Right click your user   Create like   Fill in fields   Copy SQL script   Close  If your user has object privileges  do this also  Oracle SQL Developer   View   DBA   Select your connection   Expand   Security   Users   Double click your user   Object Privs   Select all data   Right click   Export   Export as text file  Edit that text file to grant object privileges to your user

User · Answer

This worked for me    In SQL Developer  right click the object that you want to generate a script for  i e  the table name Select Quick DLL   Save To File This will then write the create statement to an external sql file    Note  you can also highlight multiple objects at the same time  so you could generate one script that contains create statements for all tables within the database

User · Answer

I did not know about DMBS METADATA  but your answers prompted me to create a utility to script all objects owned by an Oracle user

[oracle] Oracle - How to generate script from sql developer

Examples related to oracle