CODEWITHSUNDEEP
oracle-databasesql-dropdrop-table
Grace Martina
Grace Martina

Reputation: 33

How to drop user without table in oracle

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!

Upvotes: 1

Views: 208

Answers (2)

rusty
rusty

Reputation: 671

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.

Upvotes: 1

Steven
Steven

Reputation: 1620

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.

Upvotes: 0

Related Questions