BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Oracle Database Administration

Oracle Tips by Burleson Consulting

A database consists of executables, global areas, and database files. Within the database files exist tables, indexes, sequences, views, clusters, and synonyms. The Remote DBA will be involved in the creation, maintenance, and deletion of these objects on a frequent basis. The commands CREATE, ALTER, and DROP are fairly easy to master. A subset of the CREATE and ALTER command, the STORAGE clause, is also very important for the Remote DBA to understand and use properly.

The CREATE Command

As its name implies, the CREATE statement is used to create databases, tablespaces, tables, clusters, database links, indexes, sequences, views, users, packages, procedures, functions, and rollback segments. It has this general format (any thing in square brackets is optional):

CREATE object_type [schema_name.]object_name
create options,
[STORAGE ( storage parameters)]

The STORAGE Clause

The STORAGE clause specifies how an object uses the space to which it is allocated. Some objects, including packages, procedures, types, views, libraries, directories, indextypes, and others, don't use the STORAGE clause Let’s look at the format of the STORAGE clause.

[DEFAULT] STORAGE (INITIAL x [K|M] NEXT x [K|M] MINEXTENTS x MAXEXTENTS x PCTINCREASE x FREELISTS x FREELIST GROUPS x OPTIMAL x [K|M] BUFFER_POOL DEFAULT|KEEP|RECYCLE)

where:

[DEFAULT]. Is used only in a TABLESPACE specification to specify the default storage used for objects placed in the tablespace when no object-specific storage specification is made.

INITIAL.  Specifies the size in bytes of the initial extent of the object. The default is 5 Oracle block sizes (10K for a 2K blocksize, 40K for an 8K blocksize, and so forth). The minimum size is 2 Oracle blocks plus 1 for each freelist specified (freelists default to 1 for tables, 2 for indexes.) The maximum is 4 gigabytes on most platforms. All values are rounded to the nearest Oracle blocksize.

NEXT.  Indicates the size for the next extent after the INITIAL is used. The default is 5 Oracle blocks, the minimum is 1 Oracle block, the maximum is 4 gigabytes. NEXT is the value that will be used for each new extent if PCTINCREASE is set to 0. If PCTINCREASE is greater than 0, then the next extent will be NEXT, the second extension will be NEXT times 1 plus PCTINCREASE/100, then the size of that extent times 1 plus PCTINCREASE/100 for the next extension, and so forth. The factor of 1 plus PCTINCREASE/100 is only applied to the size of the last extent.

MINEXTENTS.  Specifies the number of initial extents for the object. Generally, except for rollback segments, it is set to 1. If a large amount of space is required and there is not enough contiguous space for the table, setting a smaller extent size and specifying several extents may solve the problem. The values for INITIAL, NEXT, and PCTINCREASE are used when calculating the extent sizes for the number of extents requested.

MAXEXTENTS.  Specifies the largest number of extents allowed the object. This defaults to the max allowed for your blocksize for Oracle8, Oracle8i, and Oracle9i. In addition, if UNLIMITED is set, there is no upper limit.

PCTINCREASE.  Tells Oracle how much to grow each extent after the INITIAL and NEXT extents are used. A specification of 50 will grow each extent after NEXT by 50 percent  for each subsequent extent. This means that for a table created with one INITIAL and a NEXT extent, any further extents will increase in size by 50 percent over their predecessor. Under Oracle8, Oracle8i, and Oracle9i, this parameter is applied only against the size of the previous extent. The DEFAULT value is 50, and this should always be adjusted.

OPTIMAL.  Used only for rollback segments, it specifies the value to which a rollback segment will shrink back after extending.

FREELIST GROUPS.  Specifies the number of freelist groups to maintain for a table or index. FREELIST GROUPS should be set to the number of instances that will be addressing the table in an OPS or RAC environment. You must allow one block for each FREELIST; the number of FREELISTS * FREELIST GROUPS yields the number of total freelists. If you are using locally managed tablespaces (covered in the tablespace section), and you specify a combination of FREELISTS and FREELIST GROUPS that is too large for the extent sizes specified, the create operation will fail.

FREELISTS.  For objects other than tablespaces, specifies the number of freelists for each of the freelist groups for the table, index, or cluster. The minimum value is 1; the maximum is blocksize-dependent. Always specify FREELISTS to the number of simultaneous transactions that will be addressing a single block. Empirical testing has shown little improvements by setting this parameter to greater than 4. INITIAL must be set to the minimum value plus the total number of freelists.

NOTE: Both FREELIST GROUPS and FREELISTS are ignored if the tablespace in which the object (index, table, cluster, snapshot, materialized view) resides is in automatic space management mode.

BUFFER_POOL. Specifies the buffer pool in which to place the object when it is used. The choices are DEFAULT (the default value), KEEP, and RECYCLE. KEEP should be used for objects such as indexes or lookup tables that will be referenced multiple times.

Proper use of the STORAGE clause means that you will have to perform accurate estimates of table and index size before creation. This will be covered in Chapters 4, Administration of Relational Database Tables, 5, Administration of Oracle 9i Object Tables, and 6, Administration of Indexes.

Database Creation, Alteration, and Deletion

Like other objects under Oracle, databases themselves can be created, altered, and deleted. Let’s look at these different processes.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

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


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter