Workshop II 남은 부분 수업

SYS> conn hr/hr

Connected.

HR> create table emp as select * from employees;

Table created.

HR> create index emp_id_no on emp(employee_id);

Index created.

HR> insert into emp select * from emp;

20 rows created.

HR> /

40 rows created.

HR> /

80 rows created.

HR> /

160 rows created.

HR> /

320 rows created.

HR> /

640 rows created.

HR> /

1280 rows created.

HR> /

2560 rows created.

HR> /

5120 rows created.

HR> /

10240 rows created.

HR> /

20480 rows created.

HR> /

40960 rows created.

HR> commit;

Commit complete.

HR> select sum(blocks) from user_segments where segment_name = 'EMP';

SUM(BLOCKS)

-----------

        896

HR> delete from emp;

81920 rows deleted.

HR> insert into emp select * from employees;

20 rows created.

HR> commit;

Commit complete.

HR> alter table emp shrink space check;

alter table emp shrink space check

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled

HR> select table_name, row_movement from user_tables where table_name = 'EMP';

TABLE_NAME                     ROW_MOVE

------------------------------ --------

EMP                            DISABLED

HR> alter table emp enable row movement;

Table altered.

HR> select table_name, row_movement from user_tables where table_name = 'EMP';

TABLE_NAME                     ROW_MOVE

------------------------------ --------

EMP                            ENABLED

HR> alter table emp shrink space check;

alter table emp shrink space check

*

ERROR at line 1:

ORA-10655: Segment can be shrunk

HR> alter table emp shrink space compact;

Table altered.

HR> alter table emp shrink space;

Table altered.

HR> select sum(blocks) from user_segments where segment_name = 'EMP';

SUM(BLOCKS)

-----------

          8

HR> create table rp_emp

  2  partition by range(department_id) (

  3  partition p_10 values less than (30) tablespace new_users,

  4  partition p_30 values less than (60) tablespace users,

  5  partition p_50 values less than (90) tablespace new_users,

  6  partition p_70 values less than (maxvalue) tablespace users)

  7  as        

  8  select * from employees;

Table created.

HR> insert into rp_emp   

  2  select * from rp_emp;

20 rows created.

HR> /

40 rows created.

HR> /

80 rows created.

HR> /

160 rows created.

HR> commit;

Commit complete.

HR> create table hp_emp

  2  partition by hash(employee_id) partitions 4

  3  store in (users, new_users)

  4  as select * from employees;

Table created.

HR> insert into hp_emp

  2  select * from hp_emp;

20 rows created.

HR> /

40 rows created.

HR> /

80 rows created.

HR> /

160 rows created.

HR> /

320 rows created.

HR> commit;

Commit complete.

HR> select table_name, partition_name from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

RP_EMP                         P_70

RP_EMP                         P_50

RP_EMP                         P_30

RP_EMP                         P_10

HP_EMP                         SYS_P23

HP_EMP                         SYS_P24

HP_EMP                         SYS_P22

HP_EMP                         SYS_P21

8 rows selected.

HR> select count(*) from rp_emp partition (p_10);

  COUNT(*)

----------

        48

HR> select count(*) from rp_emp partition (p_30);

  COUNT(*)

----------

        80

HR> select count(*) from rp_emp partition (p_50);

  COUNT(*)

----------

        96

HR> select count(*) from rp_emp partition (p_70);

  COUNT(*)

----------

        96

HR> select count(*) from rp_emp partition (sys_p21);

select count(*) from rp_emp partition (sys_p21)

                                       *

ERROR at line 1:

ORA-02149: Specified partition does not exist

HR> select count(*) from hp_emp partition (sys_p21);

  COUNT(*)

----------

        96

HR> select count(*) from hp_emp partition (sys_p22);

  COUNT(*)

----------

       128

HR> select count(*) from hp_emp partition (sys_p23);

  COUNT(*)

----------

       256

HR> select count(*) from hp_emp partition (sys_p24);

  COUNT(*)

----------

       160

HR> conn / as sysdba 

Connected.

SYS> select tp.endian_format

  2  from v$transportable_platform tp, v$database d

  3  where tp.platform_name = d.platform_name;

ENDIAN_FORMAT

--------------

Little

Cluster Table 실습

HR> conn hr/hr

Connected.

HR> create cluster emp_dept_clu(department_id number(4))

  2  tablespace users;

Cluster created.

HR> create index emp_dept_clu_ind

  2  on cluster emp_dept_clu

  3  tablespace users;

Index created.

HR> create table dept_clu cluster emp_dept_clu(department_id)

  2  as select * from departments;

Table created.

HR> create table emp_clu cluster emp_dept_clu(department_id)    

  2  as select * from departments;

Table created.

HR> drop table emp_clu;

Table dropped.

HR> create table emp_clu cluster emp_dept_clu(department_id)

  2  as select * from employees;

Table created.

HR> select rowid, department_id from dept_clu

  2  where department_id = 50;

ROWID              DEPARTMENT_ID

------------------ -------------

AAAETCAAEAAAADPAAA            50

HR> select rowid, employee_id, department_id from emp_clu

  2  where department_id = 50;

ROWID              EMPLOYEE_ID DEPARTMENT_ID

------------------ ----------- -------------

AAAETCAAEAAAADPAAA         124            50

AAAETCAAEAAAADPAAB         141            50

AAAETCAAEAAAADPAAC         142            50

AAAETCAAEAAAADPAAD         143            50

AAAETCAAEAAAADPAAE         144            50