DBA Blogs

Procedure calling

Tom Kyte - Thu, 2017-01-26 08:46
Hello Tom, I have to call a procedure inside a procedure more than 35k times. Can I do this using a normal loop or bulk collect-forall will be better approach? Thanks in advance.
Categories: DBA Blogs

Kill session

Tom Kyte - Thu, 2017-01-26 08:46
I executed a stored procedure in Oracle 11g using utl_stmp that had a loop that was not properly ended. Needless to say, I received thousands of emails. I requested my DBA to kill the session so that I can stop receiving emails. After the session was...
Categories: DBA Blogs

Schema Creator Name

Tom Kyte - Thu, 2017-01-26 08:46
Hello Tom, Is there a way to know the schema/User creator in Oracle?
Categories: DBA Blogs

the best way to count distinct tuples

Tom Kyte - Thu, 2017-01-26 08:46
I was always afraid to ask, but probably you can tell me: what is the best way to count distinct tuples ? Why does just <code> select count(distinct a, b) from ( select 1 a, 1 b, 1 c from dual union all select 1 a, 1 b, 2 c from dual union...
Categories: DBA Blogs

Index fragmentation - REBUILD Vs SHRINK SPACE

Tom Kyte - Thu, 2017-01-26 08:46
Hi Chris/Connor, We have gather list of tables/Index along with Allocated space, Used space and %fragmentation. Could you please help to how do analysis on Indexes e.g. based on allocated/used space which index we may need to REBUILD or SHRINK. ...
Categories: DBA Blogs

Oracle Streams - Hold/Intercept changes

Tom Kyte - Thu, 2017-01-26 08:46
Hi, We have two databases configured with One-Way Oracle Streams replication. <b>We need to intercept the data changes before they are applied, and apply these changes somewhere in the future using our business logic (keeping the original order...
Categories: DBA Blogs

Query to display Master - Detail Output in Separate Lines

Tom Kyte - Thu, 2017-01-26 08:46
Hi, Good Day ! In my current requirement, I have to display the master details relation in separate rows. The Header should show only the master record while detail should show the detail record only. I written a query using join & union all a...
Categories: DBA Blogs

Partner Webcast – Real Time Business Integration Insights with Oracle SOA Suite Service

Are your data still stale? Haven’t you had the need to view of the business in real time so you can still react to the issue, before it’s too late? Today’s business owners still lack visibility and...

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

TNS Protocol Error

Tom Kyte - Wed, 2017-01-25 14:26
Hi, I tried to connect to a remote DB through SQL * PLUS,but i got TNS Protolcol Error. Below is the command i tried Username/Password@InstanceName So please tell me what the Exact Issue.
Categories: DBA Blogs

Oracle Streams from multiple source database to target database

Tom Kyte - Wed, 2017-01-25 14:26
Hi Tom, This is my first question, Thanks for your help in advance. We have two databases for OLTP having same schema with same tables (structure wise same but with different data in them). we need to replicate these database schema tables (fr...
Categories: DBA Blogs

12.2 Index Advanced Compression “High” Part III (I Can’t Give Everything Away)

Richard Foote - Tue, 2017-01-24 23:42
If you like the look of the new Index Advanced Compression setting of “High” available with Oracle Database 12c Release 2 as I’ve discussed previously in Part I and Part II, well there’s a way to make this the default index compression method in your 12.2 database. Let’s begin by creating a table and explicitly […]
Categories: DBA Blogs

SQLPLUS takes 10 seconds to connect locally as SYSDBA

Tom Kyte - Tue, 2017-01-24 20:06
Testing Oracle12c Standard Edition under Windows Server 2012R2 with Role Active Directory. After the ORADIM -NEW -startmode MANUAL, I try a SQLPLUS SYS/pwd AS SYSDBA and it takes 10 seconds to connect to the idle instance. I try with listener run...
Categories: DBA Blogs

Backup via OS snapshot

Tom Kyte - Tue, 2017-01-24 20:06
I have question as i am using 4tb database how can i switch from rman backup to snap/san backup. Please advise
Categories: DBA Blogs

oracle data guard hardware/software requirements

Tom Kyte - Tue, 2017-01-24 20:06
Can I have a primary database of a dataguard running on Sun Solaris and have the standby by running on Linux in a physical standby running on 11.2.0.4? and I intend to use rman
Categories: DBA Blogs

Impossible passwords no longer allowed

Tom Kyte - Tue, 2017-01-24 20:06
Prior to Oracle 12 we used to be able to set impossible passwords by doing something like this ALTER USER audsys IDENTIFIED BY VALUES 'ThisIsNotTheDefaultPassword'; Since Oracle 12 when we do that, we get the exception ORA-02153: invalid VA...
Categories: DBA Blogs

GMT to New York Time

Tom Kyte - Tue, 2017-01-24 20:06
I want to convert a date field that is exists in GMT to New York time. This would be EST/EDT. But I need a query which converts the date to EST or EDT appropriately based on day light savings.
Categories: DBA Blogs

Delimited column to split into multiple rows

Tom Kyte - Tue, 2017-01-24 20:06
Please refer to the LiveSQl link for table creation and how the data is within the table. Is there a direct way to achieve this using SQL. I am aware of commands to split for each record so that Cartesian output is created but not sure if something l...
Categories: DBA Blogs

Query go down on knees when selecting a CLOB or a BLOB column data type

Tom Kyte - Tue, 2017-01-24 01:46
I have a table, When selecting all columns without "order by" clause, query is very quick : select * from LOG_STATS_JUNIT; When adding "order by" clause, query go down : select * from LOG_STATS_JUNIT order by date_exec; If using DBMS_LOB.SUB...
Categories: DBA Blogs

Sorting Of Varchar column in a table

Tom Kyte - Tue, 2017-01-24 01:46
I want order column of varchar2 datatype the following way Abc ABc ABC Bzc BZc BZC can u suggest me how to do this??
Categories: DBA Blogs

snapshot control file

Tom Kyte - Tue, 2017-01-24 01:46
Hi Tom, I knw he snapshot control file is a copy of a database control file and RMAN creates the snapshot control file so that it has a consistent version of a control file to use when either re synchronizing the recovery catalog or backing up the...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs