Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 12 hours 56 min ago

Want to retrive numbers in words

Sat, 2017-09-16 05:26
I want output as: numbers string 101 One zero one 102 One zero two 851 eight five one 9856 nine eight five six 356 three five six 748 seven four eight 254 two five four ...
Categories: DBA Blogs

How to split rows into balanced sets based on a running total limited to 2000

Fri, 2017-09-15 11:06
Hi, my question fits in to your "Balanced sets in SQL" collection of questions. I have a table as follows (in reality 26 million rows): <code> CREATE TABLE T ( "PI" VARCHAR2(120), "S" NUMBER, "L" NUMBER ); Insert into T (PI,S,L...
Categories: DBA Blogs

About connect,resource and DBA

Fri, 2017-09-15 11:06
Hi Tom, I read your book and a article and read this quote where you have quoted that "connect,resource and DBA should not be used in a system for security reasons". Could you please elaborate on this.As in our project to perform dba role ...
Categories: DBA Blogs

Performance differ on Temporary tables

Fri, 2017-09-15 11:06
Please observe my queries explain plan and let me know what is the root cause taking more time on second execution. --GT Table creation <code>create global temporary table gt_table1 (column1 varchar2(4000), column2 varchar2(4000), ........... ...
Categories: DBA Blogs

Using Standby of CDB for Reporting purpose

Fri, 2017-09-15 11:06
Hi Tom I have active data guard setup for my CDB . It means i have physical standby CDB (with multiple pdbs) . I know replication and database_Role are at CDB level and not pdb level . I have a PDB from different application . Initially when we s...
Categories: DBA Blogs

Parse cpu to parse elapsed % very low

Fri, 2017-09-15 11:06
Greetings, As in last question, i am getting these stats from awr. I tried to dig it up more for wait class concurrecy wait class. <code>Event Waits Time(s) Avg wait(ms) % DB time Wait Class --------------...
Categories: DBA Blogs

Database Service Configuration Requirements

Fri, 2017-09-15 11:06
Hi TOM, Was going through the section 5.6.1.2 in http://docs.oracle.com/database/121/DGBKR/sofo.htm#DGBKR3425. It says "Services that are to be active while the database is in the physical standby role must also be created and started on the c...
Categories: DBA Blogs

Pro*C: How to set USERID precompiler option for proxy connection

Fri, 2017-09-15 11:06
Hi, Is there a way to set the Pro*C USERID precompiler option for a proxy connection? For example, I have an OS authenticated user GEORGE who as been granted "CONNECT THROUGH" to user GEORGE_P and can perform a proxy connect to GEORGE_P in SQL*...
Categories: DBA Blogs

Cloning a PDB into a CDB having a active data guard setup

Fri, 2017-09-15 11:06
Hi Tom I have been trying to clone a pdb using database link into a cdb having dataguard setup using STANDBYS=ALL clause. But my replication stops . I tried changing the file_name_convert parameter at CDB level and still the new cloned pdb could not...
Categories: DBA Blogs

Ggsci interface

Thu, 2017-09-14 16:46
Hi Tom, I want to ask. Is there any ways to create ggsci interface? Like using netbeans or something. Thanks
Categories: DBA Blogs

Goldengate Integrated Replicat Parallel Execution

Thu, 2017-09-14 16:46
We're creating a bi-directional Goldengate replication environment between two Oracle databases... Goldengate 12.2 Oracle EE RDBMS 12.1 Both databases on a 2-node Exadata RAC We're using Integrated Extract and Replicat on both nodes. For p...
Categories: DBA Blogs

On addition of a single column, performance of query drastically impacted

Thu, 2017-09-14 16:46
Hi, On addition of a single column, performance of a query has drastically impacted (40 secs from 0.0002 secs). Oracle has changed earlier plan and picked plan that takes more time. Change : new column added to a query in select clause : dsi...
Categories: DBA Blogs

Oracle SQL background process

Thu, 2017-09-14 16:46
Hi Tom, I have bit knowledge (aware of defination) on PGA and SGA. But I would like to know, when the sql is triggered from client tool(toad). -> background process uses Shared pool memory OR pga memory for performing SYNTAX and SYMANTIC check...
Categories: DBA Blogs

Clustered Index and primary keys

Thu, 2017-09-14 16:46
I have question on clustered index I read from documents that whenever primary key is created, it creates clustered index along with it, and it sorts the rows in the table in the same order as the clustered index(on the actual disk), I didn?t unde...
Categories: DBA Blogs

Unjustified memory consumption of windows server equal to SGA_MAX_SIZE in 12.2.0.1 (with manual MM)

Thu, 2017-09-14 16:46
Hi, I just installed the latest Oracle 12.2.0.1 Enterprise Edition and I noticed something different with 12.1.0.2 regarding the memory consumption of the Windows Oracle RDBMS Kernel Executable. The database is installed on a Windows server 201...
Categories: DBA Blogs

Deterministic functions and Virtual Columns

Wed, 2017-09-13 22:26
It seems that ?DETERMINISTIC? means exactly what I think (or some of the non-oracle sources on the internet) think it does. I expected this script to fail either on the create function, create table or when running the select statements. I would ha...
Categories: DBA Blogs

Join of two tables and want first row of matching records in second table

Wed, 2017-09-13 22:26
I have two tables <code>create table g ( a int, d date); with this data in it: insert into g values ( 1, to_date('01/15/2004','mm/dd/yyyy')); insert into g values ( 2, to_date('01/15/2004','mm/dd/yyyy')); insert into g values ( 3, to_dat...
Categories: DBA Blogs

Selecting column dynamically

Wed, 2017-09-13 22:26
Is there a way to write a SQL which selects the number of columns based on the data available .. Say i am trying to post the number of lines inserted on a table per hour on a day .. So when i run the select between 03:00-04:00 hr it should give...
Categories: DBA Blogs

User defined function in select statement

Wed, 2017-09-13 22:26
Hi, I want fetch total_resale value based on quote_id and group by item_class but its not fetching proper result. Please find DDL, DML and function as below, DDL: <code>CREATE TABLE QUOTE_TEST ( ITEM_CLASS VARCHAR2(20 BYTE) , QUOTE_ID...
Categories: DBA Blogs

Import is taking 4 days to get DB impprted

Wed, 2017-09-13 22:26
Dear sir Database 11g- exported to dmp file sizing 20 GB Then i imported it with imp command as Full=Y and commit=Y, and db got imported successfully with in 5 hours. Next i imported the same dmp file in oracle 12c Initialy import went as...
Categories: DBA Blogs

Pages