DBA Blogs

Question on ORA-12899: value too large

Tom Kyte - Tue, 2017-02-07 13:46
Hi Tom, We have a migration project which from Sybase SQL Anywhere to Oracle. And there is an issue we still have no perfect solution. In Sybase, When insert/update to a target column, the source string will be auto truncate if the length more t...
Categories: DBA Blogs

right way to grant permissions to developers

Tom Kyte - Tue, 2017-02-07 13:46
hello, i am junior dba with quite a little knoweledge about oracle. lately i wanted to make users for plsql developers who work on production database with the same user-bill(which has DBA role). i wanted to make them their own user for security p...
Categories: DBA Blogs

Error(24,1): PLS-00103: Encountered the symbol "BEGIN"

Tom Kyte - Tue, 2017-02-07 13:46
I wonder where it shows PLS-00103: Encountered the symbol "BEGIN" when i compile the procedure. please help <code> create or replace PROCEDURE GETCTRLEDPTDETAILSBYIPADDRR ( v_ipaddress IN NVARCHAR2 DEFAULT NULL , v_organizationId IN NUM...
Categories: DBA Blogs

Passing multiple value in IN clause in SQL query

Tom Kyte - Tue, 2017-02-07 13:46
Hi I have SQL query which takes around 50000 as input value in IN clause. What is best approach to passing value into IN clause which are more than 1000 in count.
Categories: DBA Blogs

Dynamic SQL

Tom Kyte - Tue, 2017-02-07 13:46
Hi Tom, I have the following dynamic sql statement, I want to return the rowid of the inserted record into a variable but I get a command not properly ended error. What is the problem? stmt:= 'INSERT INTO ' || destination_table_name || ' ( ' |...
Categories: DBA Blogs

Parting With ocm.rsp Isn’t Really Sweet Sorrow…

Pythian Group - Tue, 2017-02-07 13:00

Now that we’re well into 2017, let’s sit back and ask the question: “What was the best news of 2016 in the Oracle ecosystem?”

I’ll give you a clue.  The best news was something that sneakily appeared between April and October 2016 in your patch maintenance logs :

[root@myclusterdb02 OPatch]# ./opatchauto apply /patches/OCT2016_bundle_patch/24436624/Database/12.1.0.2.0/12.1.0.2.161018DBBP/24448103 -oh /u01/app/oracle/product/12.1.0.2/dbhome_1 -ocmrf /u01/app/oracle/product/12.1.0.2/dbhome_1/OPatch/ocm/bin/ocm.rsp
System initialization log file is /u01/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatchautodb/systemconfig2016-12-11_05-23-36PM.log.
Session log file is /u01/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatchauto/opatchauto2016-12-11_05-24-09PM.log
WARNING: the option -ocmrf is deprecated and no longer needed. OPatch no longer checks for OCM configuration. It will be removed in a future release.

The interesting part is in that 4th line :

WARNING: the option -ocmrf is deprecated and no longer needed. OPatch no longer checks for OCM configuration. It will be removed in a future release.

This is awesome! We no longer need this annoying ocm.rsp file !

When I discovered this back in November 2016, I searched for an official feature announcement but found nothing (it looks like an opatch 13c new feature backported in 11g and 12c). This ocm.rsp file was still needed when I applied the April 2016 PSU, but from which opatch version was it implemented?

First, I looked at the 2016 dev comments in opatch 11.2.0.3.15 and 12.2.0.1.7 (which were the last version when I was applying this Exadata October 2016 PSU) and I sadly discovered that no specific comments were talking about this new feature (even if it would obviously deserve some):

#  opatch  04/08/16  Update CAS_VERSION to 12.2.0.1.3
#  opatch  04/11/16  Consolidate all 4 opatch scripts
#  opatch  06/07/16  Calling auto_patch.pl using the perl from Oracle Home Location
#  opatch  06/30/16  bug 23721730 - default memory was too high for 32 bit so removed and disabled opatch_env.sh script call

 

After a quick look at the code, I found the amazing piece of code that would ease our lives in the future (the function is named ocmOptionDetect()):

# Option 'ocmrf' is deprecated in OPatch 13.3 - Detect and give warning message
if [ "$OCMRF_OPTION" = "1" ] && [ "$VERSION_13_X" = "1" ]; then
echo "WARNING: the option \"-ocmrf\" is deprecated and no longer needed.  OPatch no longer checks for OCM configuration. It will be removed in a future release."

 

I then performed a few greps in different opatch versions that I had and I found that this feature has been implemented in:

  • 11.2.0.3.13 or 11.2.0.3.14 for 11g (I was unable to find any of these versions so I couldn’t check)
  • Between versions 12.1.0.1.10 and 12.2.0.1.7 for 12c (again, I was unable to find any of these versions so I couldn’t check)

If you come across any of the opatch versions specified above, please “grep -i ocmrf opatch” it and let me know so I can find when this feature has been implemented.

You can now upgrade all your opatches to become ocm.rsp-free and this (for me) was definitely the best new of 2016!

Categories: DBA Blogs

Links for 2017-02-06 [del.icio.us]

Categories: DBA Blogs

Query for generating resultset with continuos dates for existing data

Tom Kyte - Mon, 2017-02-06 19:26
Dear Tom, I have a table which stores all the account balances with dates. A row is inserted into this table only for those days where the account has transactions. The table looks as below. create table account_balance (account varchar2(16), b...
Categories: DBA Blogs

Oracle AQ and wait delay issue

Tom Kyte - Mon, 2017-02-06 19:26
I wonder if you can shed any light on the following problem we're seeing when we dequeue payloads from AQ. What seems to be happening is whenever we set a delay then all delayed payloads seem to be taking ~3 seconds longer to dequeue than the wait we...
Categories: DBA Blogs

how to use ANSI outer join in subquery

Tom Kyte - Mon, 2017-02-06 19:26
how to write this query in ANSI STANDARD (I mean without using + symbol) select a.ename, a.empno from dept a where exists(select null from emp b where a.deptno=b.deptno(+))
Categories: DBA Blogs

Why i am getting invalid cursor error/

Tom Kyte - Mon, 2017-02-06 19:26
<code>DECLARE CURSOR C_Emp IS SELECT Ename,Deptno FROM Emp WHERE Ename = 'RINTU'; V_Ename Emp.Ename%TYPE; V_Deptno Emp.Deptno%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('LOOP STAR...
Categories: DBA Blogs

How to identify sql query running against a database

Tom Kyte - Mon, 2017-02-06 19:26
Hi Toms Team, First of all thanks to you for sharing information using "Ask Tom" platform. My question is When we run any sql against a database (I mean simple select sql eg. select 1 from dual) we can see this information by querying against ...
Categories: DBA Blogs

Execute Shell Script from Oracle Database Program.

Tom Kyte - Mon, 2017-02-06 19:26
Hi Experts, I have done the required setup to execute unix OS commands using Java class as mentioned in the link(https://oracle-base.com/articles/8i/shell-commands-from-plsql) from database programs , I was able to run echo command from the Pl/SQL...
Categories: DBA Blogs

DBA_HIGH_WATER_MARK_STATISTICS Dictinoary view has cpu_count. is this value dynamically read while DB startup.

Tom Kyte - Mon, 2017-02-06 19:26
DBA_HIGH_WATER_MARK_STATISTICS Dictinoary view has cpu_count. is this value dynamically read while DB startup. if it's static can it be possible to change. we are moving OS and database disk' from 8 core to 4 core system to fix license issue. so want...
Categories: DBA Blogs

How find deleted row without using flashback

Tom Kyte - Mon, 2017-02-06 19:26
After commit how to find a deleted row from emp table in oracle?
Categories: DBA Blogs

Installing Pervasive and Oracle on the same physical server

Tom Kyte - Mon, 2017-02-06 19:26
My company is moving from Pervasive to Oracle and we are about to install Oracle 12c. We would like to know if there are any issues that we may encounter if we installed Oracle DB on the same physical server the Pervasive DB is on.
Categories: DBA Blogs

OS bootable disk with server

Tom Kyte - Mon, 2017-02-06 01:06
Hello, I am trying to make a bootable disk to run Linux on my laptop. I am learning how to use oracle database. I have downloaded the iOS image and when I start my laptop I boot off of the disk and then I get a message that a serve is not found. I do...
Categories: DBA Blogs

move DB from 8 core to 4 core

Tom Kyte - Mon, 2017-02-06 01:06
HI we need to migrate DB from 8 core system to 4 core system. one of my DB engineer is saying On the startup Oracle reads the processor and ?stamp? on the database. After that, next time when you start the database Oracle only override the stam...
Categories: DBA Blogs

update stats required after splitting adding partitions/subpartitions

Tom Kyte - Mon, 2017-02-06 01:06
I have to add dynamic partitioning/subpartitioning fucntionality to my warehouse application where i may need to split default subpartition or add new partition. These partitions may or may not contain data. Indexes used are non partitioned global...
Categories: DBA Blogs

Hotsos Symposium 2017: Feb 27 – Mar 2 Dallas, Texas (I’m Afraid of Americans)

Richard Foote - Mon, 2017-02-06 00:42
In just 3 weeks time, I’ll again have the pleasure of presenting at the awesome Hotsos Symposium in Dallas, Texas. It’s simply the best conference with regard to Oracle performance tuning anywhere. This year, I’ll be presenting on a couple of topics: New Index Features Introduced in Oracle Database 12c Release 2 Oracle Database 12c […]
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs