레도널스의 개발자 되어가기

Flashback 실습

[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-JUL-2018 09:14:31

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date                03-JUL-2018 09:14:32

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))

The listener supports no services

The command completed successfully

[oracle@localhost ~]$ startup

bash: startup: command not found

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 3 09:14:47 2018

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SYS> startup

ORACLE instance started.

Total System Global Area  623546368 bytes

Fixed Size                  1338308 bytes

Variable Size             478151740 bytes

Database Buffers          138412032 bytes

Redo Buffers                5644288 bytes

Database mounted.

Database opened.

SYS> conn hr/hr

Connected.

HR> select avg(salary) from bigemp;

AVG(SALARY)

-----------

 10641.8825

HR> update bigemp

  2  set salary=salary*1.1;

655360 rows updated.

HR> !date

Tue Jul  3 09:32:12 KST 2018

HR> commit

  2  ;

Commit complete.

HR> update bigemp

  2  set salary = salary * 1.1;

655360 rows updated.

HR> commit;

Commit complete.

HR> !date

Tue Jul  3 09:35:07 KST 2018

HR> alter table bigemp enable row movement;

Table altered.

HR> flashback table bigemp to timestamp

  2  to timestamp('2018/07/03 09:32:30','yyyy/mm/dd hh24:mi:ss');

to timestamp('2018/07/03 09:32:30','yyyy/mm/dd hh24:mi:ss')

*

ERROR at line 2:

ORA-00936: missing expression

HR> ed

Wrote file afiedt.buf

  1  flashback table bigemp to timestamp

  2* to_timestamp('2018/07/03 09:32:30','yyyy/mm/dd hh24:mi:ss')

HR> /

Flashback complete.

HR> alter table bigemp disable row movement;

Table altered.

HR> select avg(salary) from bigemp;

AVG(SALARY)

-----------

 10641.8825

HR> select * from v$option;

PARAMETER

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

VALUE

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

Partitioning

TRUE

Objects

TRUE

Real Application Clusters

FALSE

PARAMETER

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

VALUE

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

Advanced replication

TRUE

Bit-mapped indexes

TRUE

Connection multiplexing

TRUE

PARAMETER

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

VALUE

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

Connection pooling

TRUE

Database queuing

TRUE

Incremental backup and recovery

TRUE

PARAMETER

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

VALUE

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

Instead-of triggers

TRUE

Parallel backup and recovery

TRUE

Parallel execution

TRUE

PARAMETER

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

VALUE

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

Parallel load

TRUE

Point-in-time tablespace recovery

TRUE

Fine-grained access control

TRUE

PARAMETER

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

VALUE

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

Proxy authentication/authorization

TRUE

Change Data Capture

TRUE

Plan Stability

TRUE

PARAMETER

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

VALUE

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

Online Index Build

TRUE

Coalesce Index

TRUE

Managed Standby

TRUE

PARAMETER

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

VALUE

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

Materialized view rewrite

TRUE

Materialized view warehouse refresh

TRUE

Database resource manager

TRUE

PARAMETER

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

VALUE

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

Spatial

TRUE

Automatic Storage Management

FALSE

Export transportable tablespaces

TRUE

PARAMETER

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

VALUE

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

Transparent Application Failover

TRUE

Fast-Start Fault Recovery

TRUE

Sample Scan

TRUE

PARAMETER

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

VALUE

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

Duplexed backups

TRUE

Java

TRUE

OLAP Window Functions

TRUE

PARAMETER

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

VALUE

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

Block Media Recovery

TRUE

Fine-grained Auditing

TRUE

Application Role

TRUE

PARAMETER

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

VALUE

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

Enterprise User Security

TRUE

Oracle Data Guard

TRUE

Oracle Label Security

FALSE

PARAMETER

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

VALUE

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

OLAP

TRUE

Basic Compression

TRUE

Join index

TRUE

PARAMETER

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

VALUE

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

Trial Recovery

TRUE

Data Mining

TRUE

Online Redefinition

TRUE

PARAMETER

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

VALUE

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

