EZ Guide to Oracle Data Pump

Oracle Data Pump (DP) was introduced in 10g to replace the legendary export / import utility (Still available). The Data Pump although takes more time to start is more flexible and manageable and provides greater performance.

Data Pump consist of the following components:

Data Pump API: DBMS_DATAPUMP (creates and monitors DP Jobs).
Metadata API : DBMS_METADATA (Provides objects definitions to DP).
Client Tool: expdp and impdp (Makes calls to DBMS_DATAPUMP).
Data movement API: Uses direct path API (DPAPI) to move data when possible.

The first you need to do is to setup a location (Oracle Directory) where the DP files will be stored.

SQL> create directory dumplocation as '/oradata/dumpfiles';
SQL> grant read, write on directory dumplocation to scott;

In addition a default directory can be created for data pump operations in the database as follows (once created privileged users with EXP_FULL_DATABASE or IMP_FULL_DATABASE privilege do not need to specify a directory):

SQL> create directory DATA_PUMP_DIR as '/oradata/dumpfiles';

Note: The name of the directory must be DATA_PUMP_DIR.

There are 5 different types of DP import and exports:

1. Database (The default performed by specifying the FULL=Y parameter)
2. Tablespaces (Performed by specifying the TABLESPACES parameter)
3. Schema (Performed by specifying the SCHEMAS parameter)
4. Table (Performed by specifying the TABLES parameter)
5. Transportable Tablespace (Performed by specifying the TRANSPORT_TABLESPACES parameter)

EXPDP:

$ expdp directory=dumplocation
..
..
Username: scott/tiger
..
..
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:49
$

Since we did not specify any other parameters, the expdp used default values for the file names (expdat.dmp, export.log), did schema-level export (login schema), calculated job estimation using blocks method, used default job name (SYS_EXPORT_SCHEMA_01), and exported both data and metadata.

List of the expdp parameters


Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
COMPRESSION Reduce size of dumpfile contents where valid
keyword values are: (METADATA_ONLY) and NONE.
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD Password key for creating encrypted column data.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SAMPLE Percentage of data to be exported;
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
FILESIZE Default filesize (bytes) for subsequent ADD_FILE commands.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS[=interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.

IMPDP

$ impdp dumpfile=expdat.dmp
..
username: scott/tiger
..
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 07:09

List of impdp Parameters

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to load where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dump, log, and sql files.
DUMPFILE List of dumpfiles to import from (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD Password key for accessing encrypted column data.
This parameter is not valid for network import jobs.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Import everything from source (Y).
HELP Display help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of import job to create.
LOGFILE Log file name (import.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile.
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to import a subset of a table.
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
REUSE_DATAFILES Tablespace will be initialized if it already exists (N).
SCHEMAS List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE Write all the SQL DDL to a specified file.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES Identifies a list of tables to import.
TABLESPACES Identifies a list of tablespaces to import.
TRANSFORM Metadata transform to apply to applicable objects.
Valid transform keywords: SEGMENT_ATTRIBUTES, STORAGE
OID, and PCTSPACE.
TRANSPORT_DATAFILES List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

Table Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

I was debating if adding this or not to this document because this suppose to be an EZ guide, but since these are my 2 favorite Data Pump features I decided to add it.

Few years ago you needed to wait for a long import / export to finish before going home and logout of you account or turn off your desktop. Or simple you got an alectric failure while the import was taking place and now you have to clean up and start all over (I have been there and done that, it is very frustrating).

Attach and Detach from a session

The first you need to know is that the core of the DP import or export operation is taking place using a database job, so it does not really depend of your client connection, cool isn't it?. In
other words your job will continue to run even if the client disconnects.

DP works in 2 modes, logging similar to the old import / export and interactive mode. To enter into the interactive mode just control-C while the import or export is taking place.

In the example below I pressed Control-C as soon as the export started.

oracle@mtl01dbatlora03:/opt/oracle/product/10.2.0.1/rdbms/log> expdp system/password

Export: Release 10.2.0.3.0 - Production on Friday, 07 March, 2008 11:31:24

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.3.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Export> status

Job: SYS_EXPORT_SCHEMA_01
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /opt/oracle/product/10.2.0.1/rdbms/log/expdat.dmp
bytes written: 4,096

Worker 1 Status:
State: EXECUTING
Object Schema: SYSTEM
Object Name: TEMPLATE$_TARGETS_S
Object Type: SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Completed Objects: 18
Total Objects: 18
Worker Parallelism: 1

Export> exit_client

Then to re-attach all I have to do is

expdp system/password attach=SYS_EXPORT_SCHEMA_01

export>

and I will be on my way, if I decide to continue in logging mode all I next to do is

export> continue_client

The other cool feature of Data Pump is that in most of the cases you can stop the job do anything you forgot in the database (like creating a specific tablespace) and then re-start it. ``

Below is a table of some of the commands you can execute while in interactive mode.

PHOUG Presentation by Tim Gorman

I had the opportunity tonight of assisted to the PHOUG meeting which included a presentation by Tim Gorman about "Scaling to Infinity: Partitioning Data Warehousing in Oracle".

I have to be honest this is the first time I hear about Tim, but he really surprised me, what a great presentation and presenter. I can see Tim have a lot of Data Warehousing experience and enjoy sharing his knowledge. I will try to summarize below the points that Tim covered.

Tim sees 3 Mayor errors on DW design.

A) Ignore the Basic Requirements for DW and design what is familiar
B) Fail to portray data changes over time.
C) Fail to Utilize partitioning from the beginning.

