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

Pseudo Column (의사 결정 컬럼)

• ROWNUM : Top-N 분석 시 사용
           1부터 시작
  

• ROWID : 물리적 Row 주소값

* ROWID 예시 :

ROWID              EMPLOYEE_ID LAST_NAME

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

AAAETOAAEAAAAELAAL         149 Zlotkey

AAAETOAAEAAAAELAAM         174 Abel

AAAETOAAEAAAAELAAN         176 Taylor

AAAETOAAEAAAAELAAO         178 Grant

AAAETOAAEAAAAELAAP         200 Whalen

AAAETOAAEAAAAELAAQ         201 Hartstein

AAAETOAAEAAAAELAAR         202 Fay

AAAETOAAEAAAAELAAS         205 Higgins

AAAETOAAEAAAAELAAT         206 Gietz

※ 6 3 6 3으로 끊으면 된다.

AAAEY/   AAE   AAAAFb   AAT

  6       3       6      3

Object# Field# Block#  Row#

select min(rowid), employee_id, last_name

from t1

group by employee_id, last_name

order by 2;

※ group by절에는 group function을 제외한 나머지 컬럼이 반드시 들어가야 한다.

* 중복을 제거!

방법 1 :

delete from t1

where rowid not in (select(min(rowid) from t1

                    group by employee_id);

방법 2 : 별칭을 주어 rowid 최소값보다 큰 것들을 제거

delete from t1 a

where rowid > (select min(rowid) from t1 b

               where a.employee_id = b.employee_id);

* mission! 급여 상위 6 ~ 10위를 추출하라.

SYS> conn hr/hr

Connected.

HR> create table emp as select * from employees;

Table created.

HR> create index emp_id_no on emp(employee_id);

Index created.

HR> insert into emp select * from emp;

20 rows created.

HR> /

40 rows created.

HR> /

80 rows created.

HR> /

160 rows created.

HR> /

320 rows created.

HR> /

640 rows created.

HR> /

1280 rows created.

HR> /

2560 rows created.

HR> /

5120 rows created.

HR> /

10240 rows created.

HR> /

20480 rows created.

HR> /

40960 rows created.

HR> commit;

Commit complete.

HR> select sum(blocks) from user_segments where segment_name = 'EMP';

SUM(BLOCKS)

-----------

        896

HR> delete from emp;

81920 rows deleted.

HR> insert into emp select * from employees;

20 rows created.

HR> commit;

Commit complete.

HR> alter table emp shrink space check;

alter table emp shrink space check

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled

HR> select table_name, row_movement from user_tables where table_name = 'EMP';

TABLE_NAME                     ROW_MOVE

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

EMP                            DISABLED

HR> alter table emp enable row movement;

Table altered.

HR> select table_name, row_movement from user_tables where table_name = 'EMP';

TABLE_NAME                     ROW_MOVE

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

EMP                            ENABLED

HR> alter table emp shrink space check;

alter table emp shrink space check

*

ERROR at line 1:

ORA-10655: Segment can be shrunk

HR> alter table emp shrink space compact;

Table altered.

HR> alter table emp shrink space;

Table altered.

HR> select sum(blocks) from user_segments where segment_name = 'EMP';

SUM(BLOCKS)

-----------

          8

HR> create table rp_emp

  2  partition by range(department_id) (

  3  partition p_10 values less than (30) tablespace new_users,

  4  partition p_30 values less than (60) tablespace users,

  5  partition p_50 values less than (90) tablespace new_users,

  6  partition p_70 values less than (maxvalue) tablespace users)

  7  as        

  8  select * from employees;

Table created.

HR> insert into rp_emp   

  2  select * from rp_emp;

20 rows created.

HR> /

40 rows created.

HR> /

80 rows created.

HR> /

160 rows created.

HR> commit;

Commit complete.

HR> create table hp_emp

  2  partition by hash(employee_id) partitions 4

  3  store in (users, new_users)

  4  as select * from employees;

Table created.

HR> insert into hp_emp

  2  select * from hp_emp;

20 rows created.

HR> /

40 rows created.

HR> /

80 rows created.

HR> /

160 rows created.

HR> /

320 rows created.

HR> commit;

Commit complete.

HR> select table_name, partition_name from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME

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

RP_EMP                         P_70

RP_EMP                         P_50

RP_EMP                         P_30

RP_EMP                         P_10

HP_EMP                         SYS_P23

HP_EMP                         SYS_P24

HP_EMP                         SYS_P22

HP_EMP                         SYS_P21

8 rows selected.

HR> select count(*) from rp_emp partition (p_10);

  COUNT(*)

----------

        48

HR> select count(*) from rp_emp partition (p_30);

  COUNT(*)

----------

        80

HR> select count(*) from rp_emp partition (p_50);

  COUNT(*)

----------

        96

HR> select count(*) from rp_emp partition (p_70);

  COUNT(*)

----------

        96

HR> select count(*) from rp_emp partition (sys_p21);

select count(*) from rp_emp partition (sys_p21)

                                       *

ERROR at line 1:

ORA-02149: Specified partition does not exist

HR> select count(*) from hp_emp partition (sys_p21);

  COUNT(*)

----------

        96

HR> select count(*) from hp_emp partition (sys_p22);

  COUNT(*)

----------

       128

HR> select count(*) from hp_emp partition (sys_p23);

  COUNT(*)

----------

       256

HR> select count(*) from hp_emp partition (sys_p24);

  COUNT(*)

----------

       160

HR> conn / as sysdba 

Connected.

SYS> select tp.endian_format

  2  from v$transportable_platform tp, v$database d

  3  where tp.platform_name = d.platform_name;

ENDIAN_FORMAT

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

Little

Cluster Table 실습

HR> conn hr/hr

Connected.

HR> create cluster emp_dept_clu(department_id number(4))

  2  tablespace users;

Cluster created.

HR> create index emp_dept_clu_ind

  2  on cluster emp_dept_clu

  3  tablespace users;

Index created.

HR> create table dept_clu cluster emp_dept_clu(department_id)

  2  as select * from departments;

Table created.

HR> create table emp_clu cluster emp_dept_clu(department_id)    

  2  as select * from departments;

Table created.

HR> drop table emp_clu;

Table dropped.

HR> create table emp_clu cluster emp_dept_clu(department_id)

  2  as select * from employees;

Table created.

HR> select rowid, department_id from dept_clu

  2  where department_id = 50;

ROWID              DEPARTMENT_ID

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

AAAETCAAEAAAADPAAA            50

HR> select rowid, employee_id, department_id from emp_clu

  2  where department_id = 50;

ROWID              EMPLOYEE_ID DEPARTMENT_ID

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

AAAETCAAEAAAADPAAA         124            50

AAAETCAAEAAAADPAAB         141            50

AAAETCAAEAAAADPAAC         142            50

AAAETCAAEAAAADPAAD         143            50

AAAETCAAEAAAADPAAE         144            50

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.