1

How can I drop user without the table in oracle?

 create user andi identified by andi; 
 alter user andi quota 10m on users; 
 grant connect to andi; 
 grant create table to andi;

 conn andi;

 create table mahasiswa( idmhs number(3) primary key, nama varchar(20),
    nim number(7), jurusan varchar(20) );

 insert into mahasiswa values (101, 'Budi', 0881103, 'TI');

 drop user andi cascade; ---> table mahasiswa is also deleted.

How can I drop user without the table in oracle? Thank you in advance!

Kara
  • 6,115
  • 16
  • 50
  • 57
Grace Martina
  • 33
  • 4

2 Answers2

1

You can not do this.

The table belongs to andi and when you drop andi you also drop all its belongings.

As mentioned before (by @Steven Wolfe) create a user for the tables, and grant privileges for the other users.

rusty
  • 641
  • 7
  • 8
0

You could create the mahasiswa table with another user as the owner and then grant the user andi whichever privileges they require (SELECT, INSERT, UPDATE, DELETE).

I'd also create a synonym so you don't have to continually reference the table from the andi schema with the owner schema, eg: OTHERUSER.mahasiswa. As the andi user execute the following:

CREATE SYNONYM mahasiswa FOR OTHERUSER.mahasiswa

Hope that helps.

Steven
  • 1,564
  • 1
  • 22
  • 34
  • i created 'CREATE SYNONYM mahasiswa FOR OTHERUSER.mahasiswa' from user andi? but it said insufficient privileges.. so i create 'grant create synonym to andi' from dba. name is already used by an existing object but i dont have any table mahasiswa on the dba user.. – Grace Martina Apr 09 '13 at 09:19
  • @Grace You should create the synonym within the schema which will access the object referenced by the synonym, or you can `CREATE PUBLIC SYNONYM` which can be visible by any user (schema). – YasirA Apr 09 '13 at 10:01