- Reporting and analysis applications are responsible for presenting data in the format that works best for end users and their query / analysis tools (very ofter what end users want is a simple spreadsheet, hundreds of columns wide.

- Reporting and analysis applications do not enforce business rules.

- Do not build a data model to enforce referential integrity and / or business rules. Normalization is intended for use in process oriented operational systems and not in a DW.

- Provide very simple data representation, with one degree of normalization for flexibility.

Time-variant data, who cares?

There are two major types of queries from business intelligence appliances to the data warehouse databases. Point in time (What is the present situation) and trend analysis (How things looks now versus 3 months ago). Dimension tables are usually designed to be point int time while fact tables are more trend analysis.

Every Data Warehouse has at least one slowly-changing dimension usually involving people.

The Virtuous Cycle

Using EXCHANGE PARTITION for loads enables:
- Elimination of ETL load windows and 24x7 availability for queries.
- Direct path loads
- Bitmap indexes and bitmap-join indexes.

Exchange partition is the basic technique of a bulk-loading new data into a temporary "Load table" which is then indexed , analyzed and then "published" all at once to the end user using the EXCHANGE PARTITION operation.

Final Recommendations

1.- Use dimensional data models for the "presentation" to end users.
2.- Base the database design on time-variant data structures.
3.- Use partitioning

Notes from Oracle RAC Customer Summit

Hello Everybody

I had the opportunity this week of assisting to a Real Application Cluster Customer Summit in the new Oracle office of Bridgewater NJ. In the meeting was one of the product managers for Real application Cluster, members of he RACPACK and representation from Oracle Consulting.

I was a very interesting meeting where Oracle was basically asking their non RAC customers what can we do for you to start using it, and to the current customers what can we do for you be more comfortable using it.

They did emphasis on virtualization and the hardware becoming cheaper, the different ways that you can virtualize (server split (VMWARE) or server aggregations (GRID)). They went through the advantages and disadvantages of each one, in summary server split does not offer business continuity, scalability across servers, performance (high overhead) and reduction in the management burden and with the prices of the hardware and they becoming more powerful it almost a no brainier to use grid.

However Server split virtualization has its niche, It is good for systems with smaller workloads, test, development environments and non critical applications where you can justify the price tag involved with using RAC. while GRID works best for larger workloads and business critical applications.

Grid aims to provide dynamic resource provisioning and automation, For Oracle grid is evolving, the first version of grid (GRID 1.0) is the typical 2 nodes cluster database, once their customers start feeling comfortable with the and it's potential they wan to leverage the full potential of grid and evolve into what they call GRID 2.0. Oracle is looking into incorporate to it policy based service level automation.

In the meeting Oracle also spoke about ASM and Clusterware giving us some tips:

- They say that SAN based redundancy is commonly used but when that is not available ASM is a good option.
- They clarified that contrary to what people think ASM stripping in conjunction with SAN stripping does not hurt performance.
- They said there is a benefit in using ASM redundancy when using extended clusters (Clusters separated geographically).
- When using ASM 2 different LUNS should not map back to the same spindle.
- LUN provisioning should be done in a complimentary manner to ASM.
- Redundancy for private network is highly recommended, most customers use a pair of dedicated switches for it.
- Jumbo frames can be used to reduce the private network traffic and CPU overhead (1500 bps to 9k).
- They recommend to use workload management by service and to partition similar workloads by service.

Some Oracle 10g Tuning tips

Some Oracle 10g Tuning tips

AWR

In Oracle10g the Automatic Workload repository is the primary component of Common Manageability Infrastructure (CMI). It is the successor for the Statspack. While the Statspack is still available with 10g, just a few improvements are planned to it; therefore AWR is the preferred mechanism for diagnosing database problems.

AWR provides data used by other CMI components, such as system generated alerts and the advisors. The AWR consist of 2 main components:

- In-memory statistics

- Repository snapshot.

In-memory statistics are gathered once a second on active sessions. They are not written to the database and are aged out of memory as new statistics are generated. In memory statistics are accessed through the view V$ACTIVE_SESSION_HISTORY which queries the ASH (Active Session History) circular buffer area of the SGA (System Global Area)

AWR relies on MMON which wakes up every hour and statistics collection into the repository snapshot (you can modify the collection interval), AWR snapshot provides a persistent view of database statistics.

A script is provided ($ORACLE_HOME/rdbms/admin/awrrpt.sql) to generate a report using repository snapshot. There is also an awrrpti.sql report as well, which has essentially the same output but allows you to define and report on a specific instance.

ADDM

The Automatic Database Diagnostic Monitor (ADDM) uses the snapshot information of AWR to automatically identify performance problems and make recommendations to correct them. ADDM is the predecessor of the Oracle expert tool. ADDM functionality is embedded into the kernel making performance impact near negligible. ADDM analysis is performed every time an AWR snapshot is taken. in addition ADDM can automatically fix certain problems.

To allow use of ADDM, a PL/SQL interface called DBMS_ADVISOR has been provided. This interface may be called directly through the supplied script $ORACLE_HOME/rdbms/admin/addmrpt.sql. Besides this PL/SQL package a number of views allow you to retrieve the results of any actions performed with the DBMS_ADVISOR API.

When the parameter STATISTICS_LEVEL is set to TYPICAL or ALL, the database will automatically schedule the AWR to be populated every 60 min. To create snapshots out of this interval you need to use the dbms_workload_repository.create_snapshot () package.

To be useful in diagnosing a particular problem, the snapshots needs to be created before and after the situation you wish to examine.

The following views are available for querying ADDM information.

View	Description
DBA_ADVISOR_ACTIONS	Actions associated with all recommendations
DBA_ADVISOR_COMMANDS	Commands used by advisor in DB for specifying recommended actions
DBA_ADVISOR_DEF_PARAMETERS	Parameters and default values for all tasks in the database.
DBA_ADVISOR_DEFINITIONS	Properties of all the advisors in the database
DBA_ADVISOR_DIRECTIVES	Not documented
DBA_ADVISOR_FINDINGS	Findings discovered by all advisors
DBA_ADVISOR_JOURNAL	Journal entries for all tasks
DBA_ADVISOR_LOG	Current state of all tasks along with execution specific data such as progress monitoring and completion status
DBA_ADVISOR_OBJECT_TYPES	Object types used by all advisors
DBA_ADVISOR_OBJECTS	Objects currently referenced by all advisors
DBA_ADVISOR_PARAMETERS	Parameters and their current value for all tasks
DBA_ADVISOR_PARAMETERS_PROJ	Not documented
DBA_ADVISOR_RATIONALE	Rationales for all recommendations
DBA_ADVISOR_RECOMMENDATION	Result of complemented diagnostics tasks with action recommendation for the problems identified in each run
DBA_ADVISOR_SQLA_REC_SUM	Recommendation rollup information for all workload objects after analysis
DBA_ADVISOR_SQLA_WK_MAP	Workload references for all tasks
DBA_ADVISOR_SQLA_WK_STMTS	Workload objects after analysis
DBA_ADVISOR_SQLW_COLVOL	Not documented
DBA_ADVISOR_SQLW_JOURNAL	Journal entries for all workload
DBA_ADVISOR_SQLW_PARAMETERS	Workload parameters and their current values
DBA_ADVISOR_SQLW_STMTS	Rows that correspond to all statements in the workload
DBA_ADVISOR_SQLW_SUM	Aggregate picture of all SQL workload
DBA_ADVISOR_SQLW_TABLES	Cross-references between workload statements and tables references on it.
DBA_ADVISOR_SQLW_TABVOL	Not documented
DBA_ADVISOR_SQLW_TEMPLATES	Aggregate picture of all SQL workload template objects
DBA_ADVISOR_TASKS	Information about all tasks in the DB
DBA_ADVISOR_TEMPLATES	Information about all templates in the DB
DBA_ADVISOR_USAGE	Information for each type of advisor

SQL Tuning Advisor

This is feature automates the entire SQL tuning process. It analyzes the SQL statements and executes a complete analysis of the statements.

The advisor can be administered with the procedures in the DBMS_SQLTUNE package. To use the API the user must be granted DBA role, SELECT_CATALOG_ROLE role and the ADVISOR privilege.

Running SQL Tuning advisor using DBMS_SQLTUNE package is a two-step process. First you create a tuning task and then you execute the task. The CREATE_TUNING_TASK function returns the task name that you have provided or generates a unique task name. You can use the task name to specify this task when using other API’s.

Check for chaining rows

To see if you have chaining problems, run utlchain.sql script that Oracle provides and is in /$ORACLE_HOME/rdbms/admin subdirectory. You should check for chaining on a weekly basis and fix any problems immediately.

Analyze table customers; List chained rows;

Then run the following query accessing the CHAINED_ROWS table to check the customer table chaining:

Select HEAD_ROWID from CHAINED_ROWS where TABLE_NAME = ‘CUSTOMER’;

If no rows are returned, then you don’t have a chaining problem. If there is a chaining problem then the query will return the head_rowid for all chained rows. You can also use the “count(*)” against the CHAINED_ROWS table to find the number of chained rows. In V$SYSSTAT, the “table fetch continue row” is an indicator of chained rows as well.

To avoid row chaining, set PCTFREE correctly, the default is set to 10.

Increasing the log file size and LOG_CHECKPOINT_INTERVAL for speed

If you want to speed up large numbers of INSERTs, UPDATEs, and DELETEs, increase the sizes of your log files and make sure they are on the fastest disk. Previously, you could also increase the LOG_CHECKPOINT_INTERVAL if it was set such that it would checkpoint prior to log switch, but this parameter is being deprecated and currently is set to zero (which indicates switching based on redo log being full).

Determining if redo log file size is a problem

There could be 2 problems with the size of the redo log files:

A) Batch jobs that do not have enough total redo space to complete or are so fast that the online redo logs wrap (cycle through all the logs and start writing to the first one again) before they are archived to the offline redo logs.

B) Very long running jobs that are spending a large amount of time switching online redo logs. Long running jobs are often much faster when the entire job fits into a single online redo log. For the online transaction processing (OLTP) type of environment, smaller online redo log are usually better. Optimal online redo log switch should happen every half hour (no counting the long running batch jobs that shorten this time)

Use the query below to determinate if there is a problem, it shows the time between log switches.

SQL> l select b.recid, to_char(b.first_time,'DD-MON-YY HH:MI:SS') start_time, a.recid, to_char(a.first_time,'DD-MON-YY HH:MI:SS') end_time, round(((a.first_time - b.first_time)*25)*60,2) minutes from v$log_history a, v$log_history b where a.recid = b.recid+1 order by a.first_time a

Determining the size of your log files and checkpoint interval

SQL> l 1 select a.member, b.* 2 from v$logfile a, v$log b 3* where a.group# = b.group#

Discussion of the Oracle Global Executive Survey

A couple of weeks ago I got a survey from Oracle where you rated several divisions of the company separated (Support, Consulting, Education, Sales, etc). I filled it and this morning I found an invitation in my Inbox from Keith Block (Oracle Executive Vice President) thanking me for filling the survey and inviting me for a conference call at 11am today where he was going to to talk about the result of the survey and what Oracle plans to do.

They received 7000 surveys, Keith invited several of the managers from the different organizations for them to explain what are they doing with the results of it on each area. Below are outlines the main points per organization.

Support

- They improve and use more the collaboration tool and offer a more personalized support experience.

- They are encouraging customers to use their configuration manager feature.

- They are building support capabilities inside the product in order to have proactive diagnostic.

Development

Here they focused only in application development.

- Lifetime support policy for the applications.
- They are working so you can monitor all your different application from Grid Control.
- They are working on having a common reporting tool for all your applications.
- They are focusing in tasks that aim to reduce the cost of ownership of their customers.
- They are architecting the integration between the different applications, the idea is for the
delivery of this to be a prepackaged integration product.
- They spoke about the Oracle Fusion application and how they expect it to be a plug-in to any existing application you may have.

Relationships

Apparently several customers have complained because they have to deal with many different Oracle reps for the different technologies, Keith acknowledged the issue and explained that there are several sales models, but they opted for having several reps with deep knowledge about the technology they represent. Keith explained that Oracle have a huge portfolio of products and it is impossible for a single person to get to know well all of them. Keith vision is that the sales reps would become trusted advisers inside the customer organization.

Oracle 10g Installation and upgrade tips

Installing & Upgrading Oracle10g.

- The most common Oracle10g installation can be performed with just one CD.

