Oracle9i Looks Past init.ora and SYS

Our Oracle expert looks at Oracle9i, init.ora, SPFILE, SYS, INTERNAL, and the last row.

November/December 2001

I've just installed Oracle9i . I changed my init.ora settings, but they don't seem to have any effect. Can you tell me what's going on?

The answer is a new feature—the Stored Parameter File (SPFILE).

In Oracle8i and before, when you started the database, the init.ora file for that database had to be on your client machine. If you attempted to remotely start a database instance, you needed to have a local copy of the init.ora file, because the Oracle database back-end process running on your client would process the contents of the init.ora file. This made it somewhat difficult to restart a database over the network.

In Oracle9i, the SPFILE is stored in a binary format on the server itself. You no longer need to have a local copy (although you may if you like) to start the database remotely. It also means that changes made via the ALTER SYSTEM command may now persist across server restarts—so no more updating the init.ora file.

The SPFILE is stored on the server in the location specified by the initialization parameter spfile shown in Listing 1.

This SPFILE is created via the new command CREATE SPFILE, which has the syntax:

CREATE SPFILE = 'filename'
   FROM PFILE ='pfilename';

You can create a text version of this binary parameter file by using the CREATE PFILE command:

CREATE PFILE = 'pfilename'
   FROM SPFILE = 'filename';

This command always creates a text parameter file on the server (not on the client that executes the command). You can use this command to export all parameters, make changes to them, and then create a new SPFILE, using the parameter file (PFILE).

In the event that you want to use a specific PFILE, use the pfile= option in the startup command:

SQL> startup pfile=filename

When you make changes to system parameters now, you have an extended syntax in the ALTER SYSTEM command to set parameters:

ALTER SYSTEM set parameter = value
   SCOPE = MEMORY | SPFILE | BOTH;

The SCOPE clause allows you to set a system parameter:

• In memory. This affects the database now; it will not be in place after a restart.

• In the SPFILE. This does not change the current setting of the parameter, but it will modify the SPFILE so that upon a restart this parameter will take effect.

• In both memory and the SPFILE. This changes the current instance setting as well as updates the SPFILE.

I've just installed Oracle9i , and I cannot log in as SYS (see below). What's wrong? (I can no longer get in as INTERNAL, either.)

C:\>sqlplus sys/manager
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
Enter user-name: internal
Enter password:
ERROR:
ORA-09275: CONNECT INTERNAL is not a valid DBA connection

Well, there are two things at work here. The latter error— ORA-09275: CONNECT INTERNAL is not a valid DBA connection—is due to the desupport of the INTERNAL account in Oracle9i. As a result, the CONNECT INTERNAL feature was removed in Oracle9i. For some time now, Oracle has recommended that people stop using INTERNAL and start using the CONNECT user/ password "as sysdba" or "as sysoper" syntax. A user connected "as sysdba" has the same capabilities as a user connected as INTERNAL.

The other issue— ORA-28009: connection to sys should be as sysdba or sysoper—is not really new to Oracle9i, but more people will see it in Oracle9i than in earlier releases. The Oracle9i default for the initialization parameter O7_DICTIONARY_ACCESSIBILITY is now FALSE, whereas in all prior releases the default was TRUE.

There are two side effects of this parameter being set to FALSE:

• CONNECT SYS/PASSWORD does not function; you get the ORA-28009 error.

• Access to the "real" data dictionary owned by SYS is not available to users, even if they have the SELECT ANY TABLE privilege. It isn't the data dictionary views, such as ALL_OBJECTS, but rather the base tables, such as SYS.OBJ$, that are inaccessible.

The first side effect—that a SYS/ PASSWORD connection will not work without using SYSOPER or SYSDBA—is not clearly documented anywhere. But it's the O7_DICTIONARY_ACCESSIBILITY init.ora parameter that causes this.

The other side effect—that the "real" data dictionary is not accessible to normal users—is well documented. That is, after all, the purpose of this initialization parameter. That said, there is (of course) a caveat to this. Portions of the documentation still state:

If this parameter
[O7_DICTIONARY_ACCESSIBILITY] is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privilege. Also, the following roles, which can be granted to the database administrator, also allow access to dictionary objects:
SELECT_CATALOG_ROLE,
EXECUTE_CATALOG_ROLE, and
DELETE_CATALOG_ROLE.

But in Oracle9i, that is not accurate. There is a new system privilege—SELECT ANY DICTIONARY—that permits access to the SYS schema (and, by default, the DBA role has this privilege granted to it). The "Oracle9i Database README Release Notes"—included with the Oracle9i database software—describe this change. The following is an excerpt from the release notes:

18.3 Data Dictionary Protection

Data dictionary protection is now enabled by default. Specifically, the O7_DICTIONARY_ACCESSIBILITY init.ora parameter is set to FALSE on installation. As a result, regular users who are not database administrators with ANY privileges (for example, SELECT ANY TABLE) can no longer use the ANY privilege upon data dictionary objects; however, the user can access non-SYS schema objects using the ANY privilege.

A new system privilege, SELECT ANY DICTIONARY, provides users with SELECT access to any object in the SYS schema without giving them DBA privileges.

Oracle recommends that the dictionary protection feature remain enabled as it is a more secure configuration. If O7_DICTIONARY _ACCESSIBILITY is set to TRUE, then regular users with the ANY privilege can use these privileges—perhaps maliciously—to alter data dictionary objects.

My recommendation is to leave O7_DICTIONARY_ACCESSIBILITY set to FALSE and change scripts that connect as SYS to connect as another user. Avoid using the SYS account. Never use SYS to create objects. You cannot use SYS to create triggers, and some commands (such as set transaction read only) don't work when connected as SYS.

I recommend this because it is more secure than leaving the data dictionary open to casual browsing and possible modification by end users. Additionally, since this is the default in Oracle9i, you will undoubtedly start seeing more and more databases with this set to FALSE. So consider SYS to be a special account that you never need to use anymore.

How can I fetch the last record from a table? In this case, the number of records in a table is unknown. I want a query that will fetch all the columns of the last record. I have tried using ROWNUM, but I guess it doesn't work that way. If I generate a report, I want to base my report on the same query that will simply fetch all the columns of the last record. Are there built-ins in Report Builder that can fetch a table's last record?

What is your definition of the "last record"? If it is the last record inserted, there is no way to get that unless you have some field you maintain to allow you to find the last record inserted.

The last record physically inserted into a table T may very well be the first record returned by SELECT * FROM T; it may be the 100th; it may be the 1,000th; it may, in fact, be any record in the result set. Rows in a table in a relational database are not returned in a specific order.

If you want the last record inserted, you need to have a timestamp or sequence number assigned to each record as it is inserted. For example, in Oracle8i and before, you could:

create table t ( …., timestamp_column
date default SYSDATE );

And in Oracle9
i, you can:

create table t ( …, timestamp_column
timestamp default
systimestamp );

The TIMESTAMP datatype in Oracle9i provides date/time stamps with fractional seconds (with less chance of two rows with the same exact timestamp). Now you can query:

select * from t
where timestamp_column = ( select max(timestamp_column) from t )
and rownum = 1;

That gets the "last" record. We still need to use "and rownum = 1" to prevent getting more than one record back (in case two or more people inserted into the table at the same time).

Some people say that you can use the ROWID or ROWNUM column to get the last row inserted. They are incorrect, as Listing 2 demonstrates. If using the ROWID or ROWNUM column gets the last row, the answer to the query in Listing 2 would be "4," because I inserted rows 1, 2, 3, and 4 in order.

But Listing 2 shows that the answer is always "3," and this is because the ROWID for row 4 reuses the ROWID from the deleted row 2. ROWIDs are based on files, blocks, and slots on blocks. ROWIDs are reused, and ROWIDs can change (in partitioned tables and index-organized tables—IOTs—for example). You might have extent 1 in file 55 and extent 2 in file 2. Extent 4 might start on block 555 of file 3; extent 5 might start on block 2 of file 3. This means that when you order by ROWID, you are ordering by physical position on disk and not by time of insertion.

Here are failed attempts to get the last row by using ROWID and ROWNUM:

select x from (select *
from t order by rowid desc)
where rownum = 1;
X
----------
3
select x from t minus select x from t
where rownum < (select count(*) from t);
X
----------
3

ROWNUMs are assigned to rows as they are selected in the result set. Since you tend to see rows in the order they are physically stored on disk, the query assigns them row numbers in that order. The very first row we hit may, in fact, be the last row inserted into the table.

To find the "last row" inserted, you must create and maintain a column that orders the data. In Oracle9i, the TIMESTAMP datatype is recommended because it allows for fractional seconds; in Oracle8i, the DATE datatype stores time only to the second. Another approach is to use a SEQUENCE to assign increasing numbers to the rows. This has the advantage that each row will have a unique number, whereas the use of a DATE or TIMESTAMP column creates the risk of two rows with the same timestamp.