Home » Infrastructure » Linux » text for all objects (oracle 11.2 linux)
text for all objects [message #540090] Fri, 20 January 2012 04:13 Go to next message
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
Re: text for all objects [message #540095 is a reply to message #540090] Fri, 20 January 2012 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use about all the dictionary views.

Try to use DBMS_METADATA.GET_DDL procedure instead.

Regards
Michel
Re: text for all objects [message #540197 is a reply to message #540090] Fri, 20 January 2012 11:15 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: text for all objects [message #540400 is a reply to message #540394] Mon, 23 January 2012 05:21 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: text for all objects [message #540405 is a reply to message #540400] Mon, 23 January 2012 05:47 Go to previous messageGo to next message
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
Re: text for all objects [message #540408 is a reply to message #540405] Mon, 23 January 2012 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: text for all objects [message #540409 is a reply to message #540408] Mon, 23 January 2012 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also add "exclude=statistics" as statistics are irrelevant for your issue.

Regards
Michel
Re: text for all objects [message #540412 is a reply to message #540409] Mon, 23 January 2012 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: text for all objects [message #543299 is a reply to message #540412] Tue, 14 February 2012 01:10 Go to previous messageGo to next message
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?

Re: text for all objects [message #543332 is a reply to message #543299] Tue, 14 February 2012 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to create a loopback database link.

Regards
Michel
Re: text for all objects [message #543465 is a reply to message #543332] Tue, 14 February 2012 23:27 Go to previous messageGo to next message
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?
Re: text for all objects [message #543496 is a reply to message #543465] Wed, 15 February 2012 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: text for all objects [message #543558 is a reply to message #543496] Wed, 15 February 2012 04:02 Go to previous messageGo to next message
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
Re: text for all objects [message #543559 is a reply to message #543558] Wed, 15 February 2012 04:07 Go to previous message
John Watson
Messages: 8922
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.
Previous Topic: ORA-03113: end-of-file on communication channel
Next Topic: cannot start dbca in putty session
Goto Forum:
  


Current Time: Fri Mar 29 09:28:16 CDT 2024