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
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.
