DBA Blogs

Produce matrix result as table

Tom Kyte - Thu, 2017-02-09 20:46
Is it possible to create table like structure with sql what i want is display output in matrix format like if i have 3 rows and 6 columns then it should provide desire output for same. below are the scripts: create table t1 (id number, name varchar...
Categories: DBA Blogs

Shuffle values randomly in columns

Tom Kyte - Thu, 2017-02-09 20:46
Note - this is more of an academic question as I have a resolution, I am just keen to see whether my alternative approach is possible. I have a HR table with a list of names. For demo purposes I needed to shuffle these around to make them a bit mo...
Categories: DBA Blogs

WITH's work, but join on WITH's runs forever. Is there a way to force WITH tables to fully materialize perhaps?

Tom Kyte - Thu, 2017-02-09 20:46
with a as (), b as (), c as (), d as () select count(*) from a 138 select count(*) from c 138 select * from a left join c on a.val=c.val -this never finishes, runs forever Is there a way to add a hint to force it to work? My 2 ...
Categories: DBA Blogs

CLOB

Tom Kyte - Thu, 2017-02-09 20:46
I have a clob column in a table and the inserts to this table is taking hours. How can I speedup the insert. Please suggest asap.
Categories: DBA Blogs

Analytic - CONNECT BY LEVEL

Tom Kyte - Thu, 2017-02-09 20:46
<code>Hello folks - Here's my database & server info: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production ...
Categories: DBA Blogs

Dataguard vs Shareplex for DR

Tom Kyte - Thu, 2017-02-09 20:46
Hello Tom, I am looking for an advise on the DR setup. I am with a company where the design of the database and DR setup is done by the vendor and they are using shareplex instead of dataguard. I am having hard time convincing the management to c...
Categories: DBA Blogs

Partner Webcast – Announcing Oracle CASB Cloud Service, an API-based Cloud Access Security Broker

On September 18, 2016, Oracle announced that it signed an agreement to acquire Palerra, extending Oracle Identity Cloud Service with an innovative Cloud Access Security Broker (CASB). The transaction...

We share our skills to maximize your revenue!
Categories: DBA Blogs

How to change the DBID after restore the database on other server

Tom Kyte - Thu, 2017-02-09 02:26
Hi Tom, I am in the process to migrating the databases from existing server to the new server. I did restore my controlfile using RMAN catalog and after that I logged in locally ?rman target /? and restored the database. My question is how can...
Categories: DBA Blogs

How Sorts (Disk) in query works

Tom Kyte - Thu, 2017-02-09 02:26
I have two queries - 1. I see sorts(Disk) in the autotrace output for a query.What is actually sort(Disk) and how it works. Is the rowsets are brought in memory in chunks, sorted and written back to temp tablespace. After which the chunks are merg...
Categories: DBA Blogs

When to replace the hash-cluster for an in-memory table

Tom Kyte - Thu, 2017-02-09 02:26
At the moment we was a database with a dual timeline. Transaction timeling and validity timeline. All the valid records in the current transaction timeline are duplicated in hash cluster for performance. Now Oracle 12 is coming along with it's in...
Categories: DBA Blogs

Does Dataguard apply ddl on user tables

Tom Kyte - Thu, 2017-02-09 02:26
Hello, If I alter an user table (add a new column) in a dataguard environment from the active node, will that be reflected on the mounted DB of the passive node? Regards, Daniel
Categories: DBA Blogs

DBMS_PARALLEL_EXECUTE getting chunks to work in special order

Tom Kyte - Tue, 2017-02-07 13:46
Hi Tom, I'm using DBMS_PARALLEL_EXECUTE package to run in parallel my PL/SQL procedure's work. The chunks are generated by my own SQL on table which contains numeric field "priority" like this <code> v_sql := ' select rowid, rowid ...
Categories: DBA Blogs

Real time scenarios

Tom Kyte - Tue, 2017-02-07 13:46
friends, I am searching complex real time scenarios and solutions on PL/SQL and SQL, Please provide reference documentation links if any. this will b great helpful Thanks, vin
Categories: DBA Blogs

Flush buffer cache and shared pool

Tom Kyte - Tue, 2017-02-07 13:46
Hi Tom, We have an application performing many inserts and updates from many machines. At peak time, we may have the application running on 300 machines performing inserts and updates. Once in a while, we saw some active sessions blocking other sess...
Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs