Oracle query to find value of bind variables used by SQL

For less than 30 min running query:

select s.sid,

s.username,

sq.sql_text,

s.sql_hash_value,

s.sql_id,

s.sql_child_number,

spc.name,

spc.value_string,

last_captured

from v$sql_bind_capture spc, v$session s,v$sql sq

where s.sql_hash_value = spc.hash_value

and s.sql_address = spc.address

and sq.sql_id=s.sql_id

and spc.was_captured='YES'

and s.type<>'BACKGROUND'

and s.status='ACTIVE'

For historical query:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM DBA_HIST_SQLBIND WHERE SQL_ID='' and SNAP_ID='';

Oracle ORA-14048: a partition maintenance operation may not be combined with other operations

Cause : When I am trying to make an index as UNUSABLE from Toad I got this error.
Solution : Try from sqlplus.

SQL>> alter index AHBDDS.SYS_C0015740 unusable;

Index altered.

SQL>>

How To Get SQL Connections BY IP Address and loginname

-- Get SQL connections by IP address

SELECT ec.client_net_address, es.[program_name],

es.[host_name], es.login_name,

COUNT(ec.session_id) AS [connection count]

FROM sys.dm_exec_sessions AS es 

INNER JOIN sys.dm_exec_connections AS ec 

ON es.session_id = ec.session_id  

GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name 

ORDER BY ec.client_net_address, es.[program_name];

-- Get SQL connections by login_name

SELECT login_name, COUNT(session_id) AS [session_count]

FROM  sys.dm_exec_sessions

GROUP BY login_name

ORDER BY login_name;

Configure mailx in AIX

1. starting sendmail
lssrc -s sendmail                                        
startsrc -s sendmail -a "-bd -q30m"                      
vi /etc/rc.tcpip -> uncomment: start /usr/lib/sendmail  



In /etc/hosts:
10.10.139.12   smtp.alhilalbank.ae

vi /etc/mail/sendmail.cf
DSsmtp.alhilalbank.ae    -- Our mail server smtp.alhilalbank.ae

refresh sendmail:
refresh -s sendmail  

telnet smtp.alhilalbank.ae 25

ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option

alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 26280000
Session ID: 753 Serial number: 5

After RMAN restore to another server. Please check the source and target database version you have.

In my case, 

source:  11.2.0.3.0

target: 11.2.0.1.0




sqlplus / as sysdba

sql>startup upgrade

sql>@$ORACLE_HOME/rdbms/admin/catupgrd.sql

sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql

sql>shut immediate

sql>startup

Cancelling Concurrent Request script

Concurrent Request Status Codes and Phase Codes

Use below query to cancel all scheduled concurrent programs.
===================================================
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N';

COMMIT;

=====================================================
TO cancel all running concurrent programs.

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('R','I');
Commit;


Concurrent Request Phase Codes:-

SELECT LOOKUP_CODE, MEANING
  FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_PHASE_CODE' AND LANGUAGE = 'US'
       AND ENABLED_FLAG = 'Y';

To Cancel Specific request:
++++++++++++++++++++++++++++++++
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=;

Oracle RAC: ON and OFF

Oracle's RAC: ON and OFF

In some cases, you may want to disable the RAC options for testing purposes -- perhaps to run a benchmark or convert the RAC binaries to single instance binaries. In such a case, you can use the following procedure to convert the RAC installation to non-RAC. Disabling and enabling RAC options are available only for UNIX platforms. Windows installations do not support re-linking binaries with RAC ON and OFF.

Use the following steps to disable RAC (known as RAC OFF):

1. Log in as the Oracle software owner (which is typically the UNIX account oracle) in all nodes.
2. Shut down all the instances from all the nodes using a NORMAL or IMMEDIATE option.
3. Change the working directory to $ORACLE_HOME/rdbms/lib: cd $ORACLE_HOME/rdbms/lib
4. Run the following make command to relink the Oracle binaries without the RAC option: make -f ins_rdbms.mk rac_off

This normally runs for few minutes and should not pose any errors.
5. . Now relink the Oracle binaries: make -f ins_rdbms.mk ioracle 

Now the Oracle binaries are relinked with the RAC OFF option. You may have to edit the init.ora or SPFILE parameters accordingly. If errors occur in step 4, you may need to contact Oracle Support and log a service request with the trace and log files.

Use the following steps to enable RAC (known as RAC ON):

1. Log in as the Oracle software owner (typically the UNIX account oracle) in all nodes.
2. Shut down all the instances from all the nodes using a NORMAL or IMMEDIATE option.
3. Change the working directory to $ORACLE_HOME/rdbms/lib:
4. cd $ORACLE_HOME/rdbms/lib Run the following make command to relink the Oracle binaries without the RAC option:
make -f ins_rdbms.mk rac_on

This normally runs for a few minutes and should not pose any errors.
5. Now relink the Oracle binaries:
make -f ins_rdbms.mk ioracle 

Now the Oracle binaries are relinked with the RAC ON option. You may need to edit the init.ora or SPFILE parameters accordingly. If any errors occur in step 4, you may need to contact Oracle Support and log a service request with the trace and log files.