On Different Users, Different Passwords, and Hidden Ability

Our technologist explores passwords, undocumented functionality, a different export, and different SGAs.

September/October 2001

I am running Oracle8i Release 8.1.7 Enterprise Edition on Solaris. I created two databases: SUGAR and TestDb. Both have set the remote_login_passwordfile to "exclusive." The following shows how I tried to connect to TestDb as SYSDBA but could not.

$sqlplus /nolog
SQL> conn sys/change_on_install@testdb
Connected.
SQL> select * from v$pwfile_users;
username      sysdb      sysop
-----------------------------------------
internal        true            true
sys             true            true
SQL> conn sys/change_on_install@testdb as
sysdba
ERROR
ORA-01017: invalid username/password; logon
denied
Warning: You are on longer connected to
ORACLE

Why can't I connect to TestDb as SYSDBA?

Typically, the SYS and INTERNAL passwords are synchronized, and the SYS password is stored in the password file. In your case, however, you created the password file with a password other than the default change_on_install, and that is the problem here.

Hopefully, this will help. Start by creating a password file with a password that does not match the SYS password:

$ orapwd file=orapw password=foobar
entries=40

Next, go into server manager and start up the database:

$ svrmgr
SVRMGR> connect internal
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area
193073136 bytes
Fixed Size
69616 bytes
Variable Size
141639680 bytes
Database Buffers
45056000 bytes
Redo Buffers
6307840 bytes
Database mounted.
Database opened.

Now connect as SYS, using the SYS user's password:

SVRMGR> connect sys/change_on_install@ora81
Connected.

And that works. Now try connecting as SYSDBA:

SVRMGR> connect sys/change_on_install@ora81
as sysdba;
ORA-01017: invalid username/password; logon
denied

Here you get your error. Your SYS password is change_on_install, but the password file has foobar as the password. The SYS user is special. SYS connecting as SYSDBA is like connecting as INTERNAL—you have to use the password file password! Try that:

SVRMGR— connect sys/foobar@ora81 as sysdba;
Connected.

Everyone does not need to use the password file password; users need to use their own passwords. You can see this by granting SYSDBA to SCOTT:

SVRMGR> grant sysdba to scott;
Statement processed.

This adds SCOTT to the password file with SCOTT's credential. If you change SCOTT's password, it will be synced up to the password file automatically. Now, test that you can in fact connect SCOTT as a SYSDBA:

SVRMGR> connect scott/tiger@ora81 as sysdba;
Connected.

That worked well. Now just modify the SYS user's password via the ALTER USER command:

SVRMGR> alter user sys identified by
change_on_install;
Statement processed.
SVRMGR≫ connect sys/change_on_install@ora81
as sysdba;
Connected.

You can use change_on_install, because altering the SYS user's password syncs up the password in the password file. When you created the password file, the Oracle database put two accounts in there—SYS and INTERNAL—and used the password you provided it on the command line as the password for both accounts. When you alter the SYS user's password in the database, the database overwrites the SYS and INTERNAL password in the password file. The following shows that the password foobar is no longer valid:

SVRMGR> connect sys/foobar@ora81 as sysdba;
ORA-01017: invalid username/password; logon
denied

I know that Oracle8i can export table data selectively by using QUERY. Below is the EXP command I wrote to do this, but the command failed. Here are the command and error message :

exp ddd/ddd file=/dbf/u11/customer.dmp
  tables=AASC.AST_CUSTOMER_KEEP
query=\'where CUA_TRANS_DTS \<
  add_months\(sysdate, -6\)\'
table_export[2]: CUA_TRANS_DTS:  not found.

The method by which you specify the QUERY= parameter depends on the operating system. A WHERE clause generally has many special characters in it, such as =, > ,<, and spaces. The shell command prompts in UNIX and Windows are not going to like those characters. The characters will have to be escaped, and how you do that is OS-dependent. I prefer to always use a parameter file ( PARFILE) with the QUERY option. With a PARFILE, I can use the same exact methods regardless of platform.

As an example of this, I have created a table T as select * from all_objects. I want to export all rows such that the object_id is less than 5,000. On Windows, I would have to execute:

C:\exp>exp userid=tkyte/tkyte tables=t
query="""where object_id < 5000"""

Note that I need three double quotes on each side of the WHERE clause in Windows. On UNIX, I must use:

$ exp userid=/ tables=t query=\"where
object_id \< 5000\"

But, if I use a PARFILE that includes:

query="where object_id < 5000"

I can now use the single command:

exp userid=/ tables=t parfile=exp.par

on both systems without change. This is much easier than trying to escape the QUERY strings on each platform.