- On Linux and Unix you may use the runInstaller -ignoreSysPrereqs flag to continue with the Oracle install even if the flavor of Linux is not certified by Oracle.

- Starting 10g the Oracle client can only be installed from a separate CD.

- Personal Edition is only available for Windows and does not include RAC.

- If you choose a started database based in one if the schemes available (Transaction processing, General Purpose or Data Warehouse) DBCA will run in no interactive mode, if you use advance it will run in interactive mode.

- Database Control (EM) is installed by default if you install a preconfigured database, for custom you have the option not to install it.

- OUI allows you to choose 3 types of storage File system, ASM and Raw devices.

- If you choose ASM and an ASM instance is not installed already Oracle will create one for you.

- In 10g you may enable automatic database backup during install using the backup and recovery options screen.

- The default disk quota for Flash back is 2GB.

- While installing you have the option to provide separate password for each administrative user or provide one password for all.

- The EM Webstage and Apache, which were installed with Oralce9i, are not longer installed with the Oracle10g.

- In 10g the disk requirements for install are now less.

- The installation is simplified, you can install the software and create the database just with the default settings.

- Oracle10g requires a minimum of 512MB minimum for an instance with database control and 256MB without database control and about 2.5GB HD.

- The Oracle10g Companion CD includes:
- Database examples.
- JPublisher.
- Legato.
- Natively compiled Java libraries.
- Oracle text-supplied knowledge bases.
- Oracle HTTP Server (Different Oracle Home).
- Oracle Apex (Different Oracle Home).

Configuring Oracle 10g.

- 10g includes a new SYSAUX tablespace to store all auxiliary metadata.

- DBCA can setup the flash recovery area, clone a database, setup Database control and grid control, register automatically LDAP, implement backup and recovery, create management repository and services.

- Cloning a database using DBCA templates saves time in database creation, because copying an already created seed database's files to the correct location takes less time than creating them.

- DBCA Templates are stored in $ORACLE_HOME/assistants/dbca/templates directory, they can be easily shared and can be copied from one machine to another.

- There are 2 types of templates seed and nonseed, seed templates have the extension .dbc and include the data files of an existing database. Nonseed templates has the extension .dbt and does not include data files.

- When creating a database template for cloning DBCA will shutdown the database and start it in mount state.

- In 10g the instance parameters are categorized into basic and advanced, in most of the cases modification of the basic parameters is enough to setup and tune the database.

- The compatible parameter is irreversible, once you set it you can no change it's value to one that is less than the previous value. To lower the value you will need to do a point in time recovery of the databases.

- Have you ever wonder how Oracle knows if you are using or not some of the enterprise features of the license?. What about Looking here

SQL> l 1 select name, detected_usages DU, last_usage_date 2 from dba_feature_usage_statistics 3* where currently_used = 'TRUE'

