4 Replies Latest reply on Sep 2, 2015 1:54 PM by 1977008

    how to delete a reporting schema

    1977008

      how do you get rid of a reporting schema in sql developer. i accidentally created on under a user that i cant drop. The user now has all the DMRS, DMRV etc. tables and views.. how can i get rid of them without dropping the user?

        • 1. Re: how to delete a reporting schema
          BPeaslandDBA

          If you don't want to drop the user, only its objects, then you'll have to script it out. There is no quick and easy, singular command. However, SQL Developer makes it easy. In SQL Dev, run:

           

          select 'drop '||object_type||' '||owner||'.'||object_name||';'

          from dba_objects where owner='MY_USER';

           

          Press F5 to run the SQL statement. In the output pane at the bottom, put your cursor in the results and press CTL-F5. This will take all of those DROP commands and put then in a new worksheet. Then press F5 to run that.

           

          HTH,

          Brian

          1 person found this helpful
          • 2. Re: how to delete a reporting schema
            David Last-Oracle

            You can do this by selecting Export > To Reporting Schema again.  The dialog has a Maintenance tab.

            Select your connection and then select Drop Repository on the Maintenance tab.

             

            David

            1 person found this helpful
            • 3. Re: how to delete a reporting schema
              1977008

              Thanks a million David... it worked.. BUT there is a glitch with it.. when it asks do you want to delete repository and you select the OK button, it processes and comes back with a window stating Repository has been deleted and it has an OK button as well.  if you hit the OK button, for some reason  it   recreates the repository again.  BUT if you just select the X in the top right of the window and close it.. the repository does not get recreated.  Thanks so much for the response.. i googled the living daylights out of this and couldn't find it so i came here...   :-D

              1 person found this helpful
              • 4. Re: how to delete a reporting schema
                1977008

                Thanks for the response BP.. Dave had an easier method. take a look at his..