OraFAQ Forum: Backup & Recovery » How to Take export of Synonym

Home » RDBMS Server » Backup & Recovery » How to Take export of Synonym (IBM AIX 9.2.0.7.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to Take export of Synonym [message #323394]

Wed, 28 May 2008 06:48

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

Report message to a moderator

Re: How to Take export of Synonym [message #323399 is a reply to message #323394]

Wed, 28 May 2008 06:56

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

Report message to a moderator

Re: How to Take export of Synonym [message #323715 is a reply to message #323394]

Thu, 29 May 2008 05:40

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

Report message to a moderator

Re: How to Take export of Synonym [message #323727 is a reply to message #323715]

Thu, 29 May 2008 05:57

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to Take export of Synonym [message #323743 is a reply to message #323394]

Thu, 29 May 2008 06:19

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

Report message to a moderator

Re: How to Take export of Synonym [message #323746 is a reply to message #323743]

Thu, 29 May 2008 06:21

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Regards
Michel

Report message to a moderator

Re: How to Take export of Synonym [message #323769 is a reply to message #323394]

Thu, 29 May 2008 07:18

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....

Report message to a moderator

Re: How to Take export of Synonym [message #323775 is a reply to message #323769]

Thu, 29 May 2008 07:35

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to Take export of Synonym [message #323787 is a reply to message #323394]

Thu, 29 May 2008 08:00

pokhraj_das
Messages: 64
Registered: February 2008

Member

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

Thanx for your valuable update.

Pokhraj

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 23 00:19:21 CST 2025