Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 week 1 day ago

Event Based Job is not working

Fri, 2019-11-01 18:47
Hi, I am struggling several days with following issue. I am trying to implement event based job. At start all was working fine. But after several payload type modifications + several times recreated queue + recreated scheduled job ... schedu...
Categories: DBA Blogs

Database Design Question

Thu, 2019-10-31 09:47
Hello, Ask Tom Team. I have a table A with columns (client_id,invoice_number,invoice_type) with a composite primary key (client_id,invoice_number). At business level there are 5 invoice_types (70,71,72,73,74). The invoice_number always brings the ...
Categories: DBA Blogs

finding out the source of the data in a table

Thu, 2019-10-31 09:47
Hi Tom, I am working on a database application, and i need to know how a table is being populated in the database schema. I have tried querying xxx_dependencies and xxx_source but of no use. I believe that this table might be populated from an...
Categories: DBA Blogs

How to change the plan of a query in execution??

Thu, 2019-10-31 09:47
So I often face an issue when a query generates an execution plan assuming wrong carnality and since it assumes it as 1 it goes into a M3RG3 CART3SIAN join. Now if I gather the stats the query does not automatically picks up the new plan. Is there a ...
Categories: DBA Blogs

List of event codes for traces

Thu, 2019-10-31 09:47
Hello Masters, Can you give me a link on docs.oracle.com where are listed all the system event codes for the trace like 10046, 10053... and, most important, with there signification? I know the 10046, 10053 codes but I am sure there are many ...
Categories: DBA Blogs

Performance of a VIEW on multiple tables (historical plus current)

Thu, 2019-10-31 09:47
Good afternoon, I have a customer that manage millions of data with a lot of tables. The thing is that we have a big table from 2013 until now which we would like to divided in two, one for the last three months and one for the rest (which we call...
Categories: DBA Blogs

Restrict Application access to developers in same workspace

Thu, 2019-10-31 09:47
How to show a user only certain amount of applications in app builder(i.e user should not be able to see all applications in app builder)? In a Oracle Apex workspace, I need to create a new user(admin role) such that the new user can only see sele...
Categories: DBA Blogs

Find if event spanning several dates happened via SQL

Thu, 2019-10-31 09:47
Hi Tom, I have data like below <code> event_flag event_date 1 date1 1 date2 0 date3 1 date4 0 date5 0 date6 1 date7 1 date8 1 date9 ...
Categories: DBA Blogs

pragma autonomous_transaction within procedure before creating synonyms

Thu, 2019-10-31 09:47
Hi Tom, I have created a stored procedure with in oracle package which creates list of synonyms depending upon the change of dblink server name. I need to execute this procedure to create/replace synonym pointing to another dblink server. My quest...
Categories: DBA Blogs

Undo Tablespaces.

Thu, 2019-10-31 09:47
Hi Tom, Waiting to ask u this question. What is a Undo Tablespace in 9i. Is this similar to Rollback Segments. What are NonStandard Block sizes Why that non-Standarad. Why am i not able to create a RS on a Locally Managed Automatically Si...
Categories: DBA Blogs

How to count no of records in table without count?

Thu, 2019-10-31 09:47
2)How to count no of records in table without count? ---Actually,this question asked when i had attended an interview in Dell company.I don't know why they people are asked these type of questions,but i said an answer like in my own way. --->...
Categories: DBA Blogs

Oracle database software client 18c 32 bit win 7

Sat, 2019-10-26 15:46
I need to install Oracle database software client 18c on win 7 32 bit I visit Oracle website. They only offer instant client without setup file.i don't know configure on win 7.especially about odbc technology. I want to open odbc windows 32 bit an...
Categories: DBA Blogs

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [1384], [1270], [2885], [], [], [], [], [], [], []

Sat, 2019-10-26 15:46
------------------------------------- Oracle Database 11.2.0.1.0 Archive Log Mode RMAN Backup is performed daily, with backup control file and backup archive log file 1 time to disk. ------------------------------------ Hi Ask Tom t...
Categories: DBA Blogs

Last run date of a function on a menu

Sat, 2019-10-26 15:46
Is it possible extract the last run time of a function run on a menu from the Oracle database?
Categories: DBA Blogs

SQL to return 12 months of this year

Sat, 2019-10-26 15:46
select (to_char(add_months (sysdate,level-10),'Month')) as Month ,to_char(TRUNC(add_months(sysdate,level-10),'month'),'mm/dd/yyyy') as firstdayofthemonth ,to_char(last_day(add_months(sysdate,level-10)),'mm/dd/yyyy') as lastdayofmonth from dual ...
Categories: DBA Blogs

Oracle - validate date format (yyyy-mm-ddThh24:mi:ssZ) in XML against XSD

Sat, 2019-10-26 15:46
<b>Oracle version:</b> The result of this query select * from v$version; is: <code>Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2.0.4.0 Production" TNS for Li...
Categories: DBA Blogs

REGR_R2 returns a value of 1 when y column contains a constant value (e.g., all rows have value of 0.0038).

Sat, 2019-10-26 15:46
I was searching a large emissions database for pairs of columns with correlated values. My first effort identified a large number of sets with an R2 value of 1 based on blindly applying the REGR_R2 function to discrete pairs of numeric data columns....
Categories: DBA Blogs

Multiple SQL statements in a single Execute Immediate

Wed, 2019-10-23 15:46
What is the exact syntax to be able to execute multiple sql statements from within a single execute immediate statement.
Categories: DBA Blogs

Can I get a single table where each column consists of a query (each query returning three rows with ids)?

Wed, 2019-10-23 15:46
Hi, I have 27 different queries, all of which yield 3 rows max. Something like this: <code> SELECT CUSTOMER_NUMBER FROM customer WHERE name = 'SomeName' order by CUSTOMER_NUMBER fetch first 3 rows only; </code> and <code> SELECT CUSTOM...
Categories: DBA Blogs

Database Event Error Tracking

Wed, 2019-10-23 15:46
Hi Guys, I have a schema with multiple Schema objects like Procedures,Functions,Triggers and Packages. While testing via application if any DB error occurs we need to check the log to identify errors. Is there any way to create a single trig...
Categories: DBA Blogs

Pages