윤귀의 우주속 티끌

<테스트용 쿼리>

--덤프 export / import 테스트

--테이블스페이스 생성

CREATE TABLESPACE TEST_DATA_IMP

     DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\test_data_imp.dbf' SIZE 10M

     DEFAULT STORAGE

         (INITIAL    10K

          NEXT      10K

          MINEXTENTS 2

          MAXEXTENTS 50

          PCTINCREASE 50);

CREATE TEMPORARY TABLESPACE TEST_DATA_IMP_TEMP

     TEMPFILE 'D:\oracle\product\10.2.0\oradata\orcl\test_data_imp_temp.dbf' SIZE 2M;

CREATE TABLESPACE TEST_DATA_EXP

     DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\test_data_exp.dbf' SIZE 10M

     DEFAULT STORAGE

         (INITIAL    10K

          NEXT      10K

          MINEXTENTS 2

          MAXEXTENTS 50

          PCTINCREASE 50);

CREATE TEMPORARY TABLESPACE TEST_DATA_EXP_TEMP

     TEMPFILE 'D:\oracle\product\10.2.0\oradata\orcl\test_data_exp_temp.dbf' SIZE 2M;    

--유저생성

create user TEST_EXP identified by aaaa

    default tablespace TEST_DATA_EXP

    temporary tablespace TEST_DATA_EXP_TEMP

    quota unlimited on TEST_DATA_EXP;

grant connect, resource to TEST_EXP;

create user TEST_IMP identified by aaaa

    default tablespace TEST_DATA_IMP

    temporary tablespace TEST_DATA_IMP_TEMP

    quota unlimited on TEST_DATA_IMP;

grant connect, resource to TEST_IMP;

revoke unlimited tablespace from TEST_IMP;          -- 유저에게서 모든 TableSpace 권한 뺏기

--테이블 생성

create table TEST(

    id  integer

,   str VARCHAR2(10)   

);

insert into TEST values(1,'my');

insert into TEST values(2,'urs');

insert into TEST values(3,'them');

commit;

--테이블 스페이스 확인

select tablespace_name,sum(bytes),max(bytes)

from dba_free_space group by tablespace_name;