3 Replies Latest reply on Dec 8, 2018 2:32 AM by Bozzy-Oracle

    delete (contents of) a schema

    Marc C
    Marc C Dec 6, 2018 6:19 PM

      How do I delete the contents of a schema (tables, views etc) or how do I delete the schema itself in APEX (18.2) ?

      I can't find any functionality for that.

      • 116 Views
      • Tags:
        • 1. Re: delete (contents of) a schema
          Pierre Yotti
          Pierre Yotti Dec 6, 2018 6:22 PM (in response to Marc C)

          take a look here

          https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9008.htm

           

          But you should have a Privileges to do that.

          • 2. Re: delete (contents of) a schema
            Marc C
            Marc C Dec 7, 2018 1:53 AM (in response to Pierre Yotti)

            Thanks for your reply.

            I tried that, but no privilege for that. I assume that I can do that when I am logged in as PDB_ADMIN, but I can't login as that user. Neither after resetting its password.
            No idea why I cannot login like PDB_ADMIN

            I can run a generated script simply having 1 delete-line per object via 'SQL Workshop/SQL scripts' or I could do it line by line manually via 'SQL Workshop/SQL commands'.

            Still, I am a bit puzzled why there isn't a button for that, much like deleting an application or deleting a workspace.

            (this answer has been edited)

            • 3. Re: delete (contents of) a schema
              Bozzy-Oracle
              Bozzy-Oracle Dec 8, 2018 2:32 AM (in response to Marc C)

              If you can log into sqlplus at the O/S prompt as the Oracle software owning account (normally oracle on UNIX/Linux) or an account in the UNIX dba group, it's a case of doing the following:

               

              If you are on Linux or UNIX and don't know your database SID, you can try determinite it by doing a

              cat /etc/oratab

              The database SID is the 1st field and the fields are colon (:) delimited. If there is more than database entry and you are not sure which database is yours, I would advise you not to proceed.

               

              Now assuming your Linux login account is in the dba group (if you know the oracle account password and logged in as oracle you should be ok) proceed as below.

               

              You need to ensure you enter the database SID of your database when prompted by oraenv (below). Ensure you enter it in the same  case.

              $ . oraenv

              ORACLE_SID = [oracle] ? YOURDBSID

               

               

              $ sqlplus / as sysdba

              SQL> drop user username cascade;

              ...

              SQL> exit

               

              So YOURDBSID should match the database

               

              Thanks,

               

              Clive