Check sqlbind variable values

Very simple example to check sqlbind variable (will update as I go on - work in progress).

SQL > variable TABLSPC varchar2(50);

SQL> exec :TABLSPC := 'AP_MAIN';

PL/SQL procedure successfully completed.
SYS@abp1 SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files where tablespace_name = :TABLSPC

TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
AP_MAIN +DATA/AP/DATAFILE/ap_main.01.dbf

SQL>select sql_text, sql_id, LAST_ACTIVE_TIME from v$sqlarea where sql_text like '%TABLSPC%'

SQL_TEXT SQL_ID LAST_ACTI
------------------------------------------------------------------------------------------------------------------------ ------------- ---------
select FILE_NAME, TABLESPACE_NAME from dba_data_files where tablespace_name = :TABLSPC 6wg3unwbc0qk5 08-JUL-19

From here : http://www.oracle-wiki.net/startsqlshowbindvariable

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

COLUMN sql_text FORMAT A120

COLUMN sql_id FORMAT A13

COLUMN bind_name FORMAT A10

COLUMN bind_value FORMAT A26

SELECT sql_id, t.sql_text sql_text, b.name bind_name, b.value_string bind_value

FROM v$sql t JOIN v$sql_bind_capture b using (sql_id) WHERE

b.value_string is not null AND sql_id='&sqlid'

Enter value for sqlid: 6wg3unwbc0qk5
old 13: sql_id='&sqlid'
new 13: sql_id='6wg3unwbc0qk5'
Press Return to Continue
SQL_ID SQL_TEXT BIND_NAME BIND_VALUE
------------- ------------------------------------------------------------------------------------------------------------------------ ---------- --------------------------
6wg3unwbc0qk5 select FILE_NAME, TABLESPACE_NAME from dba_data_files where tablespace_name = :TABLSPC :TABLSPC AP_MAIN

Oracle DBA silent response file

Built a response file for a two node rac database; amend as appropriate.

This is mainly for my reference. 

dbca -silent \

-responseFile NO_VALUE \

-createDatabase \

-templateName General_Purpose.dbc \

-sysPassword oracle \

-systemPassword oracle \

-asmSysPassword oracle \

-createAsContainerDatabase true \

-gdbname emp_test.scc.local \

-sid emptest \

-initParams local_listener=register_local \

-storageType ASM \

-diskGroupName DATA \

-recoveryGroupName FRA \

-redoLogFileSize 500 \

-characterSet AL32UTF8 \

-nationalCharacterSet AL16UTF16 \

-nodeinfo db01,db02

Oracle Database Recommendations 19c

Email from Oracle Support - no doubt one every year from now on, ad infinitum (should not complain really keeps us DBAs in a job).

Dear Oracle Database Customer,

Oracle Database 19c is now available on all major platforms. Oracle will be supporting Oracle Database 19c (Long Term Support Release) with Oracle Premier Support through March 2023 and with Extended Support through March 2026.

In order to stay current, minimize future upgrades and take advantage of new features, we encourage you to upgrade to Oracle Database 19c without delay.

We recommend the following upgrade options:

If you are running Oracle Database 11.2.0.4 or 12.1.0.2, you need to maintain an Unlimited License Agreement that includes Extended Support, or pay for Extended Support fees and upgrade to Oracle Database 19c before patching ends on these releases.

--Oracle Database 11.2.0.4 (Terminal patch set release for 11.2, currently in Extended Support through December 2020)

--Oracle Database 12.1.0.2 (Terminal patch set release for 12.1, currently in Extended Support through July 2021 with a fee waiver available through July 2019)

If you are running Oracle Database 12.2.0.1 or 18c, you will need to upgrade to Oracle Database 19c before the error correction end dates are reached.

--Oracle Database 12.2.0.1 (Annual release with error correction ending July 2020)

--Oracle Database 18c (Annual release with error correction ending June 2021)

The purpose of these recommendations is to help you stay within the guidelines of Lifetime Support and Error Correction Policies (allowing you to avoid costly support exceptions), and to encourage you to take advantage of the new features that are available in the latest product releases.

For questions related to certification combinations and Lifetime Support agreements with other Oracle products like Oracle E-Business Suite, Oracle JD Edwards, Oracle PeopleSoft, and Oracle Siebel, please contact your Oracle Account Representative.

For questions about the Oracle Database 19c upgrade process, refer to the following MOS Community topic:

--Planning on or Currently Upgrading to 19c?

For additional information, refer to:

--Release Schedule of Current Database Releases (Doc ID 742060.1).

Regards,

Oracle Support