84 - [Oracle 19C] Managing tablespace using SQL developer wizard

 

 

 

 

 

CREATE SMALLFILE TABLESPACE TS2
DATAFILE '/u01/app/oracle/oradata/ORCL/PDB10/TS2_01.DBF' SIZE 104857600 AUTOEXTEND ON
DEFAULT NOCOMPRESS
ONLINE
SEGMENT SPACE MANAGEMENT AUTO
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;



CREATE SMALLFILE TABLESPACE TABLESPACE_TEST 
    DATAFILE 
        '/u01/app/oracle/oradata/ORCL/PDB10/ORCL/TABLESPACE_TEST_01.DBF' SIZE 104857600 AUTOEXTEND ON 
    DEFAULT NOCOMPRESS 
    ONLINE 
    SEGMENT SPACE MANAGEMENT AUTO 
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

 

 

 

 

 

-- Managing tablespace using SQL developer wizard

create user x1 identified by x1
default tablespace ts2;
/*
User X1 created.
*/


grant create session to x1;
/*
Grant succeeded.
*/


grant create table to x1;
/*
Grant succeeded.
*/


grant UNLIMITED tablespace to x1;
/*
Grant succeeded.
*/


create table  x1.test ( n number) ;
/*
Table X1.TEST created.
*/

 

 

 

--select * from dba_tables

select owner, table_name, tablespace_name from dba_tables
where owner='X1';
/*
OWNER      TABLE_NAME TABLESPACE_NAME
---------- ---------- ---------------
X1         TEST       TS2     
*/


DROP TABLESPACE TS2 ;
/*
Error starting at line : 43 in command -
DROP TABLESPACE TS2
Error report -
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
01549. 00000 -  "tablespace not empty, use INCLUDING CONTENTS option"
*Cause:    Tried to drop a non-empty tablespace
*Action:   To drop all the objects in the tablespace, use the INCLUDING
           CONTENTS option

*/


DROP TABLESPACE TS2 INCLUDING CONTENTS;
--including contents and datafiles
/*
TABLESPACE TS2 dropped.
*/


select * from dba_users where username='x1';
-- when you drop the table space, this mean you will drop everything inside this table space, 
-- including the users, the tables and the database objects that related to this table space.