FAQ of ORACLE ++++++++++++++ ****** Common SQL*Plus Questions *********** -------------------------------------------------------- Q: How to find the days between two dates excluding weekends (Saturdays and Sundays) select (trunc(enddate,'D')-trunc(begdate+6,'D')) + mod(7-to_number(to_char(begdate,'D')),6) + least(to_number(to_char(enddate,'D'))-1,5) DAYS Q: How to use dbms_output.put_line ? set serveroutput on declare Status number; begin dbms_output.enable; status := 1234567890; dbms_output.put_line('status: ' || to_char(status)); end; / Q: How To 'spell' a number You can use a date function to do this.... select to_char( to_date(5373484,'J'),'Jsp') from dual TO_CHAR(TO_DATE(5373484,'J'),'JSP') -------------------------------------------------------------------------- Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four This will work for numbers between 1 and 5,373,484... You can take it a step further to support numbers -5,373,484 .. 5,373,484 by select decode( sign( :N ), -1, 'Negative ', 0, 'Zero', NULL ) || decode( sign( abs(:N) ), +1, to_char( to_date( abs(:N),'J'),'Jsp') ) from dual / *********************** ** GENERAL QUESTIONS ** *********************** *** What are 'coraenv' and 'oraenv'? These are two shell scripts. 'coraenv' runs in the C-shell while 'oraenv' runs in the Bourne Shell. If you have multiple databases on a machine, you can put the 'coraenv' script in your '.login' (C-shell). This will then prompt you for the SID of your choice. Remember, your $ORACLE_SID determines which database you intend to point to. 'coraenv' will then look up '/etc/oratab' and set up your environment variables correctly such as $ORACLE_HOME, $ORACLE_SID and $PATH. 'oraenv' accomplishes the same thing in the Bourne shell (In the Bourne shell, the equivalent to '.login' is '.profile') *** What is the 'setuid' bit? The 'setuid' bit is the 's' bit set for the oracle executable. By setting oracle to permission 6755, any process executing oracle will have its effective 'uid' to be that of oracle. (eg. -rwsr-sr-x 1 oracle dba .......................) If the setuid bit is not set, only the oracle user will be able to login to the database, all other users will get an ORA-7320 error. *** If I had to look out for permissions on certain files, which ones are really important? The most crucial is the 'oracle' and 'dbsnmp' executables under '?/bin'. 'oracle' should be 'rwsr-sr-x' owned by 'oracle'. To set this type the following at the command line: % chmod 6755 oracle *** Is there a diagnostic script for determining the customer's current working environment? Yes, the command 'unixenv' from the '$ORACLE_HOME/orainst' will give a complete diagnostic. To spool it to a file, do the following from the command line: % $ORACLE_HOME/orainst/unixenv >> unixenv.log The following information can be viewed: User (includes user name and group) Ulimit Swap Disk Mounts Disk Free (free disk space for all mounted devices) Environment (all current environmental variables) System Tables Semaphores Shared Mem /etc/oratab Tracing (what debugger is available) *** What is the System Global Area or the shared memory region? The System Global Area (SGA) is a shared memory region allocated by ORACLE in the system memory (RAM) that contains data and control information for one ORACLE instance. An approximation to the size of SGA is: SGA = (db_block_buffers * db_block_size) + log_buffers + shared_pool_size where db_block_buffers: The number of database blocks cashed in memory in the SGA. db_block_size : The size in bytes of ORACLE database blocks (This parameter may be specified in the config.ora' file that your database's 'init.ora' file calls) log_buffers : The number of bytes allocated to the redo log buffer in the SGA. shared_pool_size: The size of the shared pool in bytes. The shared poolcontains shared cursors and stored procedures. *** How do I start investigating the use of shared memory segments and semaphores at a Unix level? Refer to the commands - 'ipcs' and 'ipcrm'. 'ipcs -b' is very useful. It shows the shared memory and semaphores currently in use. *** I want to move my datafile to a different partition because I need more space. Can I do this? Yes. You need to do the following: 1. % svrmgrl shutdown normal exit 2. At the O/S level: copy datafile to new location. (do not use 'mv'). % cp $ORACLE_HOME/dbs/dbsNEW.dbf /usr3/oracle/dbsNEW.dbf 3. svrmgrl startup mount connect internal alter database rename file '<$ORACLE_HOME>/dbs/dbsNEW.dbf' to '/usr3/oracle/dbsNEW.dbf'; alter database open. 4. You are free to remove the first copy of the file now. NOTE: you can also do this while the db is running by taking the tablespace offline (alter tablespace offline); the only exception is the system tablespace. *** How do I correct an ORA-01562: failed to extend rollback segment" First, check to ensure all rollback segments are online. SQL> select status, SEGMENT_NAME, owner, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS from dba_rollback_segs order by status, SEGMENT_NAME; If they are OFFLINE: alter rollback segment rb1 online; If they have initial_extent or next_extent less than what the Install Manual requires (for example, 1M and 1M): alter rollback segment rb1 storage (inital 1M next 1M); *** How to resolve "ORA-1194: file 1 requires more recovery to be consistent" error when creating a database from a backup? Try the following SVRMGRL> recover using backup controlfile ORA-00279: Change 7187 generated at 01/01/97 14:50:09 needed for thread1 ORA-00289: Suggestion : /home/applrt/db/ap560db1/dbs/arch1_18.dbf ORA-00280: Change 7187 for thread 1 is in sequence #18 Specify log: {=suggested | filename | AUTO | FROM logsource | CANCEL} Type in the archive log file name, eg. YOU TYPE IN> /home/applrt/db/ap560db1/dbf/log1ap560db1.dbf Applying logfile... ORA-00310: archived log contains sequence 17; sequence 18 required ORA-00334: archived log: '/home/applrt/db/ap560db1/dbf/log1ap560db1.dbf' Specify log: {=suggested | filename | AUTO | FROM logsource | CANCEL} This means that the log file you typed in did not have the sequence that it needed, so try the other log file. SVRMGRL> recover using backup controlfile YOU TYPE IN> /home/applrt/db/ap560db1/dbf/log2ap560db1.dbf Applying logfile... Log applied. Media recovery complete. Now, you can do the alter database open resetlogs command. *** What to check for when connect internal in SVRMGRL asks for a password? Make sure your userid is same as that of the owner of ORACLE_HOME and RDBMS executables. Make sure the executable $ORACLE_HOME/bin/oracle has rwxs privileges. $ chmod u+s $ORACLE_HOME/bin/oracle *** Can one resize tablespaces and data files? You can manually increase or decrease the size of a datafile using the ALTER DATABASE DATAFILE 'filename2' RESIZE 100M; command. Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database. Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements. Also, datafiles can be allowed to automatically extend if more space is required. Look at the following command: CREATE TABLESPACE pcs_data_ts DATAFILE 'c:\ora_apps\pcs\pcsdata1.dbf' SIZE 3M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED DEFAULT STORAGE ( INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) ONLINE PERMANENT; *** How can I become another user in Oracle? Of course it is not advisable to bridge Oracle's security, but look at this example: select password from dba_users where username='SCOTT'; PASSWORD ----------------------------- F894844C34402B67 alter user scott identified by lion; connect scott/lion REM Do whatever you like... connect system/manager alter user scott identified by values 'F894844C34402B67'; ***** What does it mean when a rollback shows 'NEEDS RECOVERY'?? A rollback segment with a status of NEEDS RECOVERY has failed to rollback an uncommitted transaction in it's transaction table. The most common reason for failing to rollback a transaction is because the object is in an offline datafile or tablespace. After that, the next most common reason is that the object that needs to have undo applied to it is corrupt or the rollback segment itself is corrupt. ***** What is the difference between RECOVER DATABASE and RECOVER DATABASE MANUAL? RECOVERY DATABASE is complete recovery which implies that online data files need to roll forward until the last online redo logs. This also implies that you haven't lost the online redo logs, and that you are issuing recovery with the current control file. RECOVER DATABASE MANUAL says that there will be some form of incomplete recovery. Incomplete recovery needs to result in an ALTER DATABASE OPEN RESETLOGS. You can stop at any time by typing 'cancel'. In addition, recover database manual is needed if you are using a backup of the controlfile, even if you plan to recover all the way through the online redo logs. Oracle, has many variations to the 'recover database manual' command, in fact the 'recover database manual' DOES NOT exist in Oracle7, instead we have: - recover database until cancel - recover database until time xxx - recover database until change xxx - recover database using backup controlfile. **** Is there a way to identify all users with active transactions and the rollback segment each transaction is using? The way to tell if users have active transactions, is to look at the locks that are on the system. Whenever a user has an active transaction in a rollback segment, he will also have an exclusive TX lock. The information with the TX lock gives the rollback segment name. Below are a few queries that give you the information on the users and the rollback segment they are using. SELECT r.name "ROLLBACK SEGMENT NAME ", l.sid "ORACLE PID", p.spid "SYSTEM PID ", s.username "ORACLE USERNAME" FROM v$lock l, v$process p, v$rollname r, v$session s WHERE l.sid = p.pid(+) AND s.sid=l.sid AND TRUNC (l.id1(+)/65536) = r.usn AND l.type(+) = 'TX' AND l.lmode(+) = 6 ORDER BY r.name / ****** How does oracle determine which rollback segment to use? Oracle assigns rollback segments based on the following rules: 1. always assign to rbs which has least number of active txns 2. if two or more rbs have the same "least number of active txns", the assign to the one which is after the last one used. This ensures that undo is kept for a longer time. NOTE: if a transaction is outside the system tablespace, the system rollback segment is not counted in this round-robin method. *** I already have a database up and running. I would like to change the name of the dba group. How do I do this? You will have to do the following: 1) change the dba name in ?/rdbms/lib/config.s file. Note: make sure you have a backup copy. 2) change the dba name in /etc/group file. 3) shutdown database. 4) relink oracle executable. (see Q. 38) 5) startup database. *** I am missing a executable for e.g "svrmgrl" in "$ORACLE_HOME/bin". How can I create a new one? The product executables and other kernel related executables can be generated as follows: % cd $ORACLE_HOME//lib % make -f ins_.mk install This will generate the executables for the product AND move them to the "?/bin". Therefore for the "svrmgr" product the command will be: % cd $ORACLE_HOME/svrmgr/lib % make -f ins_svrmgr.mk install *** I am missing the svrmgrl executable in ?/bin. How can I create a new one? The svrmgrl executable (and other kernel related executables) can be generated on site by using the following command: make -f oracle.mk svrmgrl make -f oracle.mk imp make -f oracle.mk exp (etc ... you get the idea) The above command has to be run in ?/rdbms/lib. The executables have to then be manually moved to the ?/bin directory. To automate this process you can type make -f oracle.mk install This will generate ALL the rdbms related executables AND move them to ?/bin. Note: You can do this to generate any product executable also, simply by using the appropriate .mk file. *** Can I manually relink ORACLE without running 'orainst'? Yes. Do the following: Shutdown the database. % cd $ORACLE_HOME/rdbms/lib % make -f oracle.mk oracle OR % make -f ins_rdbms.mk oracle (for V7.3 and higher) This will create a copy of the oracle executable in the '$ORACLE_HOME/rdbms/lib' which can then be copied over to '$ORACLE_HOME/bin'. As a last step, change the permissions of 'oracle' to 6755 and make sure that it is owned by the 'oracle' user. Note that if you use 'install' as a target as in: % make -f oracle.mk install OR % make -f ins_rdbms.mk install (for V7.3 and higher) This will generate ALL the RDBMS related executables AND move them to '?/bin' automatically.