[11/13] 실습 1

orcl@HR> create table idx1(id number(10), name char(2000), day date);

Table created.

Elapsed: 00:00:00.06

orcl@HR> create index idx1_indx on idx1(name);

Index created.

Elapsed: 00:00:00.03

orcl@HR> insert into idx1 values (1,'ABC',sysdate);

1 row created.

Elapsed: 00:00:00.02

orcl@HR> insert into idx1 values (2,'DEF',sysdate);

1 row created.

Elapsed: 00:00:00.00

orcl@HR> insert into idx1 values (3,'GHI',sysdate);

1 row created.

Elapsed: 00:00:00.00

orcl@HR> insert into idx1 values (4,'JKL',sysdate);

1 row created.

Elapsed: 00:00:00.01

orcl@HR> insert into idx1 values (5,'MNO',sysdate);

1 row created.

Elapsed: 00:00:00.01

orcl@HR> commit;

Commit complete.

Elapsed: 00:00:00.00

orcl@HR> select object_name, object_id from user_objects

  2  where object_name='IDX1_INDX';

OBJECT_NAME                                                                      OBJECT_ID

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

IDX1_INDX                                                                            98100

Elapsed: 00:00:00.03

orcl@HR> alter session set tracefile_identifier='ocmkorea';

Session altered.

Elapsed: 00:00:00.00

orcl@HR> alter session set events 'immediate trace name treedump level 98100';

Session altered.

Elapsed: 00:00:00.08

orcl@HR> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@lnx02 sqltune]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

[oracle@lnx02 trace]$ ls -al | grep ocm

-rw-r-----  1 oracle oinstall      1022 Nov 13 15:11 orcl_ora_15820_ocmkorea.trc

-rw-r-----  1 oracle oinstall        60 Nov 13 15:11 orcl_ora_15820_ocmkorea.trm

[oracle@lnx02 trace]$ vi orcl_ora_15820_ocmkorea.trc

----- begin tree dump

branch: 0x453309 4535049 (0: nrow: 2, level: 1)

   leaf: 0x45330a 4535050 (-1: nrow: 3 rrow: 3)

   leaf: 0x45330b 4535051 (0: nrow: 2 rrow: 2)

----- end tree dump

[oracle@lnx02 trace]$ vi orcl_ora_15820_ocmkorea.trc

[oracle@lnx02 trace]$ cd

[oracle@lnx02 ~]$ ss

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 13 15:15:54 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

orcl@SYS> !vi indx.sql

set feedback off 

set serveroutput on 

 

declare 

         l_dba   number := to_number ('&dba','XXXXXXXX'); 

         l_file  number := dbms_utility.data_block_address_file (l_dba); 

         l_block number := dbms_utility.data_block_address_block (l_dba); 

begin 

         dbms_output.put_line ('alter system dump datafile '||l_file||'block '||l_block||';'); 

end; 

/ 

 

set feedback on 

orcl@SYS> @indx.sql

Enter value for dba: 98100

old   2:          l_dba   number := to_number ('&dba','XXXXXXXX');

new   2:          l_dba   number := to_number ('98100','XXXXXXXX');

alter system dump datafile 0block 622848;

Elapsed: 00:00:00.01

orcl@SYS> alter system dump datafile 0 block 622848;

System altered.

Elapsed: 00:00:00.07