To find particualr tablespace usage

set pages 50
column file_name format a48
column tablespace_name format a12

set verify off
accept tbs prompt 'What is the Tablespace Name: '


SELECT  dts.tablespace_name,
NVL(ddf.bytes / 1024 / 1024, 0) avail,
NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,
NVL(dfs.bytes / 1024 / 1024, 0) free,
TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00')
"Used %" ,
TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes
* 100, 0), '990.00') free_pct,
decode(sign(
(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 /
1024, 0)),-1,0,(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 / 1024, 0))  "Required MB"
FROM
sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) dfs
WHERE
dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+)
and dts.tablespace_name = '&tbs';

select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name = '&tbs';
undefine tbs

How to Find Hidden Parameters in Oracle Database?

select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '\_%' escape '\'
order by
name;

How to find invalid objects and syntax to compile?

set pages 1000
set lines 1000
select 'alter ' || OBJECT_TYPE || ' '  ||OWNER|| '.' ||object_name||   ' COMPILE;'   FROM dba_objects where OBJECT_TYPE != 'PACKAGE BODY' and status='INVALID' order by OBJECT_TYPE;

select 'alter PACKAGE '  ||OWNER|| '.' ||object_name||   ' COMPILE BODY;'   FROM dba_objects where OBJECT_TYPE = 'PACKAGE BODY' and status='INVALID';

How to Place the some data files in +DATA2_ARCH location?

run {
ALLOCATE auxiliary CHANNEL ch00 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch01 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch02 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch03 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch04 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch05 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch06 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch07 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch08 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch09 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch10 TYPE DISK;
set until time '05-JUN-2015 19:30:00';
SET NEWNAME FOR DATAFILE 397 TO '+DATA2_ARCH';
SET NEWNAME FOR DATAFILE 30 TO '+DATA2_ARCH';
SET NEWNAME FOR DATAFILE 28 TO '+DATA2_ARCH';
SET NEWNAME FOR DATAFILE 27 TO '+DATA2_ARCH';
SET NEWNAME FOR DATAFILE 24 TO '+DATA2_ARCH';
duplicate target database to test
NOFILENAMECHECK;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
RELEASE CHANNEL ch04;
RELEASE CHANNEL ch05;
RELEASE CHANNEL ch06;
RELEASE CHANNEL ch07;
RELEASE CHANNEL ch08;
RELEASE CHANNEL ch09;
RELEASE CHANNEL ch10;
}
Exit

How to take Profile Values backup?

set pages 2000
col PROFILE_OPTION_VALUE for a50;
col PROFILE_OPTION_NAME for a50;
set linesize 200;
spool fnd_profiles_values_after_patch.log
select distinct a.PROFILE_OPTION_NAME,b.PROFILE_OPTION_VALUE from apps.fnd_profile_option_values b,apps.fnd_profile_options a where a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID;
spool off

How to generate from text file to excel Format?

set pagesize 10000
set feedback off
set echo off
set colsep ','
set linesize 10000
spool filename1.csv
query here.....(give SQL query:ex:select * from ........)
spool off

Dupilcate Command by connecting catalog database and target db

export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

export TNS_ADMIN=/var/opt/oracle  -->

rman log /u01/oracle/recovery.log

connect target sys/manager123@prod

connect catalog rman/rman@ctdblist

connect auxiliary /


run {
ALLOCATE auxiliary CHANNEL ch00 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch01 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch02 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch03 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch04 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch05 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch06 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch07 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch08 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch09 TYPE DISK;
ALLOCATE auxiliary CHANNEL ch10 TYPE DISK;
set until time '02-JUL-2014 19:00:00';----- (Mention PIT give ten minutes additional whatever you have last backup-ed time)
duplicate target database to test
NOFILENAMECHECK;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
RELEASE CHANNEL ch04;
RELEASE CHANNEL ch05;
RELEASE CHANNEL ch06;
RELEASE CHANNEL ch07;
RELEASE CHANNEL ch08;
RELEASE CHANNEL ch09;
RELEASE CHANNEL ch10;
}

How To Check Database Size ?

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB" from dual

How to find locks in RAC database @ instance level?

SQL> select 'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking
from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

                               OR

SQL> select inst_id, sid, blocking_instance, blocking_session from gv$session
where blocking_instance is not null and blocking_session is not null order by;

How to change shell for normal user in OEL?

How to submit Concuurent Request from backend?

CONCSUB apps/xxxxxx SYSADMIN 'System Administrator' SYSADMIN WAIT=N CONCURRENT FND FNDSCURS

How to find current Workflow Mailer Service log file?

set linesize 155;
set pagesize 200;
set verify off;
column MANAGER format a15;
column MEANING format a15;
SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup
WHERE concurrent_queue_name in ('WFMLRSVC')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND flkup.lookup_code=fcp.process_status_code
AND lookup_type ='CP_PROCESS_STATUS_CODE'
AND meaning='Active';