Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

BFT ROWID Format

Oracle RAC Cluster Tips by Burleson Consulting

This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters.  To get immediate access to the code depot of working RAC scripts, buy it directly from the publisher and save more than 30%.


In a BFT, there is only one file that always has a relative file number of 1024.  A BFT has a three piece format, OOOOOOLLLLLLLLLRRR, in which LLLLLLLLL is used to denote the block number.  The concatenation of FFF and BBBBBB makes the encoded block number.

For BFTs, the only supported way of getting components of extended rowids is to use the dbms_rowid package.

The following is an example on how to use the dbms_rowid package to retrieve rowid information:

select dbms_rowid.rowid_relative_fno(rowid, 'BIGFILE') 
       bigfile_rowid,
       dbms_rowid.rowid_relative_fno(rowid, 'SMALLFILE')
       smallfile_rowid,
       first_name, last_name
FROM   hr.employees where  rownum < 3;

BIGFILE_ROWID SMALLFILE_ROWID FIRST_NAME           LAST_NAME
------------- --------------- -------------------- -------------
         1024               4 Mike                 Ault
         1024               4 Madhu                Tumma

Data Dictionary Views Enhancement

A new column is added to both dba_tablespaces and v$tablespace views to indicate whether a particular tablespace is bigfile or smallfile:

SQL> select name, bigfile
     from v$tablespace;

NAME                           BIGFILE
------------------------------ -------
SYSTEM                         NO
UNDOTBS01                      NO
SYSAUX                         NO
TEMP                           NO
EXAMPLE                        NO
USERS                          NO
BIG_TBS                        YES

SQL> select tablespace_name,bigfile
     from   dba_tablespaces;

TABLESPACE_NAME                BIGFILE
------------------------------ ---------
SYSTEM                         SMALLFILE
UNDOTBS01                      SMALLFILE
SYSAUX                         SMALLFILE
TEMP                           SMALLFILE
EXAMPLE                        SMALLFILE
USERS                          SMALLFILE
BIG_TBS01                      BIGFILE

Examples

The following creates a database with default bigfile tablespace:

CREATE DATABASE GRID
SET DEFAULT BIGFILE TABLESPACE
DATAFILE ?/u02/oradata/grid/system01.dbf? SIZE 500 M,
SYSAUX DATA FILE ?/u02/oradata/grid/sysaux01.dbf? SIZE 500 M
DEFAULT TEMPORARY TABLESPACE tbs01
TEMPFILE ?/u02/oradata/grid/temp01.dbf? SIZE 1024 M
UNDO TABLESPACE undo01
DATAFILE ?/u02/oradata/grid/undo01.dbf? SIZE 1024 M;

The following shows how to move data between smallfile and bigfile tablespaces:

ALTER TABLE employee MOVE TABLESPACE bigfile_tbs;

The following shows how to create a BFT and change its size:

CREATE BIGFILE TABLESPACE user_tbs
DATAFILE ?/u02/oradata/grid/user_tbs01.dbf? SIZE 1024 M;

ALTER TABLESPACE user_tbs RESIZE 10G;

In the previous release of Oracle server, K and M were used to specify storage size. In this DDL statement, a user can specify size in gigabytes and terabytes using G and T respectively.

The following example uses DBVERIFY utility with bigfile. With SFT, multiple instances of DBVERIFY can be run in parallel on multiple datafiles to speed up integrity checking for a tablespace.  Integrity checking parallelism can be achieved with BFTs by starting multiple instances of DBVERIFY on parts of the single big file.

$dbv FILE=bigfile01.dbf  START=1 END=10000
$dbv FILE=bigfile01.dbf  START=10001

Note: START = Start Block; END = End Block

Data Pump Utilities

Data Pump is a new utility in Oracle 10g that enables very high-speed metadata and data loading and unloading from one database to another. The Data Pump Export and Import utilities are restartable and expdp and impdp have a similar look and feel to the original Export and Import utilities, exp and imp. However, they use a new mechanism that provides a dramatic increase in performance as well as new features and functions.

Data Pump Overview

The Oracle Data Pump utility ? API is implemented through a PL/SQL package, dbms_datapump.  The Data Pump makes use of the direct path load and the external table mechanisms for data movement.

The Major functional components of Data Pump are as follows:

* dbms_datapump: This package embodies the API for high-speed export and import utilities for bulk data and metadata movement.

* Direct Path API: This supports a stream interface in addition to its existing column array interface.  Row data is read or written to dump file sets as a Direct Path API stream.  The Direct Path API also minimizes data conversion and parsing at both unload and load time.

* dbms_metadata: This package is used by worker process for all metadata, database object definitions, loading and unloading.  The metadata is extracted and written to the dump file set as XML documents rather than as SQL Data Definition Language (DDL) in the original export utility.  The XML format brings great flexibility when creating the DDL during import time.  For example, the DBA can easily change an object?s ownership and default tablespace during an import operation. However, the XML format takes up more dump file space than DDL.

* External Table API: The Data Pump uses Direct Path API to load and unload when a table?s structure allows it.  However, when Direct Path API cannot be used, the Data Pump uses External Table API to move data. Oracle External Table API has two access drivers. The oracle_loader access driver provides external tables read-only access to SQL Loader compatible files.  The oracle_datapump driver also provides external tables write and read access to data file set.

The Direct Path API is much faster than the External Table API.  However, Direct Path does not support the following structures for loading and unloading data:

* Partitioned tables on which global index exists during a single-partition load.

* LOB column for which domain index exists.

* Clustered tables.

* Tables with active triggers.

* Tables with fine-grained access control enabled in insert and select modes.

* Table that contains BFILE or opaque type columns.

* Structures on which a referential integrity constraint is present.

* Table that contains VARRAY columns with an embedded opaque type.

The Figure 17.1 shows a broad outline of the utilities and components of Data Pump.

Figure 17.1: Data Pump General Architecture

 


This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters, Rampant TechPress, by Mike Ault and Madhu Tumma.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

http://www.rampant-books.com/book_2004_1_10g_grid.htm


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational