Muhammad Nadeem Chaudhry's Blog


Data Pump Export (expdp) and Data Pump Import(impdp)

Posted in Uncategorized by Muhammad Nadeem Chaudhry on October 31, 2010

Introduction
Oracle introduced the Data Pump in Oracle Database 10g Release 1. This new oracle technology enables very high transfer of data from one database to another. The oracle Data Pump provides two utilities namely:

  • Data Pump Export which is invoked with the expdp command.
  • Data Pump Import which is invoked with the impdp command.

The above two utilities have similar look and feel with the pre-Oracle 10g import and export utilities (imp and exp) but are completely separate. This means that dump files generated by the original export utility (exp) cannot be imported by the new data pump import utility (impdp) and vice-versa.

Data Pump Export (expdp) and Data Pump Import (impdp) are server-based rather than client-based as is the case for the original export (exp) and import (imp). Because of this, dump files, log files, and sql files are accessed relative to the server-based directory paths. Data Pump requires that directory objects mapped to a file system directory be specified in the invocation of the data pump import or export.

It for this reason and for convenience that a directory object be created before using the data pump export or import utilities. For example to create a directory object named expdp_dir located at /u01/backup/exports enter the following sql statement:

SQL> create directory expdp_dir as ‘/u01/backup/exports’;
then grant read and write permissions to the users who will be performing the data pump export and import.

SQL> grant read,write on directory expdp_dir to system, user1, user2, user3;
Invoking Data Pump Export
You can invoke the data pump export using a command line. Export parameters can be specified directly in the command line.

Full Export Mode
A full export is specified using the FULL parameter. In a full database export, the entire database is unloaded. This mode requires that you have the EXP_FULL_DATABASE role. Example :

$ expdp system/<password> DIRECTORY=expdp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=expfull.og

Schema Export Mode
The schema export mode is invoked using the SCHEMAS parameter. If you have no EXP_FULL_DATABASE role, you can only export your own schema. If you have EXP_FULL_DATABASE role, you can export several schemas in one go. Optionally, you can include the system privilege grants as well.

$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe

Table Export Mode
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can export only tables in your own schema. You can only specify tables in the same schema.

$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=tables_exp.dmp TABLES=employees,jobs,departments

Invoking Data Pump Import
The data pump import can be invoked in the command line. The export parameters can be specified directly in the command line.

Full Import Mode The full import mode loads the entire contents of the source (export) dump file to the target database. However, you must have been granted the IMP_FULL_DATABASE role on the target database. The data pump import is invoked using the impdp command in the command line with the FULL parameter specified in the same command line.

$ impdp system/<password> DIRECTORY=expdp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=impfull.og

Schema Import Mode
The schema import mode is invoked using the SCHEMAS parameter. Only the contents of the specified schemas are load into the target database. The source dump file can be a full, schema-mode, table, or tablespace mode export files. If you have a IMP_FULL_DATABASE role, you can specify a list of schemas to load into the target database.

$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp SCHEMAS=hr,sh,oe

Table Import Mode
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can import only tables in your own schema.

$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp TABLES=employees,jobs,departments

Spool to a .xls file

Posted in Uncategorized by Muhammad Nadeem Chaudhry on October 27, 2010

A small tip, I read on OTN about spooling to a .xls (excel) file:

It goes like this

set feed off markup html on spool on
spool c:\salgrade.xls
select * from salgrade;
spool off
set markup html off spool off

And the xls it makes shows up like:

HTML output from sqlPlus

Oracle is a very feature rich high end database, however sqlPlus is one of it’s weakness. Time to time it you need to list the content of a table or view in Oracle using sqlPlus and it usually results a hard interpretable output. There is the possibility to set line size parameter and modify the display size of the columns but it takes to much time.  

I nice solution would be to display the output in a HTML format. Fortunately we can do that. Now I will show you 2 solutions. The first one is working from the command line and the second one is working inside sqlPLus.

1. Generate Oracle HTML output from command line:

Code:

1.  sqlplus -S -M “HTML ON TABLE ‘BORDER=”2″‘” user/password @test.sql>test.html 

In this example you need to create the test.sql file and put all your sql statements in this file. The result will be written into the test.html file.

2. Generate Oracle HTML output inside sqlPlus:

Code:

1.  SET markup HTML on

2.  spool test.html

3.  SELECT * FROM mytable;

4.  spool off

5.  SET markup HTML off

In this case you can write all of your sql statements between the spool commands. The test.html file will be generated in the actual directory.

As you can see these solutions are not the best as you can view the files in a browser and not in sqlPlus, however in a lot of cases this solution makes your life easier.

Spool a Flat File with Column Headings
 
Try something like this

rem Script to spool a file with column headings.
rem This one is csv, but you can make it tab-delimited by changing || ‘, ‘
|| to || chr(9) ||

rem or any other character you want to delimit by.

set echo off
set pagesize 0

— this turns off column headings, too. If you only want the column heading to appear once, then increase the pagesize — equal to the number of rows + 2 (to account for the column header and the “——…” divider right below it.

–For example ,  if your query returns 1000 records, then set your pagesize to 1002 (or greater).
 
set feedback off
set linesize 200

— make the line wide enough for your purpose set trimspool on
— get rid of trailing spaces used to fill out the line size

set termout off

spool filename.txt

— If you make the extension csv, Excel will automatically load and format the columns
— You might not want this to happen if some columns contain numeric values like ZIP
— codes that have leading zeroes and you want to keep them

select colA || ‘,’ ||
colB || ‘,’ ||
colC || ‘,’ ||
colD
from dual;

select colA.
colB,
colC,
colD
from your_table;
spool off
exit

Note:

If table that need to be spooled, has billions records i recommend to use spooling method as illusterated above rather than using Oracle Toad to save time

SQL Loader Utility

Posted in Uncategorized by Muhammad Nadeem Chaudhry on October 26, 2010

SQL LOADER utility is used to load data from other data source into Oracle. For example, if you have a table in FOXPRO, ACCESS or SYBASE or any other third party database, you can use SQL Loader to load the data into Oracle Tables. SQL Loader will only read the data from Flat files. So If you want to load the data from Foxpro or any other database, you have to first convert that data into Delimited Format flat file or Fixed length format flat file, and then use SQL loader to load the data into Oracle.

Following is procedure to load the data from Third Party Database into Oracle using SQL Loader.

  1. Convert the Data into Flat file using third party database command.
  2. Create the Table Structure in Oracle Database using appropriate datatypes
  3. Write a Control File, describing how to interpret the flat file and options to load the data.
  4. Execute SQL Loader utility specifying the control file in the command line argument

To understand it better let us see the following case study.

 

CASE STUDY (Loading Data from MS-ACCESS to Oracle)

Suppose you have a table in MS-ACCESS by name EMP, running under Windows O/S, with the following structure

     EMPNO     INTEGER
     NAME      TEXT(50)
     SAL       CURRENCY
     JDATE         DATE

This table contains some 10,000 rows. Now you want to load the data from this table into an Oracle Table. Oracle Database is running in LINUX O/S.

Solution

Steps

Start MS-Access and convert the table into comma delimited flat (popularly known as csv) , by clicking on File/Save As menu. Let the delimited file name be emp.csv

  1. Now transfer this file to Linux Server using FTP command
    1. Go to Command Prompt in windows
    2. At the command prompt type FTP followed by IP address of the server running Oracle.

FTP will then prompt you for username and password to connect to the Linux Server. Supply a valid username and password of Oracle User in Linux

For example:-
C:\>ftp 200.200.100.111

Name: oracle

Password:oracle

FTP>

  1.  
    1. Now give PUT command to transfer file from current Windows machine to Linux machine.

FTP>put

Local file:C:\>emp.csv
remote-file:/u01/oracle/emp.csv

File transferred in 0.29 Seconds
FTP>

  1.  
    1. Now after the file is transferred quit the FTP utility by typing bye command.

FTP>bye

Good-Bye

  1. Now come the Linux Machine and create a table in Oracle with the same structure as in MS-ACCESS by taking appropriate datatypes. For example,  create a table like this

$sqlplus scott/tiger

SQL>CREATE TABLE emp (empno number(5),

              name varchar2(50),

              sal  number(10,2),

              jdate date);

  1. After creating the table, you have to write a control file describing the actions which SQL Loader should do. You can use any text editor to write the control file. Now let us write a controlfile for our case study

$vi emp.ctl

1        LOAD DATA

2        INFILE ‘/u01/oracle/emp.csv’

3        BADFILE          ‘/u01/oracle/emp.bad’

4        DISCARDFILE ‘/u01/oracle/emp.dsc’

5        INSERT INTO TABLE emp

6        FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”’ TRAILING NULLCOLS

7        (empno,name,sal,jdate date ‘mm/dd/yyyy’)

Notes:
(Do not write the line numbers, they are meant for explanation purpose)

1.       The LOAD DATA statement is required at the beginning of the control file.

2.       The INFILE option specifies where the input file is located

3.       Specifying BADFILE is optional. If you specify,  then bad records found during loading will be stored in this file.

4.       Specifying DISCARDFILE is optional. If you specify, then records which do not meet a WHEN condition will be written to this file.

5.       You can use any of the following loading option

1.       INSERT : Loads rows only if the target table is empty

2.       APPEND: Load rows if the target table is empty or not.

3.       REPLACE: First deletes all the rows in the existing table and then, load rows.

4.       TRUNCATE: First truncates the table and then load rows.

6.       This line indicates how the fields are separated in input file. Since in our case the fields are separated by “,” so we have specified “,” as the terminating char for fields. You can replace this by any char which is used to terminate fields. Some of the popularly use terminating characters are semicolon “;”, colon “:”, pipe “|” etc. TRAILING NULLCOLS means if the last column is null then treat this as null value, otherwise,  SQL LOADER will treat the record as bad if the last column is null.

7.        In this line specify the columns of the target table. Note how do you specify format for Date columns

  1. After you have wrote the control file save it and then, call SQL Loader utility by typing the following command

$sqlldr userid=scott/tiger control=emp.ctl log=emp.log

After you have executed the above command SQL Loader will shows you the output describing how many rows it has loaded.

The LOG option of sqlldr specifies where the log file of this sql loader session should be created.  The log file contains all actions which SQL loader has performed i.e. how many rows were loaded, how many were rejected and how much time is taken to load the rows and etc. You have to view this file for any errors encountered while running SQL Loader.

CASE STUDY (Loading Data from Fixed Length file into Oracle)

Suppose we have a fixed length format file containing employees data, as shown below, and wants to load this data into an Oracle table.

7782 CLARK      MANAGER   7839  2572.50          10

7839 KING       PRESIDENT       5500.00          10

7934 MILLER     CLERK     7782   920.00          10

7566 JONES      MANAGER   7839  3123.75          20

7499 ALLEN      SALESMAN  7698  1600.00   300.00 30

7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30

7658 CHAN       ANALYST   7566  3450.00          20

7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30

SOLUTION:

Steps :-

1.      First Open the file in a text editor and count the length of fields, for example in our fixed length file, employee number is from 1st position to 4th position, employee name is from 6th position to 15th position, Job name is from 17th position to 25th position. Similarly other columns are also located.

2.      Create a table in Oracle, by any name, but should  match columns specified in fixed length file. In our case give the following command to create the table. 

SQL> CREATE TABLE emp (empno  NUMBER(5),

       name VARCHAR2(20),

       job  VARCHAR2(10),

       mgr  NUMBER(5),

       sal  NUMBER(10,2),

       comm NUMBER(10,2),

       deptno    NUMBER(3) );

3.      After creating the table, now write a control file by using any text editor

$vi empfix.ctl

1)   LOAD DATA

2)   INFILE ‘/u01/oracle/fix.dat’

3)   INTO TABLE emp

4)   (empno         POSITION(01:04)   INTEGER EXTERNAL,

       name         POSITION(06:15)   CHAR,

       job          POSITION(17:25)   CHAR,

       mgr          POSITION(27:30)   INTEGER EXTERNAL,

       sal          POSITION(32:39)   DECIMAL EXTERNAL,

       comm         POSITION(41:48)   DECIMAL EXTERNAL,

5)   deptno         POSITION(50:51)   INTEGER EXTERNAL)

 Notes:

(Do not write the line numbers, they are meant for explanation purpose)

1.       The LOAD DATA statement is required at the beginning of the control file.

2.       The name of the file containing data follows the INFILE parameter.

3.       The INTO TABLE statement is required to identify the table to be loaded into.

4.       Lines 4 and 5 identify a column name and the location of the data in the datafile to be loaded into that column. empno, name, job, and so on are names of columns in table emp. The datatypes (INTEGER EXTERNAL, CHAR, DECIMAL EXTERNAL) identify the datatype of data fields in the file, not of corresponding columns in the emp table.

5.       Note that the set of column specifications is enclosed in parentheses.

4.      After saving the control file now start SQL Loader utility by typing the following command.

 $sqlldr userid=scott/tiger control=empfix.ctl log=empfix.log direct=y