Streams Capture

TRUE

File Mapping

TRUE

Block Change Tracking

TRUE

PARAMETER

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

VALUE

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

Flashback Table

TRUE

Flashback Database

TRUE

Transparent Data Encryption

TRUE

PARAMETER

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

VALUE

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

Backup Encryption

TRUE

Unused Block Compression

TRUE

Oracle Database Vault

FALSE

PARAMETER

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

VALUE

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

Result Cache

TRUE

SQL Plan Management

TRUE

SecureFiles Encryption

TRUE

PARAMETER

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

VALUE

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

Real Application Testing

TRUE

Flashback Data Archive

TRUE

DICOM

TRUE

PARAMETER

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

VALUE

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

Active Data Guard

TRUE

Server Flash Cache

TRUE

Advanced Compression

TRUE

PARAMETER

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

VALUE

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

XStream

TRUE

Deferred Segment Creation

TRUE

65 rows selected.

HR> conn / as sysdba

Connected.

SYS> show parameter undo

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SYS> create tablespace f_tbs

  2  datafile '/u01/app/oracle/oradata/orcl/f_tbs01.dbf' size 20m

  3  autoextend on;

Tablespace created.

SYS> create user fa identified by fa

  2  default tablespace f_tbs

  3  temporary tablespace temp;

User created.

SYS> grant connect, resource to fa;

Grant succeeded.

SYS> grant flashback archive administer to fa;

Grant succeeded.

SYS> conn fa/fa

Connected.

FA> create flashback archive fla1

  2  tablespace f_tbs 

  3  retention 1 year;

Flashback archive created.

FA> ed

Wrote file afiedt.buf

  1  create flashback archive fla3

  2  tablespace f_tbs

  3* retention 3 year

FA> grant flashback archive on fla1 to hr;

Grant succeeded.

FA> conn hr/hr

Connected.

HR> create table f_emp

  2  as

  3  select employee_id, last_name, salary

  4  from employees

  5  ;        

Table created.

HR> alter table f_emp flashback archive fla1;

Table altered.

HR> create table nof_emp

  2  as

  3  select employee_id, last_name, salary

  4  from employees;

Table created.

HR> select * from user_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME

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

FLASHBACK_ARCHIVE_NAME

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

ARCHIVE_TABLE_NAME                                    STATUS

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

F_EMP                          HR

FLA1

SYS_FBA_HIST_17855                                    ENABLED

HR> spool /home/oracle/test_fa

HR> select * from f_emp

  2  where employee_id = 101;

EMPLOYEE_ID LAST_NAME                     SALARY

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

        101 Kochhar                        17000

HR> !date

Tue Jul  3 10:31:14 KST 2018

HR> update f_emp

  2  set salary = salary*1.1

  3  where employee_id = 101;

1 row updated.

HR> commit;

Commit complete.

HR> update nof_emp

  2  set salary = salary*1.1

  3  where employee_id = 101;

1 row updated.

HR> commit;

Commit complete.

HR> !date

Tue Jul  3 10:32:42 KST 2018

HR> spool off;

HR> !ls /home/oracle

afiedt.buf  expdat.dmp      q_bc.sql   q_tbs.sql    ws2_labs

backup      labs            q_dbf.sql  test_fa.lst

Desktop     oradiag_oracle  q_log.sql  users01.dbf

HR> col versions_starttime for a32

HR> col versions_endtime for a32

HR> select versions_starttime, versions_endtime, salary

  2  from f_emp

  3  versions between timestamp minvalue and maxvalue

  4  where employee_id = 101;

VERSIONS_STARTTIME               VERSIONS_ENDTIME                     SALARY

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

                                 03-JUL-18 10.31.46.000000000 AM       17000

03-JUL-18 10.31.46.000000000 AM                                        18700

HR> select versions_starttime, versions_endtime, salary

  2  from nof_emp

  3  versions between timestamp minvalue and maxvalue

  4  where employee_id = 101;

VERSIONS_STARTTIME               VERSIONS_ENDTIME                     SALARY

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

                                                                       18700

