alter table emp move tablespace users and then check the result by querying user_tables : USER_TABLES « System Tables Views « Oracle PL / SQL

1. Oracle PL / SQL
2. System Tables Views
3. USER_TABLES

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';

   
  

Related examples in the same category