Ghost's Oracle Database ! :: 'Oracle 10g/10g - 실습' 카테고리의 글 목록 (2 Page)

일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31

Cold Backup & Hot Backup

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 8. 11:05

Cold Backup

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 8 10:28:54 2010

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> col name format a50

SQL> select name from v$datafile;

NAME

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

/home/oracle/oradata/testdb/system01.dbf

/home/oracle/oradata/testdb/undotbs01.dbf

/home/oracle/oradata/testdb/sysaux01.dbf

/home/oracle/oradata/testdb/users01.dbf

/home/oracle/oradata/testdb/example01.dbf

SQL> select name from v$controlfile;

NAME

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

/home/oracle/oradata/testdb/control01.ctl

/home/oracle/oradata/testdb/control02.ctl

/home/oracle/oradata/testdb/control03.ctl

SQL> select member from v$logfile;

MEMBER

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

/home/oracle/oradata/testdb/redo03.log

/home/oracle/oradata/testdb/redo02.log

/home/oracle/oradata/testdb/redo01.log

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !cp -av /home/oracle/oradata/testdb/*.dbf /data/backup/close/

`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/close/example01.dbf'

`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/close/sysaux01.dbf'

`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/close/system01.dbf'

`/home/oracle/oradata/testdb/temp01.dbf' -> `/data/backup/close/temp01.dbf'

`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/close/undotbs01.dbf'

`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/close/users01.dbf'

SQL> !cp -av /home/oracle/oradata/testdb/*.ctl /data/backup/close/

`/home/oracle/oradata/testdb/control01.ctl' -> `/data/backup/close/control01.ctl'

`/home/oracle/oradata/testdb/control02.ctl' -> `/data/backup/close/control02.ctl'

`/home/oracle/oradata/testdb/control03.ctl' -> `/data/backup/close/control03.ctl'

SQL> !cp -av /home/oracle/oradata/testdb/*.log /data/backup/close/

`/home/oracle/oradata/testdb/redo01.log' -> `/data/backup/close/redo01.log'

`/home/oracle/oradata/testdb/redo02.log' -> `/data/backup/close/redo02.log'

`/home/oracle/oradata/testdb/redo03.log' -> `/data/backup/close/redo03.log'

SQL> !cp -av $ORACLE_HOME/dbs /data/backup/close/

`/home/oracle/product/10g/dbs' -> `/data/backup/close/dbs'

`/home/oracle/product/10g/dbs/orapwtestdb' -> `/data/backup/close/dbs/orapwtestdb'

`/home/oracle/product/10g/dbs/init.ora' -> `/data/backup/close/dbs/init.ora'

`/home/oracle/product/10g/dbs/initdw.ora' -> `/data/backup/close/dbs/initdw.ora'

`/home/oracle/product/10g/dbs/spfiletestdb.ora' -> `/data/backup/close/dbs/spfiletestdb.ora'

`/home/oracle/product/10g/dbs/lkTESTDB' -> `/data/backup/close/dbs/lkTESTDB'

`/home/oracle/product/10g/dbs/hc_testdb.dat' -> `/data/backup/close/dbs/hc_testdb.dat'

SQL>

Hot Backup

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 100664912 bytes

Database Buffers 180355072 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> col tablespace_name format a10

SQL> col file_name format a50

SQL> select tablespace_name, status, contents from dba_tablespaces;

TABLESPACE STATUS CONTENTS

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

SYSTEM ONLINE PERMANENT

UNDOTBS1 ONLINE UNDO

SYSAUX ONLINE PERMANENT

TEMP ONLINE TEMPORARY

USERS ONLINE PERMANENT

EXAMPLE ONLINE PERMANENT

6 rows selected.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE MB FILE_NAME

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

USERS 5 /home/oracle/oradata/testdb/users01.dbf

SYSAUX 240 /home/oracle/oradata/testdb/sysaux01.dbf

UNDOTBS1 35 /home/oracle/oradata/testdb/undotbs01.dbf

SYSTEM 480 /home/oracle/oradata/testdb/system01.dbf

EXAMPLE 100 /home/oracle/oradata/testdb/example01.dbf

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/system01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/open/system01.dbf'

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/sysaux01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/open/sysaux01.dbf'

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/undotbs01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/open/undotbs01.dbf'

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/users01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/open/users01.dbf'

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> alter tablespace example begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/example01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/open/example01.dbf'

SQL> alter tablespace example end backup;

Tablespace altered.

SQL> alter database backup controlfile to '/data/backup/open/control01.ctl';

Database altered.

SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time

2 from v$datafile a, v$backup b

3 where a.file# = b.file#;

FILE# NAME STATUS TIME

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

1 /home/oracle/oradata/testdb/system01.dbf NOT ACTIVE 2010-03-08:11:10:04

2 /home/oracle/oradata/testdb/undotbs01.dbf NOT ACTIVE 2010-03-08:11:11:07

3 /home/oracle/oradata/testdb/sysaux01.dbf NOT ACTIVE 2010-03-08:11:10:38

4 /home/oracle/oradata/testdb/users01.dbf NOT ACTIVE 2010-03-08:11:11:53

5 /home/oracle/oradata/testdb/example01.dbf NOT ACTIVE 2010-03-08:11:12:16

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Archive log mode Complete Recovery (0)	2010.03.09
No Archive log mode Recovery (0)	2010.03.08
Archive log mode 설정 및 활성화/비활성화 (0)	2010.03.05
Privileges(권한) 관리 (0)	2010.03.05
Profile(프로파일) 생성과 관리 (0)	2010.03.05

Archive log mode 설정 및 활성화/비활성화

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 5. 12:32

■ Archive log mode 사용을 위한 설정

[oracle@ghost sql]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 12:10:55 2010

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

Database mounted.

#Archive log의 기본 저장 영역은 $ORACLE_BASE/flash_recovery_area 이며, 사용 가능한 기본

#용량은 2GB 이다.

#flash_recovery_area는 archive log 뿐만 아니라 flashback log, rman 등 Backup & Recovery

#관련 데이터가 저장되어 Archive log mode를 활성화하여 사용할 경우 디스크 용량 부족으로 인해

#Database에 문제가 발생될 수 있다.

#디스크 용량 부족으로 인한 문제를 방지하기 위해 아래와 같이 Archive log가 저장될 저장 장소를

#별도로 지정한다.

#spfile 사용시

#log_archive_dest_1 디렉토리 지정

SQL> alter system set log_archive_dest_1='location=/data/arc1' scope=spfile;

System altered.

#log_archive_dest_2 디렉토리 지정

SQL> alter system set log_archive_dest_2='location=/data/arc2' scope=spfile;

System altered.

#log_archive_format 지정

SQL> alter system set log_archive_format='%s_%t_%r.arc' scope=spfile;

System altered.

#pfile 사용시

SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

*.log_archive_dest_1='location=/data/arc1'

*.log_archive_dest_2='location=/data/arc2'

*.log_archive_format='%s_%t_%r.arc'

SQL>

#위에서 지정한 log_archive 디렉토리 및 포맷 적용을 위한 재 시작

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

#Archive log mode 활성화를 위해 mount 단계로 startup 한다.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

Database mounted.

#Archive log mode 상태 확인

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /data/arc2

Oldest online log sequence 2

Current log sequence 4

#Archive log mode를 활성화 한다.

SQL> alter database archivelog;

Database altered.

#Archive log mode 상태 재 확인

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /data/arc2

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

SQL> alter database open;

Database altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

Database mounted.

#Archive log mode를 비활성화 한다.

SQL> alter database noarchivelog;

Database altered.

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /data/arc2

Oldest online log sequence 2

Current log sequence 4

SQL> alter database open;

Database altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

No Archive log mode Recovery (0)	2010.03.08
Cold Backup & Hot Backup (0)	2010.03.08
Privileges(권한) 관리 (0)	2010.03.05
Profile(프로파일) 생성과 관리 (0)	2010.03.05
User(유저) 생성과 관리 (0)	2010.03.05

Privileges(권한) 관리

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 5. 03:37

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Cold Backup & Hot Backup (0)	2010.03.08
Archive log mode 설정 및 활성화/비활성화 (0)	2010.03.05
Profile(프로파일) 생성과 관리 (0)	2010.03.05
User(유저) 생성과 관리 (0)	2010.03.05
Constraints 실습 (제약 조건 테스트) (0)	2010.03.05

Profile(프로파일) 생성과 관리

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 5. 03:35

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Archive log mode 설정 및 활성화/비활성화 (0)	2010.03.05
Privileges(권한) 관리 (0)	2010.03.05
User(유저) 생성과 관리 (0)	2010.03.05
Constraints 실습 (제약 조건 테스트) (0)	2010.03.05
Table : Partitioned Table (0)	2010.03.04

User(유저) 생성과 관리

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 5. 03:34

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Privileges(권한) 관리 (0)	2010.03.05
Profile(프로파일) 생성과 관리 (0)	2010.03.05
Constraints 실습 (제약 조건 테스트) (0)	2010.03.05
Table : Partitioned Table (0)	2010.03.04
Table : 테이블의 공간 할당과 해제 (0)	2010.03.04

Constraints 실습 (제약 조건 테스트)

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 5. 03:30

■ 제약조건 테스트

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 02:12:40 2010

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 92276304 bytes

Database Buffers 188743680 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> conn hr/hr

Connected.

SQL> create table testing(no number(5) check (no in (10, 20, 30))

2 deferrable initially deferred);

Table created.

#commit 시 제약조건 검사

SQL> alter session set constraint = deferred;

Session altered.

SQL> insert into testing values(100);

1 row created.

SQL> insert into testing values(20);

1 row created.

SQL> commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02290: check constraint (HR.SYS_C005393) violated

SQL> select * from testing;

no rows selected

#DML 작업시 제약조건 즉시 검사

SQL> alter session set constraint = immediate;

Session altered.

SQL> insert into testing values(100);

insert into testing values(100)

*

ERROR at line 1:

ORA-02290: check constraint (HR.SYS_C005393) violated

SQL> insert into testing values(10);

1 row created.

SQL> commit;

Commit complete.

SQL>

■ 제약조건 활성화 / 비활성화 테스트

SQL> desc user_constraints;

Name Null? Type

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

OWNER NOT NULL VARCHAR2(30)

CONSTRAINT_NAME NOT NULL VARCHAR2(30)

CONSTRAINT_TYPE VARCHAR2(1)

TABLE_NAME NOT NULL VARCHAR2(30)

SEARCH_CONDITION LONG

R_OWNER VARCHAR2(30)

R_CONSTRAINT_NAME VARCHAR2(30)

DELETE_RULE VARCHAR2(9)

STATUS VARCHAR2(8)

DEFERRABLE VARCHAR2(14)

DEFERRED VARCHAR2(9)

VALIDATED VARCHAR2(13)

GENERATED VARCHAR2(14)

BAD VARCHAR2(3)

RELY VARCHAR2(4)

LAST_CHANGE DATE

INDEX_OWNER VARCHAR2(30)

INDEX_NAME VARCHAR2(30)

INVALID VARCHAR2(7)

VIEW_RELATED VARCHAR2(14)

SQL> col search_condition format a50;

SQL> select constraint_name, search_condition from user_constraints

2 where table_name = 'TESTING';

CONSTRAINT_NAME SEARCH_CONDITION

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

SYS_C005393 no in (10, 20, 30)

#제약조건 비활성화

SQL> alter table testing modify constraint SYS_C005393 disable;

Table altered.

#제약조건 no in (10, 20, 30)에 만족하지 않지만 비활성화 상태이기 때문에 DML이 가능하다.

SQL> insert into testing values(50);

1 row created.

SQL> insert into testing values(30);

1 row created.

SQL> commit;

Commit complete.

#제약조건 활성화

SQL> alter table testing modify constraint SYS_C005393 enable novalidate;

Table altered.

#제약조건 no in (10, 20, 30)이 활성화 상태이기 때문에 DML 작업시 에러가 발생한다.

SQL> insert into testing values(50);

insert into testing values(50)

*

ERROR at line 1:

ORA-02290: check constraint (HR.SYS_C005393) violated

SQL> insert into testing values(20);

1 row created.

SQL> commit;

Commit complete.

SQL>

■ EXCEPTIONS Table 사용법

SQL> @?/rdbms/admin/utlexcpt

Table created.

SQL> desc exceptions;

Name Null? Type

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

ROW_ID ROWID

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

CONSTRAINT VARCHAR2(30)

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Profile(프로파일) 생성과 관리 (0)	2010.03.05
User(유저) 생성과 관리 (0)	2010.03.05
Table : Partitioned Table (0)	2010.03.04
Table : 테이블의 공간 할당과 해제 (0)	2010.03.04
Table : Row Migration과 Chaining (0)	2010.03.04

Table : Partitioned Table

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 4. 12:10

■ 리스트 분할 : 학과별 정보를 각각의 분할 세그먼트에 저장하는 예

SQL> select username, default_tablespace from dba_users

2 where username = 'HR';

USERNAME DEFAULT_TABLESPACE

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

HR USERS

SQL> alter user hr

2 identified by hr

3 account unlock;

User altered.

SQL> grant unlimited tablespace to hr;

Grant succeeded.

SQL> col tablespace_name format a20

SQL> col file_name format a45

SQL> create tablespace ts1

2 datafile '/home/oracle/oradata/testdb/ts01.dbf' size 2m

3 segment space management auto;

Tablespace created.

SQL> create tablespace ts2

2 datafile '/home/oracle/oradata/testdb/ts02.dbf' size 2m

3 segment space management auto;

Tablespace created.

SQL> create tablespace ts3

2 datafile '/home/oracle/oradata/testdb/ts03.dbf' size 2m

3 segment space management auto;

Tablespace created.

SQL> select tablespace_name, file_name

2 from dba_data_files

3 where tablespace_name like 'TS%';

TABLESPACE_NAME FILE_NAME

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

TS1 /home/oracle/oradata/testdb/ts01.dbf

TS2 /home/oracle/oradata/testdb/ts02.dbf

TS3 /home/oracle/oradata/testdb/ts03.dbf

SQL> conn hr/hr

Connected.

SQL> @student <--- 없으므로 진행 불가 상태...

SQL> create table st (

2 sno varchar2(6) primary key,

3 sname varchar2(20),

4 sex varchar2(2),

5 syear number(1),

6 major varchar2(20),

7 avr number(4,2)

8 )

9 partition by list (major)

10 ( partition ma_ch values ('화학') tablespace ts1,

11 partition ma_ph values ('물리') tablespace ts2,

12 partition ma_all values ('생물', '식영', '유공') tablespace ts3);

Table created.

SQL> insert into st

2 select * from student;

select * from student

*

ERROR at line 2:

ORA-00942: table or view does not exist

SQL>

SQL> conn / as sysdba

Connected.

SQL> col owner format a10

SQL> col segment_name format a30

SQL> col tablespace_name format a20

SQL> col table_owner format a10

SQL> col table_name format a20

SQL> col partition_name format a20

SQL> select table_owner, table_name, partition_name, tablespace_name

2 from dba_tab_partitions

3 where table_owner = 'HR';

TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME

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

HR ST MA_CH TS1

HR ST MA_PH TS2

HR ST MA_ALL TS3

SQL> select table_owner, table_name, partition_name, tablespace_name

2 from dba_tab_partitions

3 where table_owner = 'HR';

TABLE_OWNE TABLE_NAME PARTITION_NAME TABLESPACE_NAME

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

HR ST MA_CH TS1

HR ST MA_PH TS2

HR ST MA_ALL TS3

SQL> select owner, table_name, partitioned

2 from dba_tables

3 where owner = 'HR';

OWNER TABLE_NAME PAR

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

HR REGIONS NO

HR LOCATIONS NO

HR DEPARTMENTS NO

HR JOBS NO

HR JOB_HISTORY NO

HR EMPLOYEES NO

HR STUDENT NO

HR COUNTRIES NO

HR ST YES

9 rows selected.

SQL>

주의사항

- Multicolumn Partition은 지원되지 않는다.

- NULL 값은 지정 가능하지만 MAXVAUES는 지정 불가능하다.

- 파티션 값의 리스트를 구성하는 문자열은 4K를 초과할 수 없다.

SQL> conn hr/hr

Connected.

SQL> alter table st

2 add partition ma_so values ('사회') tablespace ts1;

Table altered.

SQL> conn / as sysdba

Connected.

SQL> select table_owner, table_name, partition_name, tablespace_name

2 from dba_tab_partitions

3 where table_owner = 'HR';

TABLE_OWNE TABLE_NAME PARTITION_NAME TABLESPACE_NAME

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

HR ST MA_CH TS1

HR ST MA_SO TS1

HR ST MA_PH TS2

HR ST MA_ALL TS3

SQL> select owner, table_name, partitioned

2 from dba_tables

3 where owner = 'HR';

OWNER TABLE_NAME PAR

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

HR REGIONS NO

HR LOCATIONS NO

HR DEPARTMENTS NO

HR JOBS NO

HR JOB_HISTORY NO

HR EMPLOYEES NO

HR STUDENT NO

HR COUNTRIES NO

HR ST YES

9 rows selected.

SQL>

■ 범위 분할

범위분할의 예제

create table sale (

no number primary key,

goods varchar2(20),

price number,

s_date date

)

partition by range (s_date)

( partition s1999 values less than (TO_DATE('01-01-2000', 'DD-MM-YYYY')) tablespace ts1,

partition s2000_01_03 values less than (TO_DATE('01-04-2000', 'DD-MM-YYYY')) tablespace ts2,

partition s2000_04_06 values less than (TO_DATE('01-07-2000', 'DD-MM-YYYY')) tablespace ts2,

partition s2000_07_09 values less than (TO_DATE('01-10-2000', 'DD-MM-YYYY')) tablespace ts3,

partition s2000_10 values less than (MAXVALUE) tablespace ts3);

SQL> conn hr/hr

Connected.

SQL> create table sale (

2 no number primary key,

3 goods varchar2(20),

4 price number,

5 s_date date

6 )

7 partition by range (price)

8 ( partition p100 values less than (100),

9 partition p200 values less than (200),

10 partition p300 values less than (300),

11 partition pr_max values less than (MAXVALUE));

Table created.

SQL> alter table sale

2 drop partition pr_max;

Table altered.

SQL> alter table sale

2 add partition p400 values less than (400);

Table altered.

SQL> alter table sale

2 add partition pr_max values less than (MAXVALUE)

Table altered.

SQL> conn / as sysdba

Connected.

SQL> select table_owner, table_name, partition_name, tablespace_name

2 from dba_tab_partitions

3 where table_owner = 'HR';

TABLE_OWNE TABLE_NAME PARTITION_NAME TABLESPACE_NAME

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

HR SALE PR_MAX USERS

HR SALE P100 USERS

HR SALE P200 USERS

HR SALE P300 USERS

HR SALE P400 USERS

HR ST MA_CH TS1

HR ST MA_SO TS1

HR ST MA_PH TS2

HR ST MA_ALL TS3

9 rows selected.

SQL> select owner, table_name, partitioned

2 from dba_tables

3 where owner = 'HR';

OWNER TABLE_NAME PAR

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

HR REGIONS NO

HR LOCATIONS NO

HR DEPARTMENTS NO

HR JOBS NO

HR JOB_HISTORY NO

HR EMPLOYEES NO

HR STUDENT NO

HR COUNTRIES NO

HR ST YES

HR SALE YES

10 rows selected.

SQL>

■ 해시 분할

범위분할의 예제

create table st1 (

sno varchar2(6) primary key,

sname varchar2(20),

sex varchar2(2),

syear number(1),

major varchar2(20),

avr number(4,2)

)

partition by hash (sno)

partitions 8 store in (ts1, ts2, ts3);

SQL> conn / as sysdba

Connected.

SQL> create tablespace insa

2 datafile '/home/oracle/oradata/testdb/insa01.dbf' size 5m;

Tablespace created.

SQL> create user insa

2 identified by insa

3 default tablespace insa

4 quota unlimited on insa;

User created.

SQL> grant create session, create table to insa;

Grant succeeded.

SQL> conn insa/insa

Connected.

SQL> create table jikwon

2 ( sabun number(8),

3 name varchar2(10),

4 address varchar2(50),

5 salary number(8));

Table created.

#현재 로그인중인 계정이 사용 가능한 테이블 확인

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

JIKWON TABLE

#jikwon 테이블의 구조 확인

SQL> desc jikwon

Name Null? Type

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

SABUN NUMBER(8)

NAME VARCHAR2(10)

ADDRESS VARCHAR2(50)

SALARY NUMBER(8)

SQL> insert into jikwon

2 values (20071114, '천만득', '서울동작구', 200);

1 row created.

SQL> select * from jikwon;

SABUN NAME ADDRESS SALARY

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

20071114 천만득 서울동작구 200

#서브쿼리절을 이용한 테이블 생성

SQL> create table jikwon2

2 as select * from jikwon;

Table created.

#서브쿼리절 이용시 특정 컬럼만으로 테이블 생성

SQL> create table jikwon3

2 as select sabun, name from jikwon;

Table created.

SQL> desc jikwon3;

Name Null? Type

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

SABUN NUMBER(8)

NAME VARCHAR2(10)

#서브쿼리절을 이용해 테이블 구조만 복사 (데이터는 복사하지 않음)

SQL> create table jikwon4

2 as select * from jikwon

3 where 1 = 2;

Table created.

#테이블 구조 변경

#컬럼 추가

SQL> alter table jikwon

2 add (phone number(10));

Table altered.

SQL> desc jikwon;

Name Null? Type

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

SABUN NUMBER(8)

NAME VARCHAR2(10)

ADDRESS VARCHAR2(50)

SALARY NUMBER(8)

PHONE NUMBER(10)

SQL> alter table jikwon

2 drop column phone;

Table altered.

SQL> desc jikwon;

Name Null? Type

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

SABUN NUMBER(8)

NAME VARCHAR2(10)

ADDRESS VARCHAR2(50)

SALARY NUMBER(8)

#컬럼 삭제

#컬럼 변경으로 데이터 타입, 크기, 기본값을 변경할 수있다.

#변경 대상 컬럼에 데이터가 없을 경우에는 자유롭지만

#데이터가 있을 경우 char, varchar2 타입만 변경이 가능하며 크기 변경 시 최소 같거나 커야만 한다.

SQL> alter table jikwon

2 modify name varchar2(15);

Table altered.

SQL> desc jikwon;

Name Null? Type

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

SABUN NUMBER(8)

NAME VARCHAR2(15)

ADDRESS VARCHAR2(50)

SALARY NUMBER(8)

#테이블 이름 변경

SQL> rename jikwon2 to jikwon6;

Table renamed.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

JIKWON TABLE

JIKWON3 TABLE

JIKWON4 TABLE

JIKWON6 TABLE

SQL> drop table jikwon6;

Table dropped.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

JIKWON TABLE

BIN$gPGFSrk7XRTgQKjADgB4bQ==$0 TABLE

JIKWON3 TABLE

JIKWON4 TABLE

SQL>

#테이블 삭제 시 다른 테이블과 제약조건이 걸려있으면 삭제가 되지 않는다.

#이럴 경우에는 cascade constraints 옵션을 사용하면 삭제가 가능하다.

SQL> drop table jikwon6 cascade constraints;

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

User(유저) 생성과 관리 (0)	2010.03.05
Constraints 실습 (제약 조건 테스트) (0)	2010.03.05
Table : 테이블의 공간 할당과 해제 (0)	2010.03.04
Table : Row Migration과 Chaining (0)	2010.03.04
Undo Tablespace 생성과 관리 (9i) (0)	2010.03.02

Table : 테이블의 공간 할당과 해제

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 4. 11:30

실습: 테이블에 대한 공간 할당과 해제

SQL> analyze table hr.employees compute statistics;

analyze table hr.employees compute statistics

*

ERROR at line 1:

ORA-01502: index 'HR.EMP_NAME_IX' or partition of such index is in unusable state

SQL> select table_name, index_name, status from dba_indexes

2 where table_name = 'EMPLOYEES';

TABLE_NAME INDEX_NAME STATUS

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

EMPLOYEES EMP_EMAIL_UK UNUSABLE

EMPLOYEES EMP_EMP_ID_PK UNUSABLE

EMPLOYEES EMP_DEPARTMENT_IX UNUSABLE

EMPLOYEES EMP_JOB_IX UNUSABLE

EMPLOYEES EMP_MANAGER_IX UNUSABLE

EMPLOYEES EMP_NAME_IX UNUSABLE

6 rows selected.

SQL> alter index hr.emp_email_uk rebuild;

Index altered.

SQL> alter index hr.emp_emp_id_pk rebuild;

Index altered.

SQL> alter index hr.emp_department_ix rebuild;

Index altered.

SQL> alter index hr.emp_job_ix rebuild;

Index altered.

SQL> alter index hr.emp_manager_ix rebuild;

Index altered.

SQL> alter index hr.emp_name_ix rebuild;

Index altered.

SQL> select table_name, index_name, status from dba_indexes

2 where table_name = 'EMPLOYEES';

TABLE_NAME INDEX_NAME STATUS

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

EMPLOYEES EMP_EMAIL_UK VALID

EMPLOYEES EMP_EMP_ID_PK VALID

EMPLOYEES EMP_DEPARTMENT_IX VALID

EMPLOYEES EMP_JOB_IX VALID

EMPLOYEES EMP_MANAGER_IX VALID

EMPLOYEES EMP_NAME_IX VALID

6 rows selected.

SQL> analyze table hr.employees compute statistics;

Table analyzed.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len

2 from dba_tables

3 where owner = 'HR' and table_name = 'EMPLOYEES';

NUM_ROWS BLOCKS EMPTY AVG_SPACE AVG_ROW_LEN

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

107 5 3 4129 71

#NUM_ROWS : table에 저장된 row의 수

#BLOCKS : 사용중인 block (H/W 왼쪽 block의 수)

#EMPTY : 사용한 적이 없는 block (H/W 오른쪽 block의 수)

#AVG_SPACE : 사용중인 block의 평균 빈 공간 (Byte)

#AVG_ROW_LEN : row의 평균 길이

SQL> alter table hr.employees allocate extent (size 1m);

Table altered.

SQL> analyze table hr.employees compute statistics;

Table analyzed.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len

2 from dba_tables

3 where owner = 'HR' and table_name = 'EMPLOYEES';

NUM_ROWS BLOCKS EMPTY AVG_SPACE AVG_ROW_LEN

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

107 5 251 4129 71

SQL> alter table hr.employees deallocate unused;

Table altered.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len

2 from dba_tables

3 where owner = 'HR' and table_name = 'EMPLOYEES';

NUM_ROWS BLOCKS EMPTY AVG_SPACE AVG_ROW_LEN

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

107 5 251 4129 71

SQL> analyze table hr.employees compute statistics;

Table analyzed.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len

2 from dba_tables

3 where owner = 'HR' and table_name = 'EMPLOYEES';

NUM_ROWS BLOCKS EMPTY AVG_SPACE AVG_ROW_LEN

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

107 5 3 4129 71

SQL> select table_name, initial_extent, min_extents

2 from dba_tables

3 where owner = 'HR' and table_name = 'EMPLOYEES';

TABLE_NAME INITIAL_EXTENT MIN_EXTENTS

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

EMPLOYEES 65536 1

SQL> select tablespace_name, block_size, initial_extent, min_extents

2 from dba_tablespaces

3 where tablespace_name = 'EXAMPLE';

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT MIN_EXTENTS

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

EXAMPLE 8192 65536 1

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Constraints 실습 (제약 조건 테스트) (0)	2010.03.05
Table : Partitioned Table (0)	2010.03.04
Table : Row Migration과 Chaining (0)	2010.03.04
Undo Tablespace 생성과 관리 (9i) (0)	2010.03.02
Temporary Tablespace 생성과 Default Temporary Tablespace 지정 (0)	2010.03.02

Table : Row Migration과 Chaining

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 4. 11:19

실습: 테이블의 상태 확인과 Rebuild

[oracle@ghost sql]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 4 10:52:20 2010

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 92276304 bytes

Database Buffers 188743680 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> col tablespace_name format a20

SQL> analyze table hr.employees compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt from dba_tables where table_name = 'EMPLOYEES';

NUM_ROWS CHAIN_CNT

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

107 0

SQL> create tablespace test

2 datafile '/home/oracle/oradata/testdb/test01.dbf' size 10m

3 segment space management auto;

Tablespace created.

SQL> alter user hr quota 1m on test;

User altered.

SQL> conn hr/hr

Connected.

SQL> alter table employees move tablespace test;

Table altered.

SQL> conn / as sysdba

Connected.

SQL> select owner, table_name, tablespace_name from dba_tables

2 where owner = 'HR' and table_name = 'EMPLOYEES';

OWNER TABLE_NAME TABLESPACE_NAME

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

HR EMPLOYEES TEST

SQL> alter table hr.employees move tablespace example;

Table altered.

SQL> select owner, table_name, tablespace_name from dba_tables

2 where owner = 'HR' and table_name = 'EMPLOYEES';

OWNER TABLE_NAME TABLESPACE_NAME

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

HR EMPLOYEES EXAMPLE

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Table : Partitioned Table (0)	2010.03.04
Table : 테이블의 공간 할당과 해제 (0)	2010.03.04
Undo Tablespace 생성과 관리 (9i) (0)	2010.03.02
Temporary Tablespace 생성과 Default Temporary Tablespace 지정 (0)	2010.03.02
Tablespace 실습 #6 (0)	2010.02.27

Undo Tablespace 생성과 관리 (9i)

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 2. 12:07

새로운 파라미터설정
UNDO_MANAGEMENT = AUTO [MANUAL]
UNDO_TABLESPACE = UNDOTBS1
UNDO_SUPPRESS_ERRORS = TRUE <--- 설정되어 있지 않음 (~9i)
UNDO_RETENTION = integer (시간:초)

실습 #1 현재 Undo 관련 설정 확인
#현재 Oracle Database 10g R2 버전이기 때문에 에러가 발생한다. (~9i)
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> create rollback segment rbs01 tablespace users;

Rollback segment created.

SQL> alter system set undo+suppress_errors = true;
alter system set undo+suppress_errors = true
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> alter system set undo_suppress_errors = true;
alter system set undo_suppress_errors = true
*
ERROR at line 1:
ORA-25138: UNDO_SUPPRESS_ERRORS initialization parameter has been made obsolete

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> select segment_name from dba_segments
2 where lower(segment_name) like 'rbs%';

no rows selected

SQL>

실습 #2 UNDO Tablespace 생성과 Default Undo Tablespace 지정
SQL> create undo tablespace undo
2 datafile '/home/oracle/oradata/testdb/undo01.dbf' size 10m;

Tablespace created.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- --------------------------------------------------
USERS                             5242880 /disk3/users01.dbf
SYSAUX                          251658240 /disk1/sysaux01.dbf
UNDOTBS1                         36700160 /disk2/undotbs01.dbf
SYSTEM                          503316480 /disk1/system01.dbf
UNDO                             10485760 /home/oracle/oradata/testdb/undo01.dbf
EXAMPLE                         104857600 /disk3/example01.dbf

6 rows selected.

SQL> alter system set undo_tablespace = undo;

System altered.

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDO
SQL> select segment_name, owner, tablespace_name, status from dba_rollback_segs;

SEGMENT_NAME                   OWNER TABLESPACE_NAME                STATUS
------------------------------ ------ ------------------------------ ----------------
SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU10$                     PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU9$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU8$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU7$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU6$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU5$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU4$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU3$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU2$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU1$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU20$                     PUBLIC UNDO                           ONLINE
_SYSSMU19$                     PUBLIC UNDO                           ONLINE
_SYSSMU18$                     PUBLIC UNDO                           ONLINE
_SYSSMU17$                     PUBLIC UNDO                           ONLINE
_SYSSMU16$                     PUBLIC UNDO                           ONLINE
_SYSSMU15$                     PUBLIC UNDO                           ONLINE
_SYSSMU14$                     PUBLIC UNDO                           ONLINE
_SYSSMU13$                     PUBLIC UNDO                           ONLINE
_SYSSMU12$                     PUBLIC UNDO                           ONLINE
_SYSSMU11$                     PUBLIC UNDO                           ONLINE

21 rows selected.

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDO
SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- --------------------------------------------------
USERS                             5242880 /disk3/users01.dbf
SYSAUX                          251658240 /disk1/sysaux01.dbf
SYSTEM                          503316480 /disk1/system01.dbf
UNDO                             10485760 /home/oracle/oradata/testdb/undo01.dbf
EXAMPLE                         104857600 /disk3/example01.dbf

SQL> select segment_name, owner, tablespace_name, status from dba_rollback_segs;

SEGMENT_NAME                   OWNER TABLESPACE_NAME                STATUS
------------------------------ ------ ------------------------------ ----------------
SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU20$                     PUBLIC UNDO                           ONLINE
_SYSSMU19$                     PUBLIC UNDO                           ONLINE
_SYSSMU18$                     PUBLIC UNDO                           ONLINE
_SYSSMU17$                     PUBLIC UNDO                           ONLINE
_SYSSMU16$                     PUBLIC UNDO                           ONLINE
_SYSSMU15$                     PUBLIC UNDO                           ONLINE
_SYSSMU14$                     PUBLIC UNDO                           ONLINE
_SYSSMU13$                     PUBLIC UNDO                           ONLINE
_SYSSMU12$                     PUBLIC UNDO                           ONLINE
_SYSSMU11$                     PUBLIC UNDO                           ONLINE

11 rows selected.

SQL>

#현재 상태로 shutdown 했다가 startup 을 할 경우 에러가 발생한다.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

#Parameter file의 내용을 아래와 같이 편집한다.
SQL> !vi /home/oracle/product/10g/dbs/inittestdb.ora
*.undo_tablespace='UNDO' <--- 기존 설정 내용은 *.undo_tablespace='UNDOTBS1' 이다.

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Table : 테이블의 공간 할당과 해제 (0)	2010.03.04
Table : Row Migration과 Chaining (0)	2010.03.04
Temporary Tablespace 생성과 Default Temporary Tablespace 지정 (0)	2010.03.02
Tablespace 실습 #6 (0)	2010.02.27
Tablespace 실습 #5 (0)	2010.02.27

Ghost's Oracle Database !

Category

Notice

Archive

My Link

Tag

Recent Comment

Recent Post

Recent Trackback

calendar

'Oracle 10g/10g - 실습'에 해당되는 글 30건

Cold Backup & Hot Backup

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Archive log mode 설정 및 활성화/비활성화

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Privileges(권한) 관리

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Profile(프로파일) 생성과 관리

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

User(유저) 생성과 관리

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Constraints 실습 (제약 조건 테스트)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Table : Partitioned Table

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Table : 테이블의 공간 할당과 해제

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Table : Row Migration과 Chaining

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Undo Tablespace 생성과 관리 (9i)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

티스토리툴바

Category

Notice

Archive

My Link

Search

Tag

Recent Comment

Recent Post

Recent Trackback

calendar

'Oracle 10g/10g - 실습'에 해당되는 글 30건

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

티스토리툴바