Oracle DBA Place

You should have an outgoing SMTP server IP to configure sending mail from the database. I already put it in the database server /etc/hosts as mailhost sqlplus / as sysdba @?/rdbms/admin/utlmail.sql @?/rdbms/admin/prvtmail.plb grant execute on UTL_MAIL to public; ALTER SYSTEM SET smtp_out_server = 'mailhost' scope=both; a simple example BEGIN UTL_MAIL.send(sender => 'Yossi@NixonIT.com', recipients => 'you@address.com', subject => 'Test Mail', message => 'Hello World', mime_type => 'text; charset=us-ascii'); END; / For further advanced options such as attachments see in this wiki

Data Pump import as sysdba and several indexes, the unix way .... impdp "'sys/dba as sysdba'" schemas=SCOTT INCLUDE=INDEX:\"in \(\'PK_DEPT\',\'EMPIDX\'\)\" directory=TMP_DIR dumpfile=scott.dmp job_name=importing_scott.log

ORA-600/ORA-7445 are generic internal error numbers for Oracle program exceptions. Sometimes these errors are unique for your specific problem and cannot be found via search engines. Using "ORA-600 lookup tool" may point your specific problem, faster and accurate. The tool can be found in ORA-600 lookup tool - Metalink Document ID 153788.1 The flowing video will guide you the usage of the LookUp Tool (11:12) - Metalink Document ID 1082674.1

Looking for installed patches on the database I have always used opatch lsinventory (since 9.2 and up) From CPUJan2006 onwards you can just query select * from registry$history; See Metalink Note:352783.1 for more information.

installing or upgrading on Ubuntu is not supported but can be done thanks to other bloggers it is well documented here https://lostinmac.com/2012/01/29/installtion-doracle-11gr2-sur-ubunutu-11-10/ thanks

before oracle 11.2 we should have do this steps: cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk dv_off cd $ORACLE_HOME/bin relink all from now this is simpler and shorter cd %ORACLE_HOME%/bin chopt disable dv All options are written in the usage syntax: usage: chopt [enable|disable] {option} options: dm = Oracle Data Mining RDBMS Files dv = Oracle Database Vault option lbac = Oracle Label Security olap = Oracle OLAP partitioning = Oracle Partitioning rat = Oracle Real Application Testing

ADRCI & alert file In Oracle version 11, no need to look for the location of alert log file, just use: adrci exec="set home orcl ;show alert -tail -f" detailed practical usage can be found here

The purpose of this article is to simulate a block level corruption using BBED utility (block browser and editor) and recover using RMAN. In this situation the data file remains online throughout the recovery operation and hence other segments within the tablespace remain accessible. Since BBED exists from Oracle7 to Oracle10g, we will have to copy some files from earlier version and compile it Cp $ORA10g_HOME/rdbms/lib/ssbbded.o $ORA11g_HOME/rdbms/lib Cp $ORA10g_HOME/rdbms/lib/sbbdpt.o $ORA11g_HOME/rdbms/lib Message files (list may differ): Cp $ORA10g_HOME/rdbms/mesg/bbedus.msb $ORA11g_HOME/rdbms/mesg Cp $ORA10g_HOME/rdbms/mesg/bbedus.msg $ORA11g_HOME/rdbms/mesg Cp $ORA10g_HOME/rdbms/mesg/bbedar.msb $ORA11g_HOME/rdbms/mesg Issue the following command: make -f $ORA11g_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed $ORA11g_HOME/bin/bbed password: blockedit SQL> Set pages 0 SQL> set feedback off SQL> spool fileunix.log SQL> select fi...

On linux redhat 5 I checked the command ps -ef in a loop and found out that it is not reliable. checked this way: while [ `ps -ef |grep tnslsnr | grep -v grep | wc -l` -eq 1 ]; do printf . ; done after about of 2 minutes the loop finished since it didn't find the process. looking for something more trusted I found the command pgrep checked this way: while [ `pgrep tnslsnr 1>/dev/null; echo $?` -ne 1 ]; do printf . ; done and it is still running in a loop .... ;) and no need to use awk or grep -v here are some commands and the behavior of pgrep : # pgrep smon # pgrep -f smon 2396 2533 # pgrep -fl smon 2396 ora_smon_orcl 2533 ora_smon_mydb # pgrep -fl ora_smon_orcl 2396 ora_smon_orcl # echo $? 0 # pgrep not_exist_process # echo $? 1 There are some more parameters, check: man pgrep

