Mega Code Archive

 

Categories / Oracle PLSQL / System Tables Views

 

Alter table emp move tablespace users and then check the result by querying user_tables

SQL> SQL> create table EMP (   2        course_id   number not null,   3        course_name varchar2(60) not null,   4        subject_id  number not null,   5        duration    number(2),   6        skill_lvl   varchar2(12) not null   7  ); Table created. SQL> SQL> select tablespace_name, table_name   2  from user_tables   3  where table_name in ('EMP', 'DEPT')   4  order by 1, 2; TABLESPACE_NAME                TABLE_NAME ------------------------------ ------------------------------ SYSTEM                         EMP SQL> SQL> SQL> select segment_name, tablespace_name   2  from user_segments   3  where segment_name = 'EMP'; SEGMENT_NAME                                                                      TABLESPACE_NAME --------------------------------------------------------------------------------- ------------------------------ EMP                                                                               SYSTEM SQL> SQL> alter table emp move   2  tablespace users; Table altered. SQL> SQL> SQL> select segment_name, tablespace_name   2  from user_segments   3  where segment_name = 'EMP'; SEGMENT_NAME                                                                      TABLESPACE_NAME --------------------------------------------------------------------------------- ------------------------------ EMP                                                                               USERS SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL> select default_tablespace, temporary_tablespace from dba_users by username select username, default_tablespace, temporary_tablespace from dba_users where default_tablespace = 'SYSTEM' or temporary_tablespace = 'SYSTEM'; alter user scott default tablespace users; select default_tablespace, temporary_tablespace from dba_users where username = 'SCOTT';