Practice: Extents and Oracle Blocks, and Space Usage and the High-Water Mark

 

This practice looks at creating locally managed tablespaces and verifying block utilization.

 

 

Assumptions

 

·        An Oracle 9i database is up and running.

·        An HR schema with an employees table.

 

Instructions

 

1.      Create a locally managed tablespace called data5 and create a table called mytable in the tablespace. 

 

SQL> connect / as sysdba

Connected.

SQL> create tablespace data5

  2  datafile 'c:\oracle\oradata\dba9\data05.dbf' size 20m

  3  extent management local

  4  uniform size 1m;

 

Tablespace created.

 

SQL> create table mytable(

  2  id number(4),

  3  name varchar2(30))

  4  tablespace data5;

 

Table created.

 

 

2.      Check block utilization of HR’s tables.

 

SQL> l

  1  select owner, table_name, blocks, empty_blocks

  2  from dba_tables

  3* where owner = 'HR'

SQL> /

 

OWNER      TABLE_NAME           BLOCKS EMPTY_BLOCKS

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

HR         COUNTRIES

HR         DEPARTMENTS               1            0

HR         EMPLOYEES                 3           12

HR         JOBS                      1            0

HR         JOB_HISTORY               1            0

HR         LOCATIONS                 1            0

HR         REGIONS                   1            0

HR         TEMP_EMPS

 

8 rows selected.

 

SQL>

 

 

This completes this practice.

 

Practice: Chaining and Migration of Oracle Blocks

 

This practice works through the steps to detect and fix row migration using the export/import utilities.

 

 

Assumptions

 

·        An Oracle 9i database is up and running.

·        An HR schema with an employees table.

 

Instructions

 

1.      Determine if migration exists on HR’s employees table. 

 

SQL> analyze table hr.employees compute statistics;

 

Table analyzed.

 

SQL> select num_rows, chain_cnt

  2  from dba_tables

  3  where table_name = 'EMPLOYEES';

 

  NUM_ROWS  CHAIN_CNT

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

       107          0

 

SQL>

 

 

2.      Assuming that there was a chain count in the above query, export the table from the database.

 

C:\>exp

 

Export: Release 9.0.1.1.1 - Production on Thu Apr 3 19:56:38 2003

 

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

 

 

Username: hr/hr

 

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production

With the Partitioning option

JServer Release 9.0.1.1.1 - Production

Enter array fetch buffer size: 4096 >

 

Export file: EXPDAT.DMP >

 

(2)U(sers), or (3)T(ables): (2)U > 3

 

Export table data (yes/no): yes >

 

Compress extents (yes/no): yes >

 

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

 

 

About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > employees

 

. . exporting table                      EMPLOYEES        107 rows exported

 

Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

 

Export terminated successfully without warnings.

 

 

3.      Drop the employees table and import it back in again.

 

SQL> drop table hr.employees cascade constraints;

 

Table Dropped

 

SQL> host

 

C:\>imp

 

Import: Release 9.0.1.1.1 - Production on Thu Apr 3 19:25:19 2003

 

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

 

Username: hr/hr

 

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production

With the Partitioning option

JServer Release 9.0.1.1.1 - Production

 

Import file: EXPDAT.DMP >

 

Enter insert buffer size (minimum is 8192) 30720>

 

Export file created by EXPORT:V09.00.01 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

 

List contents of import file only (yes/no): no >

 

Ignore create error due to object existence (yes/no): no >

 

Import grants (yes/no): yes >

 

Import table data (yes/no): yes >

 

Import entire export file (yes/no): no >

Username: hr

 

Enter table(T) or partition(T:P) names. Null list means all tables for user

 

Enter table(T) or partition(T:P) name or . if done: employees

 

Enter table(T) or partition(T:P) name or . if done: .

 

. importing HR's objects into HR

. . importing table                    "EMPLOYEES"        107 rows imported

 

About to enable constraints...

Import terminated successfully without warnings.

 

C:\>exit

 

SQL> select table_name

  2  from dba_tables

  3  where table_name = 'EMPLOYEES';

 

TABLE_NAME

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

EMPLOYEES

 

This completes this practice

Practice: Index Reorganization and Usage Monitoring

 

This practice works with the commands that analyze and gather statistics on indexes.

 

 

Assumptions

 

·        An Oracle 9i database is up and running.

·        An HR schema with an employees table.

 

Instructions

 

1.      Analyze HR’s index emp_name_ix using the validate structure option the gather statistics.  Check the statistics to see if the index need rebuilding then rebuild the index.

 

SQL> analyze index hr.emp_name_ix validate structure;

 

Index analyzed.

 

SQL> select name, (del_lf_rows_len/lf_rows_len) * 100

  2  as wastage

  3  from index_stats;

 

NAME                              WASTAGE

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

EMP_NAME_IX                             0

 

SQL> alter index hr.emp_name_ix rebuild;

 

Index altered.

 

 

2.      Demonstrate the different commands that compute statistics on indexes.

 

SQL> execute dbms_stats.gather_index_stats('HR','loc_country_ix');

 

PL/SQL procedure successfully completed.

 

SQL> alter index hr.loc_country_ix rebuild compute statistics;

 

Index altered.

 

 

3.      Turn on index monitoring for the hr.emp_name_ix index.  Run a query against the base table and validate if the index was used.

 

  1  alter index emp_name_ix

  2* monitoring usage

SQL> /

 

Index altered.

 

SQL> get emphire

  1  select employee_id, first_name, last_name, hire_date

  2  from hr.employees

  3* where hire_date like ('%96')

SQL> /

 

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE

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

        120 Matthew              Weiss                     18-JUL-96

        133 Jason                Mallin                    14-JUN-96

        145 John                 Russell                    01-OCT-96

        156 Janette              King                      30-JAN-96

        157 Patrick              Sully                     04-MAR-96

        158 Allan                McEwen                    01-AUG-96

        174 Ellen                Abel                      11-MAY-96

        184 Nandita              Sarchand                  27-JAN-96

        192 Sarah                Bell                      04-FEB-96

        201 Michael              Hartstein                 17-FEB-96

 

10 rows selected.

 

SQL> select index_name, used

  2  from v$object_usage;

 

INDEX_NAME                     USE

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

EMP_NAME_IX                    NO

 

SQL>

 

This completes this practice

1