HR> drop table nof_emp purge;

Table dropped.

HR> conn / as sysdba

Connected.

SYS> drop table f_emp purge;

drop table f_emp purge

           *

ERROR at line 1:

ORA-00942: table or view does not exist

SYS> alter table hr.f_emp no flashback archive;

Table altered.

SYS> conn hr/hr

Connected.

HR> drop table f_emp purge;

Table dropped.

HR> conn / as sysdba

Connected.

SYS> drop user fa cascade;

User dropped.

SYS> drop tablespace f_tbs including contents and datafiles;

Tablespace dropped.

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

Flashback Database 실습

[oracle@localhost ~]$ cd /u01/app/oracle/flash_recovery_area/ORCL/

[oracle@localhost ORCL]$ ls

archivelog  autobackup  backupset

(flash_recovery_area/ORCL 디렉토리의 내용 확인)

SYS> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     190

Next log sequence to archive   192

Current log sequence           192

SYS> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SYS> alter database flashback on;

Database altered.

SYS> select flashback_on from v$database;

FLASHBACK_ON

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

YES

(다시 폴더 확인)

[oracle@localhost ORCL]$ ls

archivelog  autobackup  backupset  flashback

(flashback이라는 디렉토리가 만들어졌다.)

[oracle@localhost ORCL]$ cd flashback/

[oracle@localhost flashback]$ ls

o1_mf_fmor2181_.flb

(백그라운드 프로세스 확인)

[oracle@localhost flashback]$ ps -ef | grep orcl

oracle    5292     1  0 09:14 ?        00:00:00 ora_pmon_orcl

oracle    5294     1  0 09:14 ?        00:00:00 ora_vktm_orcl

oracle    5298     1  0 09:14 ?        00:00:00 ora_gen0_orcl

oracle    5300     1  0 09:14 ?        00:00:00 ora_diag_orcl

oracle    5302     1  0 09:14 ?        00:00:00 ora_dbrm_orcl

oracle    5304     1  0 09:14 ?        00:00:00 ora_psp0_orcl

oracle    5306     1  0 09:14 ?        00:00:00 ora_dia0_orcl

oracle    5308     1  0 09:14 ?        00:00:00 ora_mman_orcl

oracle    5310     1  0 09:14 ?        00:00:01 ora_dbw0_orcl

oracle    5312     1  0 09:14 ?        00:00:03 ora_lgwr_orcl

oracle    5314     1  0 09:14 ?        00:00:00 ora_ckpt_orcl

oracle    5316     1  0 09:14 ?        00:00:00 ora_smon_orcl

oracle    5318     1  0 09:14 ?        00:00:00 ora_reco_orcl

oracle    5320     1  0 09:14 ?        00:00:00 ora_mmon_orcl

oracle    5322     1  0 09:15 ?        00:00:00 ora_mmnl_orcl

oracle    5363     1  0 09:15 ?        00:00:00 ora_arc0_orcl

oracle    5365     1  0 09:15 ?        00:00:00 ora_arc1_orcl

oracle    5367     1  0 09:15 ?        00:00:00 ora_arc2_orcl

oracle    5369     1  0 09:15 ?        00:00:00 ora_arc3_orcl

oracle    5371     1  0 09:15 ?        00:00:00 ora_ctwr_orcl

oracle    5374     1  0 09:15 ?        00:00:00 ora_qmnc_orcl

oracle    5389     1  0 09:15 ?        00:00:00 ora_cjq0_orcl

oracle    5404     1  0 09:15 ?        00:00:00 ora_q000_orcl

oracle    5406     1  0 09:15 ?        00:00:00 ora_q001_orcl

oracle    5490     1  0 09:20 ?        00:00:00 ora_smco_orcl

oracle    6215     1  0 09:32 pts/2    00:00:00 /u01/app/oracle/product/11.2.0/db_1/perl/bin/perl /u01/app/oracle/product/11.2.0/db_1/bin/emwd.pl dbconsole /u01/app/oracle/product/11.2.0/db_1/localhost.localdomain_orcl/sysman/log/emdb.nohup

oracle    6229  6215  0 09:32 pts/2    00:00:31 /u01/app/oracle/product/11.2.0/db_1/jdk/bin/java -server -Xmx192M -XX:MaxPermSize=200M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 -Doracle.home=/u01/app/oracle/product/11.2.0/db_1/oc4j -Doracle.oc4j.localhome=/u01/app/oracle/product/11.2.0/db_1/localhost.localdomain_orcl/sysman -DEMSTATE=/u01/app/oracle/product/11.2.0/db_1/localhost.localdomain_orcl -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.localdomain_orcl/config/jazn.xml -Djava.security.policy=/u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.localdomain_orcl/config/java2.policy -Djavax.net.ssl.KeyStore=/u01/app/oracle/product/11.2.0/db_1/sysman/config/OCMTrustedCerts.txt-Djava.security.properties=/u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/u01/app/oracle/product/11.2.0/db_1/localhost.localdomain_orcl -Dsysman.md5password=true -Drepapi.oracle.home=/u01/app/oracle/product/11.2.0/db_1 -Ddisable.checkForUpdate=true -Doracle.sysman.ccr.ocmSDK.websvc.keystore=/u01/app/oracle/product/11.2.0/db_1/jlib/emocmclnt.ks -Dice.pilots.html4.ignoreNonGenericFonts=true -Djava.awt.headless=true -jar /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/home/oc4j.jar -config /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.localdomain_orcl/config/server.xml

oracle    6265     1  0 09:33 ?        00:00:07 oracleorcl (LOCAL=NO)

oracle    6305     1  0 09:33 ?        00:00:00 oracleorcl (LOCAL=NO)

oracle    6538     1  0 09:33 ?        00:00:01 oracleorcl (LOCAL=NO)

oracle    6780     1  0 09:33 ?        00:00:00 oracleorcl (LOCAL=NO)

oracle    6786     1  0 09:33 ?        00:00:01 oracleorcl (LOCAL=NO)

oracle    6848     1  0 09:33 ?        00:00:04 oracleorcl (LOCAL=NO)

oracle    6898     1  0 09:33 ?        00:00:00 oracleorcl (LOCAL=NO)

oracle    6900     1  0 09:33 ?        00:00:00 oracleorcl (LOCAL=NO)

oracle    8366     1  0 09:52 ?        00:00:00 oracleorcl (LOCAL=NO)

oracle   11218     1  0 10:18 ?        00:00:00 ora_fbda_orcl

oracle   15277     1  0 11:10 ?        00:00:00 ora_w000_orcl

oracle   16644  5221  0 11:28 ?        00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle   16894     1  0 11:32 ?        00:00:00 ora_rvwr_orcl

oracle   16978     1  0 11:33 ?        00:00:00 oracleorcl (LOCAL=NO)

oracle   17141     1  0 11:34 ?        00:00:00 oracleorcl (LOCAL=NO)

oracle   17354  5758  0 11:37 pts/2    00:00:00 grep orcl

(이제 flashback database 실습용 유저 만들고 접속하기)

SYS> create user fd identified by fd;

User created.

SYS> grant connect, resource to fd;

Grant succeeded.

SYS> conn fd/fd

Connected.

FD> create table fd_test 

  2  (id number(2));

Table created.

FD> insert into fd_test

  2  values(12);

1 row created.

FD> insert into fd_test   

  2  values(22);

1 row created.

FD> commit;

Commit complete.

FD> conn / as sysdba

Connected.

SYS> select current_scn from v$database;

CURRENT_SCN

-----------

    1493883

SYS> select scn_to_timestamp(1493883) from dual;

SCN_TO_TIMESTAMP(1493883)

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

03-JUL-18 11.42.51.000000000 AM

SYS> drop user fd cascade;

User dropped.

SYS> create user aa identified by aa;

User created.

SYS> grant connect, resource to aa;

Grant succeeded.

SYS> conn aa/aa

Connected.

AA> create table aatest

  2  (id number);

