Our technologist explores passwords, undocumented functionality, a different export, and different SGAs.
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: 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
Oracle Managing Technologist Tom Kyte answers your most difficult Oracle technology questions in Oracle Publishing Online's Ask Tom forum, at asktom.oracle.com. Highlights from that forum appear in this column.
VISIT docs.oracle.com for free access to downloads of installation and configuration guides, release notes, and other Oracle documentation. To purchase hardcopy Oracle documentation, visit oraclestore.oracle.com.
MORE INFORMATION on the DBMS_RANDOM PL/SQL package in Oracle 9i, refer to the alphabetical listing of SQL and PL/SQL syntax and examples at tahiti.oracle.com.
READ more Tom. Tom Kyte has written a book on Oracle technology, appropriately titled Expert One on One: Oracle and published by Wrox Press. To purchase Tom's book or to get more information, visit www.amazon.com/exec/obidos/
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:
Use the index on DEPT(name) to find the dept rows
Use the index on EMP(dept_num) to find the matching emp rows (this is the join emp.dept_num = dept.num )
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:
Use the index on EMP(name) to find the emps with S's
Use the index on DEPT(num) to find the matches
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.
Columnist Tom Kyte ( email@example.com) has worked with the Oracle Services Industry Technology Group, in Reston, Virginia, since 1993.