Moving Tables and using indexes

Tabloların taşınılması işlemleri

Create a tablespace

SQL> create tablespace test_tbs datafile '/u01/app/oracle/oradata/datafile/test01.dbf' size 15M  autoextend on next 1M;

Tablespace created.

Create second tablespace

SQL> create tablespace test_tbs2 datafile '/u01/app/oracle/oradata/datafile/test02.dbf' size 15M  autoextend on next 1M;

Tablespace created.

Create a user 

SQL> create user veysi identified by veysi;

User created.

Grant some roles to user

SQL> grant resource,connect to veysi;

Grant succeeded.

Set a default tablespace to user..

SQL> alter user test_usr default tablespace test_tbs   temporary tablespace temp;

User altered.

SQL> conn veysi/veysi

Connected.

Create table

SQL> create table test_table( t_id number, t_name varchar(30), hire_date  date );

Table created.

Creating Sequence.

SQL> create sequence id_seq

minvalue 1

maxvalue 100

 increment by 1

start with 1

nocycle ;

sequence created.

SQL> insert into test_table

  2  values(id_seq.nextval,'Veysi',sysdate-30);

1 row created.

SQL> insert into test_table

  2  values(id_seq.nextval,'Ali',sysdate-60);

1 row created.

SQL> insert into test_table

  2  values(id_seq.nextval,'Mustafa',sysdate-90);

1 row created.

SQL> select * from test_table;

      T_ID T_NAME                         HIRE_DATE

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

         2 Veysi                         20/01/2012

         3 Ali                            21/12/2011

         4 Mustafa                        21/11/2011

Create an other child table.

SQL> create table test_child_table(id number,month varchar(20),salary number);

Table created.

Set the foreign to parent table.

SQL> alter table test_child_table add constraint fk_id_test_ch_tbl foreign key(id) references test_table(t_id);

Table altered.

Then you can create a index . 

SQL> create bitmap index idx_id_child_tbl  on test_child_table (id);

Index created.

new insert

SQL> insert into test_child_table values(2,'JAN',2000);

1 row created.

Looking our table

SQL> select table_name,tablespace_name from  user_tables;

TABLE_NAME                     TABLESPACE_NAME

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

TEST_TABLE                            TEST_TBS

TEST_CHILD_TABLE               TEST_TBS

Looking a USER'S objects

SQL> select object_name,OBJECT_TYPE from all_objects where 

owner='VEYSI';

OBJECT_NAME                    OBJECT_TYPE

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

ID_MATIC                       SEQUENCE

TEST_TABLE                     TABLE

PK_T_ID_TEST_TBL               INDEX

TEST_CHILD_TABLE               TABLE

Now move table to the other tablespace

SQL> alter table test_child_table move tablespace test_tbs2;

Table altered.

As you se The Test_child_table now on Test_tbs2.

SQL> select table_name,tablespace_name from  user_tables;

TABLE_NAME                           TABLESPACE_NAME

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

TEST_TABLE                          TEST_TBS

TEST_CHILD_TABLE                          TEST_TBS2

The table's index is now "unusable" because of i change the tablespaces.

SQL> select index_name,status from user_indexes where table_name='TEST_CHILD_TABLE';

INDEX_NAME                     STATUS

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

IDX_ID_CHILD_TBL               UNUSABLE

Now i need rebuild the index for valided it by using a simple command.

SQL> alter index IDX_ID_CHILD_TBL  rebuild tablespace test_tbs2 ;

Index altered.

SQL> select index_name,status from user_indexes where table_name='TEST_CHILD_TABLE';

INDEX_NAME                     STATUS

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

IDX_ID_CHILD_TBL               VALID

lets look at the constraints

SQL> insert into test_child_table values(3,'FEB',4000);

1 row created.

SQL> select * from test_child_table;

        ID MONTH                    SALARY

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

         2 FEB                        4000

         3 FEB                        4000

Looking the parent table

SQL> select * from test_table;

      T_ID T_NAME                         HIRE_DATE

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

         2 Veysi                           20/01/2012

         3 Ali                          21/12/2011

         4 Mustafa                         21/11/2011

SQL> insert into test_child_table values(1,'FEB',4000);

insert into test_child_table values(1,'FEB',4000)

*

ERROR at line 1:

ORA-02291:  integrity constraint (string.string) violated  (TEST_USR.FK_ID_TEST_CH_TBL)- parent key not found  

 So,as you see i got error because a foreign key value has no matching primary key value

To correct this problem, you need to insert the value into the parent table first and then you can insert the corresponding value into the child table.