This discussion is archived
12 Replies Latest reply: Oct 10, 2013 11:22 AM by jgarry RSS

Is there a way to export user created objects which is in system schema?

karthiksingh_dba Explorer
Currently Being Moderated

Dear Legends,

 

I require your help that is there a way to export user created objects in SYSTEM schema ? Sorry I hope the question is little stupid, but unfortunately one of our user created all their objects in SYSTEM schema.

 

Oracle 11g XE Windows Environment

1. We are trying to identify the objects created by SYSTEM user.

2. Verifying the count and the object names with tables, procedures, functions, triggers and sequences.

 

Thanks,

Karthik

  • 1. Re: Is there a way to export user created objects which is in system schema?
    sb92075 Guru
    Currently Being Moderated

    karthiksingh_dba wrote:

     

    Dear Legends,

     

    I require your help that is there a way to export user created objects in SYSTEM schema ? Sorry I hope the question is little stupid, but unfortunately one of our user created all their objects in SYSTEM schema.

     

    Oracle 11g XE Windows Environment

    1. We are trying to identify the objects created by SYSTEM user.

    2. Verifying the count and the object names with tables, procedures, functions, triggers and sequences.

     

    Thanks,

    Karthik

     

    query ALL_OBJECTS for recent creation date & owned by SYSTEM.

     

     

    Since duhveloper created object, then should have same DDL to make again,  again in different schema.

    Is this a Professional organization which maintains source code in code repository?

  • 2. Re: Is there a way to export user created objects which is in system schema?
    karthiksingh_dba Explorer
    Currently Being Moderated

    Hi,

     

    Thanks for your response.

    >>query ALL_OBJECTS for recent creation date & owned by SYSTEM.

    Works like Charm. I queried select count(*) from all_objects where owner='SYSTEM' and object_type='TABLE' and created > '16-02-13';

     

     

    >>Is this a Professional organization which maintains source code in code repository?

    Yes. I have instructed the client Not to use SYSTEM schema, but they are not aware of the necessity Sorry. So now I'm again forcefully instructing them to create a Separate schema(MGR).

     

    Question:

    1. Regarding Index the export sql contains the "SYSTEM" as its default tablespace. So now which tablespace would be best preferred?

     

    Thanks,

    Karthik

  • 3. Re: Is there a way to export user created objects which is in system schema?
    Pradeepcmst Journeyer
    Currently Being Moderated

    Hi Karthik,

    Use all_objects to pull out the user objects ,.. and then you can use datapump to export the objects and import in another new user schema..

     

     

    Regards,

    Pradeep. V

  • 4. Re: Is there a way to export user created objects which is in system schema?
    sb92075 Guru
    Currently Being Moderated

    karthiksingh_dba wrote:

     

    Hi,

     

    Thanks for your response.

    >>query ALL_OBJECTS for recent creation date & owned by SYSTEM.

    Works like Charm. I queried select count(*) from all_objects where owner='SYSTEM' and object_type='TABLE' and created > '16-02-13';

     

     

    >>Is this a Professional organization which maintains source code in code repository?

    Yes. I have instructed the client Not to use SYSTEM schema, but they are not aware of the necessity Sorry. So now I'm again forcefully instructing them to create a Separate schema(MGR).

     

    Question:

    1. Regarding Index the export sql contains the "SYSTEM" as its default tablespace. So now which tablespace would be best preferred?

     

    Thanks,

    Karthik

     

    SYSTEM  schema is different from SYSTEM  tablespace.

    why does any user (other than the DBA) have any access to the  SYSTEM  schema/password?

     

    why does user have quota on  SYSTEM  tablespace to ever deposit any object in this tablespace?

    Lack oversight & too many privileges resulted in this situation

  • 5. Re: Is there a way to export user created objects which is in system schema?
    karthiksingh_dba Explorer
    Currently Being Moderated

    sb92075 wrote:

     

    SYSTEM  schema is different from SYSTEM  tablespace.

    why does any user (other than the DBA) have any access to the  SYSTEM  schema/password?

     

    why does user have quota on  SYSTEM  tablespace to ever deposit any object in this tablespace?

    Lack oversight & too many privileges resulted in this situation

    Sorry our Client themselves executed without the Knowledge of DBA.

  • 6. Re: Is there a way to export user created objects which is in system schema?
    sb92075 Guru
    Currently Being Moderated

    karthiksingh_dba wrote:

     

    sb92075 wrote:

     

    SYSTEM  schema is different from SYSTEM  tablespace.

    why does any user (other than the DBA) have any access to the  SYSTEM  schema/password?

     

    why does user have quota on  SYSTEM  tablespace to ever deposit any object in this tablespace?

    Lack oversight & too many privileges resulted in this situation

    Sorry our Client themselves executed without the Knowledge of DBA.

     

    EVERYTHING  in Oracle is forbidden; except that which is explicitly GRANTED.

    if user is GRANTEd privilege (or ROLE) that can cause a problem, who is ultimately responsible for the content of the database?

    If you don't want folks to cause a problem, then do not issue GRANT that can cause a problem..

  • 7. Re: Is there a way to export user created objects which is in system schema?
    Mark D Powell Guru
    Currently Being Moderated

    Yes, you can export specific tables or use dbms_metadata to generate the DDL for the object in question and move the objects and data manually.  Once you allocate the table under the new owner you can then use insert/select to copy the data, run the index DDL, contraint DDL, trigger DDL to complete the necessary changes.  Once have the table moved you drop the origional.

     

    What works best may depend on how many objects you have to deal with.

     

    HTH -- Mark D Powell --


  • 8. Re: Is there a way to export user created objects which is in system schema?
    TSharma-Oracle Guru
    Currently Being Moderated

    If you have few tables inside system schema , you can just simply use export dump utility "expdp" to take an export at schema level. This will export few system(not all) objects along will all your "user created objects" and you can use remap_schema to dump all the tables into different schema. For example:

    expdp username directory=<dir_name> filename=exp.dmp logfile=log1.log schemas=system

     

    For packages, triggers and procedures you can use dbms_metadata or some third party tool like TOAD to pull all  the definitions of those objects.

  • 9. Re: Is there a way to export user created objects which is in system schema?
    karthiksingh_dba Explorer
    Currently Being Moderated

    Thanks All.

     

    We have solved the issue by exporting the objects which is created on or after the above said date and I forcefully recommended our users to use a separate schema.

    We always would like to follow the standards and best practices, but some times it happens like this.

     

    Once again Thanks to all.

    Regards,

    Karthik

  • 10. Re: Is there a way to export user created objects which is in system schema?
    EdStevens Guru
    Currently Being Moderated

    karthiksingh_dba wrote:

     

    Thanks All.

     

    We have solved the issue by exporting the objects which is created on or after the above said date and I forcefully recommended our users to use a separate schema.

    We always would like to follow the standards and best practices, but some times it happens like this.

     

    Once again Thanks to all.

    Regards,

    Karthik

    There is no "sometimes" that the dba hasn't allowed.  Don't give them quota in the system tablespace.  Don't give them any CREATE ANY ... privileges.  Don't give them the password to accounts they have no business using.  It's not a matter of 'trust but verify', it's a matter of locking the doors.  You could absolutely, 100% prevent it in about two minutes.

  • 11. Re: Is there a way to export user created objects which is in system schema?
    karthiksingh_dba Explorer
    Currently Being Moderated

    Yes I accept and I will follow the above said valuable points. Thanks Steve.

  • 12. Re: Is there a way to export user created objects which is in system schema?
    jgarry Guru
    Currently Being Moderated

    It's XE... "free to develop, deploy, and distribute; fast to download; and simple to administer. "

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points