Could you please tell me the best way to write a random number generator that can generate random numbers greater than 0 and less than 1?

Oracle8 Release 8.0 introduced the DBMS_RANDOM package. Oracle8i Release 8.1.6 introduced new functionality for the DBMS_RANDOM package, but the functionality wasn't fully covered in the Oracle8i documentation. Fortunately for you, one of the new DBMS_RANDOM package functions returns a random number between 0 and 1. The new functions are:

FUNCTION value RETURN NUMBER;
FUNCTION value (low IN NUMBER, high IN
NUMBER) RETURN NUMBER;
FUNCTION normal RETURN NUMBER;
FUNCTION string (opt char, len NUMBER)
RETURN VARCHAR2; 

The first version of VALUE returns a random number that is greater than or equal to 0 and less than 1. The second version of VALUE returns a random number that is greater than or equal to LOW and less than HIGH. Here is an example of their usage:

SQL> select dbms_random.value,
dbms_random.value(55,100)
  2  from dual;
          VALUE   DBMS_RANDOM.VALUE(55,100)
---------------   -----------------------------
     .782821936   79.6367038

The NORMAL function is designed to return a set of numbers with a normal distribution. This normal distribution has a standard deviation of 1 and is centered on the number 0. Therefore, you would expect about 68 percent of the values returned by this function to be between 1 and +1, 95 percent to be between 2 and +2, and 99 percent to be between 3 and +3. And, in fact, that is what you can see in Listing 1.

Last, there is the STRING function. This returns a random string of characters up to 60 characters in length. The OPT parameter is a single character that may be any of the values shown in Listing 2.

For documentation on these functions and the DBMS_RANDOM package, issue the following in SQLPlus:

select text
  from all_source
 where name = 'DBMS_RANDOM'
   and type = 'PACKAGE' order by line;

In the following query, which part of the WHERE clause is performed first?

Select field names from emp, dept
where emp.dept_num = dept.num and
    emp.name Like 'S%' and dept.name=
'IT';

That depends. The optimizer will decide based on existing indexes, statistics, and session/init.ora parameters.

Say that you have an index on DEPT(name) and EMP(dept_num). Suppose the optimizer sees that DEPT(name) is unique. The opti- mizer will probably do the following, in order:

1. Use the index on DEPT(name) to find the dept rows

2. Use the index on EMP(dept_num) to find the matching emp rows (this is the join emp.dept_num = dept.num )

3. Filter on emp.ename like 'S%'

Now, let's say that there is no index on EMP(dept_num), no index on DEPT(name), and indexes do exist on EMP(name) and DEPT(num). The optimizer will probably use this order:

1. Use the index on EMP(name) to find the emps with S's

2. Use the index on DEPT(num) to find the matches

3. Filter the result on dept.name = 'IT'

The order of evaluation of the predicate is not defined, not assured, can and will change over time, and is decided by the optimizer. Do not assume anything happens in a certain order. If you do, you could encounter seemingly strange errors in your application over time. Consider the following example: Start by creating a table and putting some data in it. The data in the second column Y is a number value when X = 'a' and is not a number when X = 'b':

SQL> create table t ( x varchar2(1),
y varchar2(1) );
Table created.
SQL> insert into t values ( 'a', '1' );
1 row created.
SQL> insert into t values ( 'b', 'x' );
1 row created.

Now run a query against this table that looks for rows where X = 'a' and Y is 1.

SQL> select * from t where x = 'a' and
y = 1;
ERROR:
ORA-01722: invalid number
no rows selected

Whoops—this doesn't work. In this case, the database evaluates the Y=1 first, and when it hits the row where Y = 'x', it cannot convert 'x' into a number (obviously), and it fails. The following, however, gives a different result:

SQL> analyze table t compute statistics;
Table analyzed.
SQL> select * from t where x = 'a' and
y = 1;
X Y
- -
a 1

Use a different optimizer mode, and voilà—it works! Why? The optimizer says "Hey, it will be faster to check x='a' than to do y=1, since y=1 includes a conversion of y from a string to a number. I'll do x='a' first and then y=1."

The moral of this story is that the order of evaluation of a predicate is purposely indeterminate. You cannot count on a specific order of operation. That means you must be careful when you are counting on an implicit conversion.

When we run "show sga" at the svrmgr prompt, what does it mean by fixed size and variable size?

The fixed size is the size of a fixed component of the SGA (fixed at the compile time of the Oracle database itself). It is a fixed amount of memory we set aside to point into other pieces of the SGA. You cannot influence the size of this part of the SGA.

Variable size is where varying-sized chunks of memory are allocated. You will find the shared pool, large pool, Java pool, cursor area, and many other structures inside the variable size of the SGA.