Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Fwd: [] Create an SPFILE in 9i

Fwd: [] Create an SPFILE in 9i

From: Ron Rogers <>
Date: Fri, 12 Jul 2002 04:33:23 -0800
Message-ID: <>

 Just by coincidence this came in the mail today. Ron
ROR mm

>>> "Oracle Tips at"

<> 07/12/02 03:05AM >>>

Presents your
ORACLE E-NEWSLETTER for July 12, 2002


THE DATA STORAGE INFO YOU NEED BEFORE YOU BUY Selecting the appropriate data storage solution is vital to your organization's future. When the choice rests on your shoulders, you need
the unbiased expert advice you can only get from TechRepublic - so make
an informed decision with IT Professional's Guide to Enterprise Storage
book and CD-ROM. Don't wait to hit critical mass! Get the insights and articles to help you through the maze of features, benefits, and limitations of a wide range of data storage solutions.



An exciting new feature of Oracle9i is the removal of the init.ora file.
Oracle has replaced the init.ora file because you can now change all parameters dynamically with the ALTER SYSTEM commands.

With Oracle9
i, you can manage initialization parameters using a binary server parameter file (SPFILE). By default, a new Oracle9i database will be working on
a PFILE, so you must create the SPFILE from the PFILE at the SQL prompt.
You can create the SPFILE using the CREATE SPFILE statement, which requires connecting as SYSDBA.

Connect system/manager as sysdba;
CREATE SPFILE FROM PFILE; This command creates an SPFILE in a nondefault location ($ORACLE_HOME/database). However, you can fully qualify the path name in the CREATE
SPFILE statement.

CREATE SPFILE='/u01/admin/prod/pfile/file_mydb.ora' FROM

After creating an SPFILE, you may encounter an error when you bounce the
database. To work around this, you have to reconnect as SYSDBA and use the STARTUP command.

The addition of the SPFILE has changed the search path for the Oracle startup deck. In Oracle9i, follow these steps to search for the SPFILE:

  1. Search for the spfile$ORACLE_SID.ora file in the default location.
  2. Look for the spfile.ora.
  3. Search for the PFILE by name: init$ORACLE_SID.ora.

SPECIFYING SCOPE Once you have an SPFILE, you can change any initialization parameter with the ALTER SYSTEM command. However, there's an important SCOPE parameter
that you need to understand.

The SCOPE parameter has three values: SPFILE, MEMORY, and BOTH. Let's look at an example of each.

Alter system set db_2k_cache_size=100m SCOPE=SPFILE;

If you want to make a change to a parameter in the SPFILE without affecting the current instance, you can do so using the SCOPE=SPFILE option of
the ALTER SYSTEM statement. This is useful when you want to make a change
starting from the next startup and not for the current instance.

Alter system set db_2k_cache_size=100m SCOPE=MEMORY;

In the example above, the SCOPE=MEMORY option tells Oracle9i to make the
change for the life of the instance and to change it back to the default
value the next time the database is bounced.

Alter system set db_2k_cache_size=100m SCOPE=BOTH;

When you specify SCOPE=BOTH, Oracle makes the change immediately, and Oracle will also make the change permanent, even after the database is bounced.

Donald Burleson has been a database administrator for 23 years and has written 14 database books and over 100 articles. He is editor in chief of
Oracle Internals and runs Burleson Oracle Consulting.

READ MORE ABOUT DEVELOPMENT AN INSIDER'S GUIDE TO ORACLE CERTIFICATION Is an OCP certification your ticket to becoming an Oracle DBA, or is it
just one of several important criteria? Check out this article for one expert's opinion. hPwP4lpp69RR

Following these simple naming conventions and documentation standards simplifies team code maintenance. Try these techniques on your next project
and see if they work for you.

CLIMBING TO THE TOP OF THE CORPORATE TECHNOLOGY LADDER Do your technical skills qualify you for an executive role? Before you fire off that resume to a headhunter, you need to consider a number of critical success factors. Here are some practical suggestions for breaking
into the VP club.

NEW AT BUILDER.COM WHERE WILL THE VISUAL BASIC 6.0 DEVELOPERS GO? You love Visual Basic 6.0, but .NET is breathing down your neck. Should
you make the move to Visual Basic .NET or C#? Before you make the choice,
check out these three dispelled post-VB6 myths.

COUNTING LINES OF CODE CAN HELP MEASURE PROGRESS One developer believes that counting a developer's lines of code can yield useful information. He explains why and shares a sample app that automates the process.

FLASH REMOTING WITH COLDFUSION COMPONENTS Flash Remoting makes it easy to use dynamic data in your Flash applications. Here's everything you need to know to use Flash Remoting with
ColdFusion Components.

You're tasked with keeping a project running smoothly, so you need to be
ready for potential issues to rear their ugly heads. Turn to
for help. Sign up instantly for Project Management Blueprint
e-newsletter, delivered each Thursday, and get the resources you need.

This e-newsletter may contain links to sites on the Internet that are owned and operated by third parties. CNET Networks, Inc. is not responsible
for the content of any such third-party site.

To update your subscriptions:

To unsubscribe from this mailing list:

Questions or comments:

Visit Builder

Copyright 2002 CNET Networks, Inc. All rights reserved. is a trademark of CNET Networks, Inc.

Please see the official ORACLE-L FAQ:
Author: Ron Rogers

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Jul 12 2002 - 07:33:23 CDT

Original text of this message