Table created.

AA> conn hr/hr

Connected.

HR> update employees

  2  set salary = salary*1.1;

20 rows updated.

HR> commit;

Commit complete.

HR> select avg(salary) from employees;

AVG(SALARY)

-----------

       9691

HR> conn fd/fd

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

> 

(유저가 삭제되었음을 발견)

> conn / as sysdba

Connected.

(DB를 내린다.)

SYS> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

(마운트 상태까지만 올린다.)

SYS> startup mount exclusive 

ORACLE instance started.

Total System Global Area  623546368 bytes

Fixed Size                  1338308 bytes

Variable Size             469763132 bytes

Database Buffers          146800640 bytes

Redo Buffers                5644288 bytes

Database mounted.

(시점을 정하여 flashback 한다.)

SYS> flashback database to scn 1493883;

Flashback complete.

(DB를 다시 연다.)

SYS> alter database open resetlogs;

Database altered.

SYS> conn fd/fd

Connected.

FD> select * from fd_test;

        ID

----------

        12

        22

FD> conn aa/aa

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

(fd 유저는 살아났지만, 그 후에 수정(추가)한 aa 유저는 없다.)

> conn / as sysdba

Connected.

SYS> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

(불완전 복구와 매우 닮았음을 알 수 있다.)

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

Managing Memory

실습)

SYS> col component for a35

SYS> select component, current_size/1024/2014

  2 from v$sga_dynamic_components;

COMPONENT                           CURRENT_SIZE/1024/2014

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

shared pool                                     113.890765

large pool                                      4.06752731

java pool                                       2.03376365

streams pool                                    2.03376365

DEFAULT buffer cache                            71.1817279

KEEP buffer cache                                        0

RECYCLE buffer cache                                     0

DEFAULT 2K buffer cache                                  0

DEFAULT 4K buffer cache                                  0

DEFAULT 8K buffer cache                                  0

DEFAULT 16K buffer cache                                 0

COMPONENT                           CURRENT_SIZE/1024/2014

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

DEFAULT 32K buffer cache                                 0

Shared IO Pool                                           0

ASM Buffer Cache                                         0

14 rows selected.

SYS> show parameter cache_size

NAME                                 TYPE        VALUE

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

client_result_cache_size             big integer 0

db_16k_cache_size                    big integer 0

db_2k_cache_size                     big integer 0

db_32k_cache_size                    big integer 0

db_4k_cache_size                     big integer 0

db_8k_cache_size                     big integer 0

db_cache_size                        big integer 0

db_flash_cache_size                  big integer 0

db_keep_cache_size                   big integer 0

db_recycle_cache_size                big integer 0

SYS> alter system set db_keep_cache_size=10m;

System altered.

SYS> alter system set db_recycle_cache_size=10m;

System altered.

SYS> select component, current_size/1024/2014

  2  from v$sga_dynamic_components;

COMPONENT                           CURRENT_SIZE/1024/2014

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

shared pool                                     113.890765

large pool                                      4.06752731

java pool                                       2.03376365

streams pool                                    2.03376365

DEFAULT buffer cache                             58.979146

KEEP buffer cache                               6.10129096

RECYCLE buffer cache                            6.10129096

DEFAULT 2K buffer cache                                  0

DEFAULT 4K buffer cache                                  0

DEFAULT 8K buffer cache                                  0

DEFAULT 16K buffer cache                                 0

COMPONENT                           CURRENT_SIZE/1024/2014

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

DEFAULT 32K buffer cache                                 0

Shared IO Pool                                           0

ASM Buffer Cache                                         0

14 rows selected.

SYS> select tablespace_name, block_size from dba_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE

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

SYSTEM                               8192

SYSAUX                               8192

UNDOTBS1                             8192

TEMP                                 8192

USERS                                8192

SYS> create tablespace sales

  2  datafile '/u01/app/oracle/oradata/orcl/sales01.dbf'

  3  size 10m blocksize 4k;

create tablespace sales

*

ERROR at line 1:

