DBA Blogs

Scan Listener for Single Instances

Tom Kyte - Fri, 2017-02-10 15:06
Hi, We have a bunch of <b><u>non</u></b>-RAC single instances (SE and EE). I'm thinking about still using a SCAN listener for these single instances as I could avoid any TNS configuration change when moving around databases between hosts. Is th...
Categories: DBA Blogs

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

Categories: DBA Blogs

Generate Trace Files

Tom Kyte - Thu, 2017-02-09 20:46
Hi, I am working on Oracle database version is 11.2.0.4.0. I have PL/SQL packages, which run from few minutes to couple of hours. My requirement is that for each package execution, it should generate a trace file and once it is generated, it sho...
Categories: DBA Blogs

How does Oracle know whether the index belongs to the primary key?

Tom Kyte - Thu, 2017-02-09 20:46
Consider the following two tables and their primary keys: <code> create table testuser.test1 ( col1 number not null, col2 number not null, col3 number not null ); alter table testuser.test1 add constraint test1_pk primary key ...
Categories: DBA Blogs

Query on time overlaps

Tom Kyte - Thu, 2017-02-09 20:46
I am struggling to merge continuous time ranges to one.Here is my record set. STAFF_NUMBER SHIFT_DATE TASK_START_TIME TASK_END_TIME 123 12/10/2016 12/10/2016 17:14 12/10/2016 20:10 123 12/10/2016 12/10/2016 20:08 12/10/2016 21:08 1...
Categories: DBA Blogs

Update query taking long time to execute

Tom Kyte - Thu, 2017-02-09 20:46
<code>dear tom, i am using a query that update a column of one table. But the query is very costly and taking huge time to complete. though i am using it parallel. The query is: UPDATE LOANACNTS l set l.LNACNT_RTMP_LAST_DATE=(SELECT /*+paralle...
Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs