OraFAQ Forum: Linux » text for all objects

Home » Infrastructure » Linux » text for all objects (oracle 11.2 linux)

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

text for all objects [message #540090]

Fri, 20 January 2012 04:13

nehhaverma
Messages: 34
Registered: September 2009
Location: JAIPUR

Member

Hi

I have 90 schemas over there on my database. I would like to check if any object of any schema will change on last day means tomorrow the change will be made over all the schemas.
For that I I need to use some of the data dictionaries but I am able to search which data dictionary will help me in getting the whole text of all the objects(table,view,procedure,trigger,synonym,sequence,function,grant).
I got a view dba_views in which the whole text of view is available.
Can you please let me know other Data Dictionary for all the objects.
Thanks

Regards//Neha

Report message to a moderator

Re: text for all objects [message #540095 is a reply to message #540090]

Fri, 20 January 2012 04:27

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

Senior Member
Account Moderator

You have to use about all the dictionary views.

Try to use DBMS_METADATA.GET_DDL procedure instead.

Regards
Michel

Report message to a moderator

Re: text for all objects [message #540197 is a reply to message #540090]

Fri, 20 January 2012 11:15

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Or perhaps just export the metadata using datapump or old export utility.

[Updated on: Fri, 20 January 2012 11:16] by Moderator

Report message to a moderator

Re: text for all objects [message #540394 is a reply to message #540095]

Mon, 23 January 2012 04:57

nehhaverma
Messages: 34
Registered: September 2009
Location: JAIPUR

Member

Hi

I just need to pick the sql which generates on last day then it will execute in every schema of my database.
This sql may contain modified view,changes in table,created synonym,created grant, modified procedure,modified function,modified index, add constraint etc.

Regards//Neha

Report message to a moderator

Re: text for all objects [message #540400 is a reply to message #540394]

Mon, 23 January 2012 05:21

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Neha, I'm not sure what you mean. Are you trying to capture changes made to objects, and propagate those changes to copies of the objects in other schemas?
If so, the easiest way to automate this is with Streams. You would set up a capture to record all the DDLs in your source schema as they occur, and a propagation that runs once a day to transmit them to the other schemas.
You do need an Enterprise Edition licence to use Streams.

Report message to a moderator

Re: text for all objects [message #540405 is a reply to message #540400]

Mon, 23 January 2012 05:47

nehhaverma
Messages: 34
Registered: September 2009
Location: JAIPUR

Member

Hi
Yes, this is what I want to do on my database.
Could it be possible with the help of some procedure or without using streams that I can create using dbms_metadata or something else which can get the sql and will execute on other schema.

BR//Neha

Report message to a moderator

Re: text for all objects [message #540408 is a reply to message #540405]

Mon, 23 January 2012 06:01

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

Senior Member
Account Moderator

You can use:

impdp michel/michel schemas=michel sqlfile=michel.sql network_link=<loopback dblink>

Replace "michel" by the account you want to keep trace.
All the DDL will be in the sqlfile you give.

Regards
Michel

Report message to a moderator

Re: text for all objects [message #540409 is a reply to message #540408]

Mon, 23 January 2012 06:09

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

Senior Member
Account Moderator

Also add "exclude=statistics" as statistics are irrelevant for your issue.

Regards
Michel

Report message to a moderator

Re: text for all objects [message #540412 is a reply to message #540409]

Mon, 23 January 2012 06:16

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

Senior Member
Account Moderator

And in the same way: "exclude=role_grant exclude=system_grant exclude=role exclude=tablespace_quota exclude=password_history exclude=user exclude=default_role", then you have only the schema objects.

Regards
Michel

Report message to a moderator

Re: text for all objects [message #543299 is a reply to message #540412]

Tue, 14 February 2012 01:10

nehhaverma
Messages: 34
Registered: September 2009
Location: JAIPUR

Member

Hi

Will it be possible for applying on all the users with the help of this impdb process.
I have to pick all DDL generate by a day for any user and then I have to apply these DDL for all the users on my database.

As you said to use network_link but I hav to apply this all on same server. I mean why sholud I use this network link?

Report message to a moderator

Re: text for all objects [message #543332 is a reply to message #543299]

Tue, 14 February 2012 03:25

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

Senior Member
Account Moderator

You have to create a loopback database link.

Regards
Michel

Report message to a moderator

Re: text for all objects [message #543465 is a reply to message #543332]

Tue, 14 February 2012 23:27

nehhaverma
Messages: 34
Registered: September 2009
Location: JAIPUR

Member

hi
I am using this procedure but I got an error and this will help me only in that condition in which only CCD schema gets change with some object.
expdp dbauser/dbauser network_link=REMOTE_CCD directory=test_dir dumpfile=ccd.dmp logfile=ccd.log

Export: Release 11.2.0.1.0 - Production on Tue Feb 14 22:32:23 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39200: Link name "REMOTE_CCD" is invalid.
ORA-12154: TNS:could not resolve the connect identifier specified

So how to do for getting all users modification and apply for all users?

Report message to a moderator

Re: text for all objects [message #543496 is a reply to message #543465]

Wed, 15 February 2012 01:10

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

Senior Member
Account Moderator

ORA-12154: TNS:could not resolve the connect identifier specified
 *Cause:  A connection to a database or other service was requested using
 a connect identifier, and the connect identifier specified could not
 be resolved into a connect descriptor using one of the naming methods
 configured. For example, if the type of connect identifier used was a
 net service name then the net service name could not be found in a
 naming method repository, or the repository could not be
 located or reached.
 *Action:
   - If you are using local naming (TNSNAMES.ORA file):
      - Make sure that "TNSNAMES" is listed as one of the values of the
        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA)
      - Verify that a TNSNAMES.ORA file exists and is in the proper
        directory and is accessible.
      - Check that the net service name used as the connect identifier
        exists in the TNSNAMES.ORA file.
      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
        file.  Look for unmatched parentheses or stray characters. Errors
        in a TNSNAMES.ORA file may make it unusable.
   - If you are using directory naming:
      - Verify that "LDAP" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Verify that the LDAP directory server is up and that it is
        accessible.
      - Verify that the net service name or database name used as the
        connect identifier is configured in the directory.
      - Verify that the default context being used is correct by
        specifying a fully qualified net service name or a full LDAP DN
        as the connect identifier
   - If you are using easy connect naming:
      - Verify that "EZCONNECT" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Make sure the host, port and service name specified
        are correct.
      - Try enclosing the connect identifier in quote marks.

   See the Oracle Net Services Administrators Guide or the Oracle
   operating system specific guide for more information on naming.

Regards
Michel

Report message to a moderator

Re: text for all objects [message #543558 is a reply to message #543496]

Wed, 15 February 2012 04:02

nehhaverma
Messages: 34
Registered: September 2009
Location: JAIPUR

Member

Could you tell me if there is any Oracle Data Dictionary available which shows all executed SQL for last some days.
Because as I know if table changed then by using expdp respective table will not change and expdp skip the changes on table.

So kindly tell me how to get DDL changes of last some days.

Regards//
Neha

Report message to a moderator

Re: text for all objects [message #543559 is a reply to message #543558]

Wed, 15 February 2012 04:07

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Neha, I really wonder why you don't use Streams, as I suggested earlier. It might have been designed for this sort of thing.

Report message to a moderator

Previous Topic:	ORA-03113: end-of-file on communication channel
Next Topic:	cannot start dbca in putty session

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 22 06:00:35 CST 2025