DBA-Philiac

select /*+ rule */ aa.object_name,aa.owner,LL.LOCKED_MODE, DECODE (LL.LOCKED_MODE, 0, 'none', 1, 'null', 2,'Row- Share', 3,'Row- exclusive', 4,'Share', 5,'Share /Row- exclusive', 6,'exclusive' ) Locked_Mode_Detail, SES.PROGRAM ,LL.SESSION_ID,SES.SQL_ID ,TXT.LAST_ACTIVE_TIME, TXT.SQL_FULLTEXT from dba_objects aa, v$locked_object ll, v$session ses, V$SQLAREA txt where aa.object_id = LL.OBJECT_ID and LL.SESSION_ID=SES.SID and TXT.SQL_ID=SES.SQL_ID; What queries are going on in my database on a particular object select AR.PARSING_SCHEMA_NAME, AR.EXECUTIONS, AR.ELAPSED_TIME/1000000 "ELAPSED_SECOND", AR.ROWS_PROCESSED,AR.MODULE,AR.LAST_LOAD_TIME, AR.LAST_ACTIVE_TIME,AR.SQL_FULLTEXT from v$sqlarea ar where trunc(AR.LAST_ACTIVE_TIME,'MI') = trunc(sysdate,'MI') and upper(AR.SQL_FULLTEXT) like '%SAMPLE_TEXT%'; Total query list in my database SELECT parsing_schema_name,LAST_LOAD_TIM

Hi All, This blog depicts - configure files regarding send mail service - collect an output from sql - send that output to recipients through mail (office domain) configuration of send mail service file: install the rpm of send mail up the service "service sendmail restart" vi /etc/mail/submit.cf D{MTAHost}[192.168.x.x] --this will be the smtp ip then save and quit service sendmail restart collect an output from sql This is very simple that you all can generate. Also this link is very useful to do that http://www.oracle-base.com/articles/misc/OracleShellScripting.php send that output to recipients through mail (office domain) Okay now is the most important part. To attach that file and send it echo | mutt -a list_"$CURR_TIME".txt -s FileName_"$CURR_TIME" x@y.com,z@y.com < mail_body.txt Here the attachment file name is list_"$CURR_TIME".txt This is a technique to zip a directory in linux without using tar. I have used as I have to send

Hi All, Now what i am writing, I believe many of new bees will require it while using sqlldr for ETL or any other data loading issue. The thing is while loading using sqlldr there is no option to enter the source file name in that table. So what I have done I just create a dynamic control file which generates before loading of each source file. Here I have used two scripts 1) proc1: for filtering files and running a loop. Every source file will be processed in this loop. 2) another procedure proc2 which is called by proc1 to perform load operation proc1 ------- while (( $i < $filecounter )); do echo "file number $i" ### Take the first File FILENAME=`ls -l *.txt|head -1|awk -F' ' '{print $9}'` dos2unix $FILENAME ### if filename exists already continue the loop ### FINDSQLSCRIPT="select 'COUNT:'||count(*) from TBLBRACSUMMARY where file_name='$FILENAME';" OUTPUT_FILECOUNT=`/app/u01/oracle/prod

Precaution: ----------- 1. Be sure that your oracle user on destination host is owned by "dba" secondary group and "oinstall" primary group other wise you will get RMAN-04005: error from target database: ORA-01031: insufficient privileges 2. Create a password file Steps to restoration: --------------------- ----- untar the file containing backup pieces tar -zxvf /app/u02/oradata/full_rman_backup_190911.tar.gz copy all archivelog (near date of full backup) to archiveloglocation ----- mount the database with control file export ORACLE_SID=orcl rman target / nocatalog set dbid xxxxxxxxx startup nomount pfile='/app/u01/oracle/product/10.2.0/db_1/dbs/initorcl.ora' restore controlfile from '/app/u03/oradata/full_rman_backup_190911/o1_mf_s_762246924_77f5xflw_.bkp'; alter database mount; ---- check the datafile, logfile information and find the scn number sqlplus / as sysdba select group#, to_char(first_change#), status, archived from v$log order

First select all the parent table of your desired exported tables. select parent.owner || '.' || parent.table_name "Parent table", 'is parent of' " ", child.owner || '.' || child.table_name "Child table" from dba_constraints child join dba_constraints parent on child.r_constraint_name = parent.constraint_name and child.r_owner = parent.owner where lower(child.table_name) in ( 'tblmcu', 'tblmcup', 'tblmcuy', 'tbltacc', 'tbltdebitdocument'); ------------- Find the distinct table name if you want select distinct (aa.Parent_table) from ( select ''''||parent.table_name||''',' Parent_table from dba_constraints child join dba_constraints parent on child.r_constraint_name = parent.constraint_name and child.r_owner = parent.owner where lower(child.table_name) in ( 'tblmcu', 'tblmcup&

Create user: oracle Create group: oinstall,dba Add oinstall,dba to user oracle groupadd oinstall groupadd dba useradd oracle -g oinstall -G dba passwd oracle [ for OSDBA select dba group, OS users under this group will get SYSDBA privilege for OSOPER select oinstall group, OS users under this group will get SYSOPER privilege ] chown oracle /opt/oracle/ chgrp dba /opt/oracle/ increase "tmpfs(/dev/shm)" to >= "memory_max_target" (If use autometic memory management) unzip two disks in the same folder like below, o therwise some .ear file missing error may occur: ---------------------------------------- unzip linux_11gR2_database_1of2.zip unzip linux_11gR2_database_2of2.zip cd database ./runInstall ---------------------------- append below lines in file:/etc/sysctl.conf # Controls the maximum number of shared memory segments kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 6815744 fs.aio-max-nr=1048576 net.ipv4.ip_local_port_range=9000 65500 net.core

1 Upgrade Oracle Database from 10.2.0.4 to 11.2.0.1 1.1 Pre-installation Tasks - Upgrade 10.2.0.1 to any upper version (we upgraded to 10.2.0.4) - Install Oracle Database 11gR2 software (only binary, no database and must be in different ORACLE_HOME ): 1. In our case we install in in same ORACLE_BASE but Different ORACLE_HOME 2. on RHEL5R4 some kernel parameter value needed to be changed and some rpm needed to be installed. Change those and use sysctl –p (from root) to reload kernel parameters. 3. In case of installing unix-ODBC and unix-ODBC-devel rpm, first install unix-ODBC and unix-ODBC-devel to avoid dependency warnings. 4. ./runinstaller (install Software only) 5. After resolving all requirements use check again button to recheck. 6. Press Next and complete SW installation process. 7. Incase of running $ORACLE_HOME(new)/root.sh accept all default values. - Run the Pre-Upgrade Information Tool 1

1. Patching Oracle Database 10.2.0.1 to 10.2.0.4 1.1 Pre-installation Tasks - Identify Oracle home directories, view the /etc/oratab file - Download the p6810189_10204_Linux-x86.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory (patch set id: 6810189 ) - Check the current version of the Oracle time zone definitions with the following command: SELECT version FROM v$timezone_file; If this query reports version 4, no action is required; If this reports a version lower or higher then 4: Check if you are storing TZ (TSTZ and TSLTZ) data use this sql query: select c.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col from dba_tab_cols c, dba_objects o where c.data_type like '%TIME ZONE' and c.owner=o.owner and c.table_name = o.object_name and o.object_type = 'TABLE'