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

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.

정규표현식

* 메타 문자

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

SELECT * FROM t1;

q1.sql >

SELECT fname, lname

FROM t1

WHERE REGEXP_LIKE (fname, '^Ste(v|ph)en$');

q2.sql >

SELECT fname, phone

FROM t1

WHERE REGEXP_LIKE (phone, '...\...\.....\.......');

- . : 정규표현식에서는 문자 하나를 대신함.

- \ : 예외 처리

q3.sql >

SELECT fname, phone

FROM t1

WHERE REGEXP_LIKE (phone, '[0-9]{3}\.[0-9]{2}\.[0-9]{4}\.[0-9]{6}');

- [] : 문자(숫자)의 범위

- {} : 자리수

q4.sql >

SELECT fname, phone, REGEXP_REPLACE ( phone , '\.' , '-' ) new_format

FROM t1;

- REGEXP_REPLACE : 다른 문자로 대체

q5.sql >

SELECT fname, phone,

REGEXP_REPLACE (phone, '(\d{3})\.(\d{3})\.(\d{4})','(\1)-\2-\3') new_phone

FROM t1;

- \d : 숫자(digit). [0-9]와 같은 의미.

- 뒤의 \1, 2, 3 : 그룹을 지정, '\'는 앞의 표현식과 일치하기 위해 사용.

q6.sql >

SELECT fname, addr,

REGEXP_INSTR ( addr, '[[:alpha:]]' ) pos ,

phone,

REGEXP_INSTR ( phone, '[[:alpha:]]') pos

FROM t1;

- [:alpha:] : 범주를 지정(alpha: 알파벳, digit: 숫자)해서 해당되는 부분이 나타나는 자리수를 찍어줌.

              해당되는 것이 없으면 0.

q7.sql >

SELECT fname, addr,

REGEXP_SUBSTR ( addr, ' [^ ]+ ') road

FROM t1;

- REGEXP_SUBSTR : 첫 번째 나오는 문자를 검색한다.

- [^#] : #가 아닌, 부정의 의미.

- + : 한 개 이상.

- ' [^ ]+ ' : 공백과 공백 사이를 추출하라.

q8.sql >

SELECT fname, phone,

REPLACE(REGEXP_SUBSTR(phone,'\.(\d{3})\.'),'.') code

FROM t1;

- 정규표현식도 중첩이 가능하다.

q9.sql >

SELECT fname, addr,

REGEXP_COUNT(addr,'a') cnt

FROM t1;

- REGEXP_COUNT : 표현된 횟수

q11.sql >

SELECT REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 1 ) "Exp1" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 2 ) "Exp2" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 3 ) "Exp3" ,

REGEXP_SUBSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 'i', 4 ) "Exp4"

FROM dual;

- 순서 : 123 → 45678 → 56 → 78

q10.sql >

SELECT REGEXP_INSTR ('0123456789',

'(123)(4(56)(78))', 1, 1, 0, 'i', 2 ) "Position"

FROM dual;

- 자리값

q12.sql >

SELECT

REGEXP_INSTR('ccacctttccctccactcctcacgttctcacctgtaaagcgtccctc

cctcatccccatgcccccttaccctgcagggtagagtaggctagaaaccagagagctccaagc

tccatctgtggagaggtgccatccttgggctgcagagagaggagaatttgccccaaagctgcc

tgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagttttca

ccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagag

gctcttgggtctgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggc

atgtaggggcgtggggatgcgctctgctctgctctcctctcctgaacccctgaaccctctggc

taccccagagcacttagagccag',

'(gtc(tcac)(aaag))',

1, 1, 0, 'i',

1) "Position"

FROM dual;

- 쿼리문은 DNA 유전자 배열

- 특별한 배열을 찾을 때 사용

q14.sql >

SELECT REGEXP_COUNT(

'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcag

ggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggag

aatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagtt

ttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtc

tgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctg

ctctgctctcctctcctgaacccctgaaccctctggctaccccagagcacttagagccag',

'gtc') AS Count

FROM dual;

- 'gtc'가 몇 번 나오는지 count

* 기타 메타 문자들

- + : 1번 이상

- ? : 0번 또는 1번

- * : 0번 이상

- {m, } : m 이상

- {m, n} : m 이상 ~ n 이하

- ( ... ) : 표현식을 묶어줄 때 사용

- $ : 끝

- ^ : 시작

- \ : escape

- \n : 선행하는 표현식의 위치

- \d : 숫자

- [:class:] : class 종류를 지정

- [^:class:] : class가 아닌 것

SQL 끝. DB Admin 시작!

플래시백

conn / as sysdba

SELECT * FROM v$option;

WHERE parameter like 'Flashback%';  ← 플래시백 유료 옵션 확인하기

* 플래시백 실습

conn hr/hr

UPDATE employees

SET salary = salary*1.5

WHERE employee_id = 141;

commit;

SELECT salary FROM employees

WHERE employee_id = 141;

=> 롤백은 안 되지만, undo 파일에 남아있을 것임.