How to take script for schema of the tables, stored procedures of Oracle through SQL Developer tool (SQLPLUS command line interface)?
This question is related to
oracle
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.
step 1. select * from <tablename>;
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.
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.
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.
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 >= 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'.
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.
use the dbms_metadata package, as described here
This worked for me:
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.
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 userall_tables
contains all tables that are accessible by the userSource: Stackoverflow.com