Tom Kyte

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

DISTINCT vs UNION

Thu, 2019-02-21 12:46
Hello Tom, my test case create table xxx as select * from dba_tables; insert into xxx select * from dba_tables; I tried 2 queries 1* select distinct * from xxx 2359 rows selected. Execution Plan -----------------------------------------...
Categories: DBA Blogs

Please help to understand how Nested Table cardinality estimation works in Oracle 12C

Wed, 2019-02-20 18:26
Hi Team, Request your help with one issue that we are facing. We pass a Nested table as a variable to a select statement. Example SQL is: <code>SELECT CAST ( MULTISET ( SELECT DEPTNBR FROM DEPT ...
Categories: DBA Blogs

Presentation of function result, which is own-type table via SELECT <func> FROM DUAL in sql developer.

Wed, 2019-02-20 18:26
Hi TOM, I've created a function, that granting access to tables and views in given schema to given user. In result, function returns a own-type table, that contains prepared statement and exception message, if thrown. 1. Creating types: <code...
Categories: DBA Blogs

With clause in distributed transactions

Wed, 2019-02-20 18:26
Hi Tom ! As there is put a restriction on GTTs: Distributed transactions are not supported for temporary tables does that mean that inline views in a query, i.e. using WITH clause, but those with MATERIALIZED hint will not work properly...
Categories: DBA Blogs

SQL Performance Tuning

Wed, 2019-02-20 00:06
Hi Team, i am kinda new to SQL performance tuning, So i need you guys to suggest me some helpful hand books (with test cases) which will suit for me. I have googled for it but don't know which one should i prefer as a newbie. So please share your...
Categories: DBA Blogs

What does argument "shares" stand for in create_plan_directive()?

Wed, 2019-02-20 00:06
Hi, In procedures create_cdb_plan_directive() or create_cdb_profile_directive() of dbms_resource_manager package, it is clear what role plays The parameter called "shares". Now, I fail to see what can be done with this parameter in create_plan_dir...
Categories: DBA Blogs

Dynamic database creation

Wed, 2019-02-20 00:06
Hello.. I'm asking if there is anyway to create an Oracle PDB database dynamically, I mean after the completion of some web registration, for example if I want to provide a complete private oracle database for my customer after he/she register to ...
Categories: DBA Blogs

How to print function name in sqlplus along with creation time.

Wed, 2019-02-20 00:06
Hi, I need advise on below query. Whenever we created any procedure/function from sqlplus command prompt then Function created message shown in sqlplus. E:g SQL> @C:\abc.fnc; Function created. 1:Can...
Categories: DBA Blogs

REGEXP_LIKE - Pattern match with complex logic

Tue, 2019-02-19 05:46
I want to do the regexpr pattern matching and I couldn't. Please help. I want to evaluate the value that is going to be inserted in DB. I want to perform a check as below. Only four special characters and alphabets are allowed. other special ch...
Categories: DBA Blogs

The relationship between null and 0

Tue, 2019-02-19 05:46
I asked you one last time and I will ask you one more question. If !=0, we know we should include null, but the result is not null. If !=0, it is different from zero and null, should not it also contain null if it is not 0?
Categories: DBA Blogs

Select from a table where a key value has matching link for all key values of another table

Tue, 2019-02-19 05:46
I have a set of three tables (T1, T2, T3) that represent dictionary data from external sources. I need to match the data from table 1 with the data in table 3 where <b><i>all</i></b> the rows in table 1 for a given OR_ID are reflected in table 3. T...
Categories: DBA Blogs

Update statement to flag rows

Tue, 2019-02-19 05:46
Hello, Ask Tom team. I'm using the query below to load rows to a destination database based on some conditions. After this is done I want to flag those rows in order to exclude them in the next SSIS ETL run. <code>select t1.invoice_sender,t1.ei...
Categories: DBA Blogs

latch undo global data

Tue, 2019-02-19 05:46
Hi team, I see spikes in oem for wait event latch undo global data . This is on insert statement , having concurrency of 50 Inserts in one second Due to heavily loaded db ash report takes high time It would be helpful if you share some s...
Categories: DBA Blogs

bound variables

Tue, 2019-02-19 05:46
I would like to know more about sql injection. Why is it so hard to tell to the Oracle that a certain string is a parameter and not a part of a Sql command? For example, can a person call himself Delete and his name can not be used in a search? And i...
Categories: DBA Blogs

unable to connect using database link

Tue, 2019-02-19 05:46
DEAR TOM, I CREATED A DATABASE LINK ON MY LOCAL DATABASE USING THE FOLLOWING COMMANDS. SQL> CREATE DATABASE LINK RP 2 CONNECT TO PRINCE 3 IDENTIFIED BY PRINCE 4 USING 'ORB'; Database link created. SQL> SELECT COUNT(*) FROM DUAL@...
Categories: DBA Blogs

Performance issue with data densification process

Mon, 2019-02-18 11:26
Hi Tom, I am facing an issue while making sparse data dense. Here is the problem statement: We are getting price information for securities from upstream in a file and prices will come only when either there will be new security on boarded or t...
Categories: DBA Blogs

The relationship between null and 0

Mon, 2019-02-18 11:26
Coding 1) <code>select comm from emp where comm is not null and comm != 0;</code> Coding 2) <code>select comm from emp where comm != 0;</code> The results of Coding 1 and Coding 2 are the same. I know that the values ??of null and 0 ar...
Categories: DBA Blogs

Table and Index maintenance

Mon, 2019-02-18 11:26
Good Afternoon Tom, I'm going to develop a little SQL Tool for maintenance of compress tables and indexes for our DWH Schema, our clients have Oracle EE (11.2 and 12.2), my "big" doubt is use or not use parallel execution because i see that using ...
Categories: DBA Blogs

writing a stand-alone application to continuously monitor a database queue (AQ)

Mon, 2019-02-18 11:26
Hi Tom, A question regarding oracle AQ... I wish to write a small stand-alone application that would *constantly* monitor a queue (only one queue) for the arrival of a message and as soon as a mesage arrives, take some action. I figured I could use...
Categories: DBA Blogs

Best way to enforce cross-row constraints?

Mon, 2019-02-18 11:26
I use the database to declare (and enforce) as much application logic as I can. What I'd like to do is to enforce application constraints across related rows, if possible. As a contrived example, suppose we have a table of Agreements and a secon...
Categories: DBA Blogs

Pages