Home » RDBMS Server » Backup & Recovery » How to Take export of Synonym (IBM AIX 9.2.0.7.0)
How to Take export of Synonym [message #323394] Wed, 28 May 2008 06:48 Go to next message
pokhraj_das
Messages: 64
Registered: February 2008
Member

Hi,

Any one please let me know how to take the export of particular synonyms.

I have two sysnonyms "hr_navigation_paths","hr_navigation_paths_tl"
and schema name is TEST, which have the privilege of IMP_FULL_DATABASE and EXP_FULL_DATABASE.

Please help.

Pokhraj
Re: How to Take export of Synonym [message #323399 is a reply to message #323394] Wed, 28 May 2008 06:56 Go to previous messageGo to next message
Rajabaskar Thangaraj
Messages: 13
Registered: March 2008
Location: CHENNAI
Junior Member
hi,

you don't export synonym alone.
using dbms_metadata.get_ddl package or third party tool get the create synonym syntax.

Regards
Raja baskar
Re: How to Take export of Synonym [message #323715 is a reply to message #323394] Thu, 29 May 2008 05:40 Go to previous messageGo to next message
pokhraj_das
Messages: 64
Registered: February 2008
Member

Hi,

I am not able to use the package dbms_metadata.get_ddl('TABLESPACE','<TABLESPACE_NAME>') at the procedure.

When I am using the above package Oracle giving the following error:-

select dbms_metadata.get_ddl('TABLESPACE','ERWORK') from dual;
*

ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

Is there is any way to use the package at the procedure from where I can CALL the package.

Please suggest...

Pokhraj

Re: How to Take export of Synonym [message #323727 is a reply to message #323715] Thu, 29 May 2008 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has to be used in this way at SQL prompt not inside a PL/SQL block.

ALWAYS copy and paste your session.
Also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: How to Take export of Synonym [message #323743 is a reply to message #323394] Thu, 29 May 2008 06:19 Go to previous messageGo to next message
pokhraj_das
Messages: 64
Registered: February 2008
Member

Hi,

Is there is any way to write any query into that package..
for example

select dbms_metadata.get_ddl('TABLESPACE','select tablespace_name from dba_tablespaces') from dual;

I am not sure.. As At my production database I have 153 tablespaces. For all the tablespaces I need the tablespace structure.


please help.

Pokhraj
Re: How to Take export of Synonym [message #323746 is a reply to message #323743] Thu, 29 May 2008 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

Regards
Michel
Re: How to Take export of Synonym [message #323769 is a reply to message #323394] Thu, 29 May 2008 07:18 Go to previous messageGo to next message
pokhraj_das
Messages: 64
Registered: February 2008
Member

thanx for the solution.
It really helped me a lot.

I have one confusion. At my production database I have around 250 procedures. I want to move all the procedures at the TEST server for testing perpose.

Is there is any way I can transfer all the procedures to TEST server?

Please help....
Re: How to Take export of Synonym [message #323775 is a reply to message #323769] Thu, 29 May 2008 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The same way.

select dbms_metadata.get_ddl(object_type,object_name,owner)
from dba_objects
where object_type in ('PROCEDURE','FUNCTION','PACKAGE')
and owner in (<your user list>)
order by owner, object_name, object_type
/

Regards
Michel
Re: How to Take export of Synonym [message #323787 is a reply to message #323394] Thu, 29 May 2008 08:00 Go to previous message
pokhraj_das
Messages: 64
Registered: February 2008
Member

Great .. Its really helped me a lot....

Thanx for your valuable update.

Pokhraj
Previous Topic: Dynamic Performance tables and views
Next Topic: recover missing datafile
Goto Forum:
  


Current Time: Tue Dec 06 02:30:58 CST 2016

Total time taken to generate the page: 0.08213 seconds