After you have executed the above command SQL Loader will shows you the output describing how many rows it has loaded.

Loading Data into Multiple Tables using WHEN condition

You can simultaneously load data into multiple tables in the same session. You can also use WHEN condition to load only specified rows which meets a particular condition (only equal to “=” and not equal to “<>” conditions are allowed).

For example, suppose we have a fixed length file as shown below

7782 CLARK      MANAGER   7839  2572.50          10

7839 KING       PRESIDENT       5500.00          10

7934 MILLER     CLERK     7782   920.00          10

7566 JONES      MANAGER   7839  3123.75          20

7499 ALLEN      SALESMAN  7698  1600.00   300.00 30

7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30

7658 CHAN       ANALYST   7566  3450.00          20

7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30

Now we want to load all the employees whose deptno is 10 into emp1 table and those employees whose deptno is not equal to 10 in emp2 table. To do this first create the tables emp1 and emp2 by taking appropriate columns and datatypes. Then, write a control file as shown below

$vi emp_multi.ctl

Load Data
infile ‘/u01/oracle/empfix.dat’
append into table scott.emp1
WHEN (deptno=’10 ‘)
  (empno        POSITION(01:04)   INTEGER EXTERNAL,

   name         POSITION(06:15)   CHAR,

   job          POSITION(17:25)   CHAR,

   mgr          POSITION(27:30)   INTEGER EXTERNAL,

   sal          POSITION(32:39)   DECIMAL EXTERNAL,

   comm         POSITION(41:48)   DECIMAL EXTERNAL,

   deptno       POSITION(50:51)   INTEGER EXTERNAL)

    INTO TABLE scott.emp2
  WHEN (deptno<>’10 ‘)
  (empno        POSITION(01:04)   INTEGER EXTERNAL,

   name         POSITION(06:15)   CHAR,

   job          POSITION(17:25)   CHAR,

   mgr          POSITION(27:30)   INTEGER EXTERNAL,

   sal          POSITION(32:39)   DECIMAL EXTERNAL,

   comm         POSITION(41:48)   DECIMAL EXTERNAL,

   deptno       POSITION(50:51)   INTEGER EXTERNAL)

 After saving the file emp_multi.ctl run sqlldr
$sqlldr userid=scott/tiger control=emp_multi.ctl

Conventional Path Load and Direct Path Load.

SQL Loader can load the data into Oracle database using Conventional Path method or Direct Path method. You can specify the method by using DIRECT command line option. If you give DIRECT=TRUE then SQL loader will use Direct Path Loading otherwise, if omit this option or specify DIRECT=false, then SQL Loader will use Conventional Path loading method.

Conventional Path
Conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables.

When SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources. This can slow the load significantly. Extra overhead is added as SQL statements are generated, passed to Oracle, and executed.

The Oracle database looks for partially filled blocks and attempts to fill them on each insert. Although appropriate during normal use, this can slow bulk loads dramatically.

Direct Path

In Direct Path Loading, Oracle will not use SQL INSERT statement for loading rows. Instead it directly writes the rows, into fresh blocks beyond High Water Mark, in datafiles i.e. it does not scan for free blocks before high water mark. Direct Path load is very fast because

  • Partial blocks are not used, so no reads are needed to find them, and fewer writes are performed.
  • SQL*Loader need not execute any SQL INSERT statements; therefore, the processing load on the Oracle database is reduced.
  • A direct path load calls on Oracle to lock tables and indexes at the start of the load and releases them when the load is finished. A conventional path load calls Oracle once for each array of rows to process a SQL INSERT statement.
  • A direct path load uses multiblock asynchronous I/O for writes to the database files.
  • During a direct path load, processes perform their own write I/O, instead of using Oracle’s buffer cache. This minimizes contention with other Oracle users.

Restrictions on Using Direct Path Loads

The following conditions must be satisfied for you to use the direct path load method:

  • Tables are not clustered.
  • Tables to be loaded do not have any active transactions pending.
  • Loading a parent table together with a child Table
  • Loading BFILE columns 

———————————————————————————————————————————————-

Sample Files:

A)     Control File:

LOAD DATA

INFILE ‘c:\bkp.csv’

INTO TABLE bkp

SINGLEROW

APPEND

FIELDS TERMINATED BY ‘,’ optionally enclosed by ‘”‘

TRAILING NULLCOLS

(

 SUBSCRIBER_ID,

 FIRST_NAME,

 LAST_NAME,

 EXTERNAL_ID,

 ADDRESS_1,

 CITY,

 COUNTRY,

 HOME_PHONE,

 MOBILE_PHONE,

 INTERNET_ID,

 MAC,

 PACKAGE_NAME

)

B)     Batch File:

@echo off

sqlldr user_name/password@SID CONTROL=C:\control.ctl  DIRECT = TRUE errors = 500000 READSIZE = 1048576 STREAMSIZE = 356000 BINDSIZE = 356000 LOG =c:\receipts.log

DBCA to Create a Database

Posted in Uncategorized by Muhammad Nadeem Chaudhry on October 8, 2010

Starting DBCA

To launch DBCA on the Windows operating system use the following procedure:

1. Log onto your computer as a member of the administrative group that is authorized to install Oracle software and create and run the database.

2. To launch DBCA on a Windows operating system select the following: Start > Programs > Oracle – home_name > Configuration and Migration Tools > Database Configuration Assistant

To launch the DBCA on a UNIX, or as another method on a Windows operating system, enter the following at a command prompt: dbca which is typically found in $ORACLE_HOME/bin. The Welcome page appears.

3. Click Next to continue.

Using DBCA to Create a Database

Select Create a Database on the DBCA Operations page to begin a wizard that enables Oracle DBA to configure and create a database. During the interview, Oracle DBA are asked for your input on the following:

Database Templates

Database Identification

Management Options

Database Credentials

Storage Options

Database File Locations

Recovery Configuration

Database Content

Initialization Parameters

Database Storage

Creation Options

Note that most pages of the wizard provide a default setting that Oracle DBA can accept. To accept all the default parameters, Oracle DBA can click Finish at any step. Database Templates

This page enables Oracle DBA to select the type of database Oracle DBA want to create. By default, Oracle Corporation ships pre-defined templates. There are templates for Data Warehouse, General Purpose, and Transaction Processing databases. The templates contain settings optimized for workload. Click Show Details to see the configuration for each type of database. Choose the template suited to the type of workload your database will support. If Oracle DBA are not sure, select the default General Purpose template. For more complex environments, Oracle DBA may want to select the Custom Database option. This will result in a more extensive interview and it will take longer to create your database, since a database creation script must be run.

Database Identification

Enter the Global Database Name, in the form database_name.domain_name and SID (Oracle system identifier). The SID defaults to the database name and uniquely identifies the instance that runs the database.

Management Options

Use this page to set up your database so it can be managed with Oracle Enterprise Manager, which provides web-based management tools for individual databases, as well as central management tools for managing your entire Oracle environment. To use Enterprise Manager, check Configure the Database with Enterprise Manager. If the Oracle Management Agent has been installed on your host computer, then you are given the option of selecting central management by selecting Use Grid Control for Database Management. If you select this type of management, you must also indicate which management service to use in the drop-down menu. Otherwise, select Use Database Control for Database Management to manage your database locally. If you choose this option, you can additionally Enable Email Notifications, for Oracle to email you alerts regarding potential problems, and Enable Daily Backup. Click Help for more information about these options.

Database Credentials

In this page, you specify the passwords for the administrative accounts such as SYS and SYSTEM. To use the same password for all accounts, specify Use the Same Password for All Accounts and enter the password. Otherwise, specify Use Different Passwords and specify passwords individually. Storage Options

Specify the type of storage mechanism you would like your database to use. For more information, refer to “Installation Choices” earlier in this chapter. Database File Locations

In this page, Oracle DBA specify the Oracle home and directory path in which to install the Oracle software. Choose one of the following:

Use Database File Locations from Template—Selecting this option instructs the DBCA to use the directory information as specified in the template. Even if Oracle DBA choose this option, Oracle DBA will have an opportunity later to make modifications to database filenames and locations.

Use Common Location for All Database Files—This option requires Oracle DBA to specify a new directory for the Oracle home. All the database files will be created in this location. Even if Oracle DBA choose this option, Oracle DBA will have an opportunity later to make modifications to database filenames and locations.

Use Oracle-Managed Files—Select this option to eliminate the need for you, the DBA, to directly manage operating system files comprising an Oracle database. You specify default location called a database area for all your files. Oracle thereafter automatically creates and deletes files in this location as required. You also have the option to create multiple copies of your redo and online log files by Selecting this option enables you to delegate the complete management of database files to the database. Oracle DBA no longer need to specify the filenames, location, or their sizes.

Recovery Configuration

When Oracle DBA create a new database, it is important to configure the database so you can recover your data in the event of a system failure. Use this page to specify a flash recovery area and to enable archiving.

To specify a backup and recovery area, choose Specify Flash Recovery Area and specify its directory location and size. You can use variables to identify some standard locations. To review or add additional locations, click File Location Variables at the bottom of the page.

By checking Enable Archiving, you can enable archiving. This includes archiving database redo logs, which can be used to recover a database. Selecting this option is the same as enabling Archive Log Mode in Oracle Enterprise Manager or running the database in ARCHIVELOG mode. You can accept the default archive mode settings or change them by selecting Edit Archive Mode Parameters Oracle recommends you select Enable Archiving. Selecting this option provides better protection for your database in the case of software or hardware failure. If Oracle DBA do not select this option now, Oracle DBA can set up archive log mode later.

Database Content

In the Sample Schemas property page, check Sample Schemas if Oracle DBA want to include the Sample Schemas (EXAMPLE) tablespace in your database. The Sample Schemas provide a common platform for examples. Oracle books and educational materials contain examples based upon the Sample Schemas. Oracle recommends they be included in your database. In the Custom Scripts property page, you can specify one or more SQL scripts to run after your database is created. This is useful for performing post-installation tasks, such as loading custom schemas. To specify scripts, check Run the following scripts. Note that if you choose to run scripts after installation, your scripts must include a connect string that identifies the database. Click Help for more information. Otherwise to run no script, accept the default No scripts to run.

Initialization Parameters

The links on this page provide access to pages that enable you change default initialization parameter settings. These parameters fall into the following categories:

Memory

Sizing

Character Sets

Connection Mode

Memory Use this page to set the initialization parameters that control how the database manages its memory usage. Oracle DBA can choose from one of two approaches to memory management:

Typical—requires little configuration, and allocates memory as a percentage of total overall physical system memory. To use this method, select Typical and enter a percentage value. Click Show Memory Distribution to see how much memory the Assistant assigns to the System Global Area (SGA) and the Program Global Area

Custom—requires more configuration, but gives Oracle DBA more control over how the database uses system memory. This option is meant for more experienced database administrators. Oracle DBA can directly specify memory sizes for the SGA and PGA and their sub-structures, such as the shared pool and buffer cache. To use this method, click Custom. To allocate specific amounts of memory to SGA and PGA, select Automatic. To customize how the SGA memory is distributed among the SGA memory substructures, select Manual and enter specific values for each SGA component. For a complete description of these memory areas, click Help. Sizing In this property page, Oracle DBA specify the smallest block size and the maximum number of operating system user processes that can simultaneously connect to the database.

To specify block size, enter the size in bytes or accept the default. Oracle database data is stored in these blocks. One data block corresponds to a specific number of bytes of physical space on disk. While using pre-defined templates, this field is not enabled since the database will be created with the default block size of 8KB. But while using the custom option, you can change block size. Selecting a block size other than the default 8 KB value requires advanced knowledge and should only be done when absolutely required.

To specify maximum number of processes that can simultaneously connect to the database, enter the number or accept the default. The value should be 6 or greater. This value should allow for all background processes, such as locks and parallel execution processes. The default value for this parameter is 150 which is good enough for many environments.

Character Sets Use this page to define the character sets used by your database. Character sets are the encoding schemes used to display characters on your computer screen. Choosing a character set determines what languages can be represented in the database.

For Database Character Set, select from one of the following options:

Use the Default–Select this option if Oracle DBA need to support only the language currently used by the operating system for all your database users and your database applications.

Use Unicode (AL32UTF8)–Select this option if you need to support multiple languages for your database users and your database applications.

Choose from the list of character sets–Select this option if Oracle DBA want the Oracle Database to use a character set other than the default character set used by the operating system.

The national character set is an alternate character set that enables you to store Unicode characters in a database that does not have a Unicode database character set. Another reason for choosing a national character set is that programming in the national character set might be easier. Select the National Character Set from the drop-down menu or accept the default.

information such as day and month abbreviations, default sorting sequence for character data, and writing direction (left or right). Select the default language from the drop-down menu or accept the default.

The Default Date Format determines the convention for displaying the hour, day, month, and year. For example in the United Kingdom, the date format is DD-MM-YYYY. Select the format from the drop-down menu or accept the default. Connection Mode Use this page to select the database mode. Oracle DBA can run the database in dedicated server mode or shared server mode.

In Dedicated Server Mode, there is a dedicated server process for each user process. Select this option when the number of total clients is expected to be small, or when database clients will make persistent, long-running requests to the database.

In Shared Server Mode, several client connections share a database-allocated pool of resources. Use this mode when a large number of users need to connect to the database. It is also useful when database memory is limited and for better performance, since you can have more client connections to the database than in dedicated server mode with the same memory. If Oracle DBA choose shared server mode, you must also indicate the number of server processes you want to create when an instance is started up. For more information about setting this parameter, click Help.

Database Storage

A tree listing shows you the storage structure of your database (control files, datafiles, redo log groups, and so forth). If you are not satisfied with the storage structure or parameters you can make changes. You can create a new object with Create and delete existing objects with Delete.

Note that if Oracle DBA selected one of the preconfigured templates for a database, you cannot add or remove control files, datafiles or rollback segments.

Creation Options

Oracle DBA have the option of creating your database at this time, or saving the database definition as a template to use at another time, or both.