Tom Kyte

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

SQL Performance Tuning

13 hours 1 min ago
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()?

13 hours 1 min ago
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

13 hours 1 min ago
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.

13 hours 1 min ago
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

Efficient way to fetch data using rownumber from a billion record table??

Fri, 2019-02-15 10:06
I'm trying to fetch required data from a billion record table using the row number approch (i.e, select "ingest_ts_utc" from (SELECT to_char(sys_extract_utc(systimestamp), 'YYYY-MM-DD HH24:MI:SS.FF') as "ingest_ts_utc" ,ROWNUM as rno from XYZ.ABC ) ...
Categories: DBA Blogs

using sql loader for a table where you only have insert privileges via a non default role

Fri, 2019-02-15 10:06
TeamTOM, I'm trying to use Sql Loader to load a text file into a normal Oracle table (as opposed to an external one). Unfortunately I only have insert rights to the table via a non default role. If I were just doing a simple insert statement in sq...
Categories: DBA Blogs

Access Table Without Synonym and Without Schema Name

Thu, 2019-02-14 16:06
I have a schema called HCR_SCHEMA, in which I have one table called TEST_EMP, and I have a user say USER_A and granted him select on the TEST_EMP table. I connected as USER_A as tried to execute SELECT * FROM TEST_EMP. Obviosuly it threw me error ...
Categories: DBA Blogs

print an XMLTYPE Record Type variable

Thu, 2019-02-14 16:06
we have a following definition TYPE response_t IS RECORD ( doc XMLTYPE ); A local variable is defined with that record type lv_resp response_t; I want to print the value this local variable has stored.I tried ...
Categories: DBA Blogs

Pages