Extract the filename from a full file path unix select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name)) FROM dba_data_files; windows select substr(file_name,(instr(file_name,'\',-1,1)+1),length(file_name)) FROM dba_data_files; Validates a long filename using Windows' rules: select file_name from table_of_files WHERE not REGEXP_LIKE(file_name,'^[^\\\./:\*\?\" \|]{1}[^\\/:\*\?\" \|]{0,254}$'); combinning these two SQLs: WITH files AS ( select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name)) base_filename FROM dba_data_files) select base_filename from files WHERE REGEXP_LIKE(base_filename,'^[^\\\./:\*\?\" \|]{1}[^\\/:\*\?\" \|]{0,254}$');

A new option started from 11.1 for listing Valid Values in init.ora at the site of Jonathan Lewis http://jonathanlewis.wordpress.com/2011/03/08/valid-values/

Needed to check the validity of an Israeli identity card number I created this simple function CREATE OR REPLACE FUNCTION checkid (id_number IN VARCHAR2) RETURN VARCHAR2 IS fixed_number VARCHAR2 (10); digit NUMBER := 0; sum_digits NUMBER := 0; BEGIN CASE WHEN LENGTH (id_number) 9 THEN RETURN 'Too Long'; ELSE fixed_number := id_number; END CASE; FOR i IN 1 .. 9 LOOP digit := TO_NUMBER (SUBSTR (fixed_number, i, 1)) * (CASE WHEN MOD (i, 2) = 0 THEN 2 ELSE 1 END); IF LENGTH (digit) > 1 THEN digit := SUBSTR (digit, 1, 1) + SUBSTR (digit, 2, 1); END IF; DBMS_OUTPUT.put_line (i || '#'); sum_digits := sum_digits + digit; IF MOD (sum_digits, 10) = 0 THEN RETURN 'OK'; ELSE RETURN 'BAD'; END IF; END LOOP; RETURN TO_CHAR (sum_digits); END; / references: http://goo.gl/z2roI http://goo.gl/dCbS0

Trying to retrieve lost records from a datapump backup directly to the production database using impdp syntax: impdp User/password directory=my_directory dumpfile=my_full_backup.dmp logfile=imp_lost_records.log QUERY=MY_TABLE:\"where code=1 and recid in \(2,5,8\) \" job_name=imp_lost_records INCLUDE=TABLE:\"=\'MY_TABLE\'\" CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND

Using Autotrace SQL> set autotrace traceonly explain SQL> select ename from emp where sal > 500; Execution Plan ---------------------------------------------------------- Plan hash value: 2872589290 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------- Using DBMS_XPLAN Package SQL> explain plan for select ename from emp where sal > 500; Explained. SQL> select * from TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------ Plan hash value: 2872589290 ------------------------------------------------------------------------- |...

Using the same technique I mentioned at " Tracing Commands " here is a code in a form of logging trigger, easier when you want to capture just a specific schema from the very first transaction. CREATE OR REPLACE TRIGGER SYS. LOGON_TRACE_CRYSTAL_TRG AFTER LOGON ON CRYSTAL. SCHEMA DECLARE cmd VARCHAR2(100); BEGIN cmd := 'ALTER SESSION SET max_dump_file_size = unlimited'; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET tracefile_identifier = ''10046'''; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET statistics_level = ALL'; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET events ''10046 trace name context forever, level 12'''; EXECUTE IMMEDIATE cmd; EXCEPTION WHEN OTHERS THEN --NULL; RAISE; END ; / optional: CREATE OR REPLACE TRIGGER  SYS. LOGOFF_TRACE_CRYSTAL_TRG BEFORE LOGOFF ON CRYSTAL. SCHEMA DECLARE cmd VARCHAR2(100); BEGIN cmd := 'ALTER SESSION SET EVENTS ''10046...

Cool and simple code to format any kind of script in Unix environment awk.info » Format Shell Scripts Recommended

Background: Dynamicaly moving control files and redo log files from filesystem /dbdata1/ to /dbdata3/ and from /dbdata2/ to /dbdata4/ 1. run as sysdba export ORACLE_SID=orcl sqlplus / as sysdba create pfile='pfile_backup_orcl.ora' from spfile; SET pagesize 0 SET feedback off spool OS_command_orcl.sh SELECT 'cp ' || NAME || ' ' || CASE WHEN REGEXP_SUBSTR(NAME, '^\/[^/]+\/') = '/dbdata1/' THEN REPLACE (NAME, '/dbdata1/', '/dbdata3/') WHEN REGEXP_SUBSTR(NAME, '^\/[^/]+\/') = '/dbdata2/' THEN REPLACE (NAME, '/dbdata2/', '/dbdata4/') END OS_command FROM v$controlfile UNION ALL SELECT 'cp ' || MEMBER || ' ' ||CASE WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata1/' THEN REPLACE (MEMBER, '/dbdata1/', '/dbdata3/') WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata2/' THEN REPLACE (MEMBER, '/dbdata2/', ...

I find it usefull sending log files from linux/unix by mail, some times it is just for saving clicks instead of using ftp, and often as part of a script. Of course that the server should be configured for that, usually it is enabled by default. Sending attached file uuencode original_file_name new_file_name | mailx -s "Subject" My.Mail@Mail.COM Sending a file as text in the email body mailx -s "Subject" My.Mail@Mail.COM &lt file_name (if mailx does not exists -&gt just use mail)

Using Enterprise Manager or dbconsole is the convenient way for using SQL Advisor, the problem is that these options not always exists, and there are some scenarios that you are not authorized to activate dbconsole. So we are left with the PL/SQL option which appears to be not so complicated. In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege: CONN sys/password AS SYSDBA GRANT ADVISOR TO my_user; CONN my_user/my_password Creating tuning task DECLARE l_sql VARCHAR2(3200); l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql := 'SELECT COUNT (*) FROM MY_TABLE'; l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_text => l_sql, scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 360, task_name => 'Yossi_Nixon_tuning_task1', description => 'Tun...

Some times there are problems using installation with GUI, the reason can be: 1. Firewall 2. Unix security 3. Client software (missing or not working( and maybe some more reasons If your environment is cooperative helping you solve this - it is simple - go for it. But if you are on your own and can't reach "the right guys" - do it yourself " Silently " Look for the proper response file fits for your needs in /database/response/ Edit it and run the following command: $ ./runInstaller -force -invPtrLoc /oraInst.loc -silent -noconfig -ignoreSysPrereqs -responseFile /database/response/enterprise.rsp You can also run the installer without a response file as mentioned in Metalink Note 782918.1 $ ./runInstaller -silent -force -debug \ FROM_LOCATION="/mount/dvd/database/stage/products.xml" \ ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1" \ ORACLE_HOME_NAME="Ora11gDb1" ORACLE_BASE="/u01/app/oracle" \ TOPLEVEL_COMPONENT='{...