which sql is running on background

SELECT a.username, b.sql_text FROM v$session a, v$sqlarea b, v$process c
WHERE c.spid = '&PID' AND a.paddr = c.addr AND a.sql_address = b.address;

To Find Top List Segments Usage...

Total list
========
select SEGMENT_NAME "Table Name",sum(BLOCKS)  "Total blocks" ,
sum(bytes/1024/1024) "Size in MB" from dba_segments
where segment_name like 'MLOG$%'
group by segment_name
 order by 2 desc;

Top-20 list
=========
select * from (
select SEGMENT_NAME "Table Name",sum(BLOCKS)  "Total blocks" ,
sum(bytes/1024/1024) "Size in MB" from dba_segments
where segment_name like 'MLOG$%'
group by segment_name
 order by 2 desc)
where rownum < 20

TOP SQLs & TableSpace Free

TOP sqls :

set linesize 1000

set pagesize 1000

col sql for a50

SELECT sql,buffer_gets, executions

FROM (SELECT hash_value,address,substr(sql_text,1,40) sql,

            buffer_gets, executions, buffer_gets/executions "Gets/Exec"

      FROM V$SQLAREA

      WHERE buffer_gets > 100000 AND executions > 10

      ORDER BY buffer_gets DESC)

      WHERE rownum <= 5

/

Tablespace Free and Fragmentation [ID 1019709.6]

select
  total.tablespace_name                       tsname,
  count(free.bytes)                           nfrags,
  nvl(max(free.bytes)/1024,0)                 mxfrag,
  total.bytes/1024                            totsiz,
  nvl(sum(free.bytes)/1024,0)                 avasiz,
  (1-nvl(sum(free.bytes),0)/total.bytes)*100  pctusd
from
  dba_data_files  total,
  dba_free_space  free
where
  total.tablespace_name = free.tablespace_name(+)
  and total.file_id=free.file_id(+)
group by
  total.tablespace_name,
  total.bytes
/

set linesize 1000

set pagesize 1000

OPatch apply & rollback

1.Stop all services...on both APPS & DB node.......

2.export PATH=$ORACLE_HOME/OPatch

3.Above is mandatory...Before applying patch check PATH should points to opatch directory location.....

4.opatch lsinventory -invPtrLoc /u01/app/oracle/db/tech_st/11.1.0/oraInst.loc |grep 8940108(patch number)

Above command shows whether the patch is applied or not.

5.unzip the patch...

6.shutdown the database...

7.goto patch directory location.....

8.fire the below command to apply the patch....(go to patch directory)

opatch apply -invPtrLoc /u01/app/oracle/db/tech_st/11.1.0/oraInst.loc.

Once the patch is applied successfully on database...

Startup the database....

Start the services

Patch is already … Download the new version of patch rollback patch and apply new version patch:

Oracle_home/inventory/oneoffs/<patch_no>/  opatch rollback  –id <pch_no> -invPtrLoc /u01/app/oracle/db/tech_st/11.1.0/oraInst.loc

---------------------------------------------------------------------------> ManjunathaReddy

To create inventory

./runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u01/app/oracle/db/tech_st/11.1.0" ORACLE_HOME_NAME="oracleHome" CLUSTER_NODES="{}"

Foms compilation 11i & R12...

11i:

$APPL_tOP/apps/ora/8.0.6/bin/f60gen module=/appltop/apps/au/11.5.0/forms/US/ INVSDOIO.fmb userid=APPS/apps output_file=/appltop/apps/fnd/11.5.0/forms/US/ INVSDOIO.fmx  module_type=form batch=yes compile_all=special

cd $AU_TOP/forms/US
ls -ltr INVSDOIO.fmb
FORMS60_PATH=$AU_TOP/resource:$AU_TOP/forms/US
export FORMS60_PATH
f60gen Module=INVSDOIO.fmb Userid=apps/apps Output_file=INVSDOIO.fmx Debug=Yes Module_type=FORM

--------------

Module type =pll

f60gen module=PNSULOCN.pll module_type=library compile_all=Yes userid=apps/apps@DRTSTB | tee ~/PNSULOCN.log

Module type = form

f60gen module=INVSDOIO.fmb module_type=form compile_all=Yes userid=apps/apps@DRTSTB | tee ~/INVSDOIO.log

----------------------------------------------------------

R12:
frmcmp_batch WMSLABEL.fmb userid=APPS/<apps-password> output_file=/u01/app/oracle/apps/apps_st/appl/wms/12.0.0/forms/US/WMSLABEL.fmx module_type=form compile_all=special

Ho to Create adpatch defaultsfile :

How to apply patches in future ?

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt

Now any future patches you apply non interactive using defaults file like

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt logfile=6241631.log patchtop=/nfs/patches/6241631 driver=u6241631.drv interactive=no

adpatch Non-Interactive mode
               [defaultsfile=<$APPL_TOP/admin/SID/defaultsfile>]
               [logfile=<logfile>][interactive=y|n][workers=<workers>]
               [patchtop=<patchtop>][driver=<driver_file>][restart=y|n]
               [abandon=y|n][wait_on_failed_job=y|n][stdin=y|n]
               [maxworkers=<maxworkers>]

Note:

help=y      - Prints help information about adpatch options.
* preinstall  - To run adpatch in Pre-Install Mode.   Default - No.

* apply       - To run adpatch in Test Mode.          Default - Yes.

* uploadph    - To upload the patch history from the filesystem to the
                database and exit. This feature can be used when the system
                is not in maintenance mode.    Default - No.

* interactive - Invokes adpatch in Non-Interactive mode when
               "interactive=no" is specified.       Default - Yes.

Following are usually specified in Non-Interactive Mode, however not all

  are compulsory. Some are also valid for interactive mode.

    * defaultsfile         - Defaults file name. Default - none. No default file read or written.

    * stdin            - If "stdin=y" then password will be prompted.   Default - No.

    * logfile          - Adpatch log file name. Default - none. Adpatch prompts for this value.

    * workers       - Specifies the number of workers to run. Default - none. Adpatch prompts for this value.

    * maxworkers           - Specifies the maximum number of workers allowed to run.

                             Default - computed dynamically based on database parameters.

    * patchtop             - Top-level directory for the current patch.

                             Default - none. Adpatch prompts for this value.

    * driver               - Name of the patch driver file.

                             Default - none. Adpatch prompts for this value.

    * backup           - Absolute Path for backup. Default - patchtop.

    * restart            To restart an existing session.   Default - none. Adpatch prompts for this value.

    * abandon         To abandon the previous failed session.   Default - No.

    * wait_on_failed_job   - To prevent adpatch from exiting, when all

                             workers have failed in Non-Interactive Mode.  Default - No.

adpatch help=y

options = Generic options passed to adpatch.

checkfile,compiledb,compilejsp,copyportion,databaseportion,generateportion,integrity,parallel,prereq,validate,hotpatch & maintenancemode