ORA-29339: tablespace block size 4096 does not match configured block sizes

SYS> alter system set db_4k_cache_size=10m;

System altered.

SYS> create tablespace sales

  2  datafile '/u01/app/oracle/oradata/orcl/sales01.dbf'

  3  size 10m blocksize 4k;

Tablespace created.

SYS> select tablespace_name, block_size from dba_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE

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

SYSTEM                               8192

SYSAUX                               8192

UNDOTBS1                             8192

TEMP                                 8192

USERS                                8192

SALES                                4096

6 rows selected.

SYS> conn hr/hr

Connected.

HR> analyze table bigemp compute statistics;

Table analyzed.

HR> select num_rows, blocks, empty_blocks from user_tables

  2  where table_name = 'BIGEMP';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

    655360       7051          117

HR> ed

Wrote file afiedt.buf

  1  select tablespace_name, num_rows, blocks, empty_blocks from user_tables

  2* where table_name = 'BIGEMP'

HR> /

TABLESPACE_NAME                  NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

USERS                              655360       7051          117

HR> conn / as sysdba

Connected.

SYS> alter database datafile

  2  '/u01/app/oracle/oradata/orcl/sales01.dbf' resize 100m;

Database altered.

SYS> conn hr/hr

Connected.

HR> create table bigemp2

  2  tablespace sales

  3  as

  4  select * from bigemp;

Table created.

HR> analyze table bigemp2 compute statistics;

Table analyzed.

HR> select tablespace_name, num_rows, blocks, empty_blocks from user_tables

  2 where table_name = 'BIGEMP2';

TABLESPACE_NAME                  NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

SALES                              655360      13975          105

HR> 

Moving Data

* dump 파일 생성하기

export/import 실습)

full export/import 실습)

Control file Backup

- Image(snapshot)

  SQL> alter database backup controlfile

       to '_______';

- Text

  SQL> alter database backup controlfile

       to trace

       → USER_DUMP_DEST에 CREATE CONTROLFILE 명령 포함

실습)

* RMAN 백업

* Control file 백업 - DB가 운영 중인 상태에서

Recovery

* Media Recovery

• Complete Recovery 완전 복구
• incomplete Recovery 불완전 복구

• Noarchive Log Mode

• Archive Log Mode

다중화 Redo Log 복구 실습)

* Control file 추가

1.  alter system set
• control_files = ____________ → 경로/이름 추가. scope=spfile;
2. shutdown (abort X)
3. 기존 control file 이용해서 새 file 복사
4. startup

* 자동 Undo 관련 Parameter (9i 버전 ~)

• undo_management = Auto
• undo_tablespace = Undo Tbs
• undo_retention = 900 (15분) : 유지(ONLINE) 최소 시간

SESSION 3: HR

HR> update employees

  2  set salary=salary*1.1

  3  where employee_id=101;

1 row updated.

* Constraint

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');

ALTER TABLE dept

DROP primary key;  <- 삭제가 되지 않는다.

ALTER TABLE dept

DROP primary key CASCADE;  <- 옵션을 추가해 준다.

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 다시 확인해 본다.

ALTER TABLE dept

ADD primary key(department_id);

ALTER TABLE emp

ADD CONSTRAINT emp_deptid_fk foreign key(department_id)

REFERENCES departments(department_id);  <- 제약 조건을 다시 만들고,

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 다시 확인해 보면, enabled 상태.

INSERT INTO dept

VALUES(10, 'CS', null, null);

UPDATE emp

SET department_id = 300

WHERE employee_id = 176;  <- 두 문장 모두 오류 발생

ALTER TABLE dept

DISABLE primary key;

ALTER TABLE emp

DISABLE CONSTRAINT emp_deptid_fk;  <- 제약 조건을 끌 수 있다.

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN('EMP', 'DEPT');  <- 확인해 보면, 두 제약 조건은 disable 상태.

제약 조건을 disable하고, 테이블에 새로운 데이터를 입력 후 enable할 때 문제가 있으면 enable되지 않는다.