- If you want to know the high watermark usage of various attributes such as number of tablespaces, concurrent connections, number of user indexes, etc. Then this is the query you need.

SQL> l 1* select NAME, VERSION, HIGHWATER, LAST_VALUE, DESCRIPTION from dba_high_water_mark_statistics SQL>

If you don't see the information populated in the table all you have to do is execute the following package.

exec dbms_stats.gather_database_stats

- Oracle 10g provides a utility script $ORACLE_HOME/rdbms/admin/ultu10i.sql to perform pre-upgrade validation on the database to be upgraded. DBUA will also automatically run this tool. This scripts needs to be run as SYSDBA before you plan to perform a manual upgrade.

- Oracle uses the DBMS_REGISTRY package to determinate the objects to be upgrades. In 10g the cmpdbmig.sql script determinate which components are in the database by performing specifics callouts to the components REGISTRY. Versions of Oracle prior to 9.2 does not have components REGISTRY in this case the upgrades automatically creates and populates the component REGISTRY. You can query the components using the view DBA_REGISTRY.

- If you get any errors during the upgrade "Manually only" the utl101s.sql scripts provides the name of specific scripts to run to fix the failed component.

- Run the script $ORACLE_HOME/rdbms/admin/utlrp.sql at to end to recompile all objects.

When the use of Oracle range Partitioning is effective

Have you ever wondered what is the point (number of rows) where the use of Oracle range partitioning start been effective? , we all know that there is some internal overhead in using partitioning but at what moment that overhead pays off.

For this test I created 2 tables as follow:

Non partitioned Table

create table np (id number primary key, name varchar2(30), last_name varchar2(30),
age number, dept_no number);

Partitioned Table

create table pt (id number primary key, name varchar2(30), last_name varchar2(30),
age number, dept_no number)
partition by range (dept_no)
(partition dept1 values less than (2),
partition dept2 values less than (3),
partition dept3 values less than (4),
partition dept4 values less than (5),
partition dept5 values less than (6),
partition dept6 values less than (7),
partition dept7 values less than (8),
partition dept8 values less than (9),
partition dept9 values less than (10),
partition dept10 values less than (maxvalue));

In addition I created 2 indexes one for each table on the column dept_no. For the partition table it was a local index

I used the following sql to populate the tables with different amount of data.

set serveroutput on;
declare
dept_alea number(2);
name_alea varchar2(30);
last_alea varchar2(30);
begin
for i in 1..&limit
loop
select dbms_random.value(1,10) into dept_alea from dual;
select dbms_random.string('U',30) into name_alea from dual;
select dbms_random.string('U',30) into last_alea from dual;
insert into &tabla
values (i, name_alea, last_alea, 37, dept_alea);
dbms_output.put_line (i);
commit;
end loop;
end;
/

In order to be partial and try to obtain valid information I did flushed the buffer cache and shared pool after each query. These are the queries I used:

@flush.sql
select * from np where dept_no=5;

@flush.sql
select * from pt where dept_no=5;

The following are the results I obtained

Number of Rows	Non Partition Time	Partition Time
100	Elapsed: 00:00:00.11	Elapsed: 00:00:00.51
1000	Elapsed: 00:00:00.14	Elapsed: 00:00:00.29
10000	Elapsed: 00:00:00.67	Elapsed: 00:00:00.94
11500	Elapsed: 00:00:00.87	Elapsed: 00:00:01.08
12000	Elapsed: 00:00:00.84	Elapsed: 00:00:01.05
12500	Elapsed: 00:00:01.25	Elapsed: 00:00:01.30
15000	Elapsed: 00:00:01.27	Elapsed: 00:00:01.42
17500	Elapsed: 00:00:01.63	Elapsed: 00:00:01.59
18000	Elapsed: 00:00:01.64	Elapsed: 00:00:01.40
20000	Elapsed: 00:00:04.24	Elapsed: 00:00:01.83

Conclusion: Now you know and you don't have to test, if you will have more than 17,500 rows
in a table, and you can range partition and will query or access the table by the partition key, then range partitioning is a good idea. If you are wondering this test was done in 11.0.6 EE on a Intel dual core 1.3Ghz HP computer on Oracle Unbreakable Linux.