문제가 있는 해당 데이터(중복 등)를 삭제 후 enable 해주어야 한다.

DELETE FROM dept

WHERE department_name = 'CS';

UPDATE emp

SET department_id = 30

WHERE employee_id = 176;  <- 데이터 갱신 후

ALTER TABLE dept

ENABLE primary key;

ALTER TABLE emp

ENABLE CONSTRAINT emp_deptid_fk;  <- 제약 조건 enable

* Cascading Constraints

연쇄 제약 조건

@c:\oraclexe\labs\cre_test1.sql  <- TEST1 테이블 생성

DESC test1;

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name = 'TEST1';

ALTER TABLE test1

DROP COLUMN col2;  <- 삭제가 된다.

ALTER TABLE test1

DROP COLUMN col1;  <- 삭제가 안 된다.

ALTER TABLE test1

DROP COLUMN col1

CASCADE CONSTRAINTS;  <- 옵션 추가하여 삭제.

DROP TABLE test1;  <- 테스트 끝났으니 테이블 삭제.

* DROP TABLE ... PURGE : 영구 삭제

DROP TABLE emp;

SELECT * FROM tab;

SHOW RECYCLEBIN;  <- 휴지통 보기

FLASHBACK TABLE emp TO BEFORE DROP;  <- 복원

DESC emp;

DROP TABLE emp PURGE;  <- 완전 삭제, FLASHBACK 불가.

PURGE RECYCLEBIN;  <- 휴지통 비우기

※ 오라클의 휴지통 특징

- 파일 이동이 없다. 따라서, 속도가 빠르다.

* Temporary Tables : 임시 테이블

- 장바구니 개념

CREATE GLOBAL TEMPORARY TABLE emp_temp1

ON COMMIT DELETE ROWS

AS

SELECT employee_id, salary, department_id

FROM employees;

CREATE GLOBAL TEMPORARY TABLE emp_temp2

ON COMMIT PRESERVE ROWS

AS

SELECT employee_id, salary, department_id

FROM employees;

< 재접속 >

SELECT * FROM emp_temp1;

SELECT * FROM emp_temp2;  <- 테이블에 아무것도 없다.

INSERT INTO emp_temp1

SELECT employee_id, salary, department_id

FROM employees

WHERE department_id = 50;

INSERT INTO emp_temp2

SELECT employee_id, salary, department_id

FROM employees

WHERE department_id = 60;  <- 데이터 삽입

SELECT * FROM emp_temp1;

SELECT * FROM emp_temp2;

COMMIT;

SELECT * FROM emp_temp1;  <- 테이블이 비워진다.

SELECT * FROM emp_temp2;  <- 테이블의 내용이 유지된다.

※ 임시 테이블은 세션 범위를 벗어나지 못한다.

* External Tables : 외부 테이블

- 자주 사용하지는 않지만, 버리지는 못하는 자료를 빼두는 용도로 사용

  ex) 은행 거래내역 등

- Oracle 9i부터 사용

- DML 불가

- 부득이하게 수정이 필요하면, 파일을 직접 수정

실습)

• SQL*Plus에서 권한 주기

> conn / as sysdba

Connected.

SYS> CREATE DIRECTORY dir_1

AS 'c:\oraclexe\test';

Directory created.

SYS> GRANT read, write ON DIRECTORY dir_1 TO hr;

Grant succeeded.

• SQL문 실행

@C:\oraclexe\labs\cre_empxt.sql;

DESC empxt;

SELECT * FROM empxt;

Manipulating Large Data Sets

생략

★ Oracle의 Flashback 기술에 대하여

 - 빠르게 취소하는 기능(commit이 된 것)

 - 과거에는 못 했는데 가능하도록 추가된 기능.

• Flashback Query - 9i ver.~             ┐
• Flashback Versions Query               │→ Undo data 읽기 방식(영구적이지 않음)
• Flashback Transaction Query - Undo SQL ┘
• Flashback Table ($)
• Flashback Data Archive ($) - 11g ver.~
• Flashback Undrop - Recyclebin
• Flashback Database ($)