Tom Kyte

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

Referential Partitioning

Mon, 2018-08-06 09:26
Hi Tom, I am working on an 11g database and writing a PL/SQL application that moves partitions from the active schema to an historical schema. In one instance, I have a RANGE partitioned table with two "child" REFERENCE partitioned tables. Since I...
Categories: DBA Blogs

Indexes on View

Mon, 2018-08-06 09:26
Hi Tom, Thanks for your kind support always. I have one question which is confusing me a lot. I know when any changes are made to base tables then those changes are reflected are on view as well when view is refreshed. But my question is revers...
Categories: DBA Blogs

Create Table Within SQL Script - When Changes Visible?

Mon, 2018-08-06 09:26
HI TOM, I have one SQL script. In That I am creating backup table say table A. After that I am updating the table A with some condition (with No Commit). After that I am creating another backup table say table B. After that I am updating ta...
Categories: DBA Blogs

Create a json object that combines a person with multiple addresses

Mon, 2018-08-06 09:26
I'm trying to create a json object that combines (for example) a person with multiple addresses. It could be for any parent/child relationship where there can be multiple children with multiple attributes. <code> -- ------------------------------...
Categories: DBA Blogs

Moving partitioned table and index to multiple tablespaces

Sat, 2018-08-04 02:06
I have a 10 billion row table, partitioned 32 ways. Each partition is located in a separate tablespace. Each of the 12 local partitioned indexes occupies its own tablespace as well (yes, total of 44 tablespaces). We are moving all application ta...
Categories: DBA Blogs

JSON_TABLE from array - not nested

Sat, 2018-08-04 02:06
The following json_table works perfectly well. The object "testing" holds an array, and data from the array is fetcehd as two rows, when the path is "$.testing[*]" <code>select j.* from json_table ( ' {"testing": [ { "message": "Th...
Categories: DBA Blogs

Date Intersection

Sat, 2018-08-04 02:06
Hi Team, Could you please have a look ate below scenario and help me with building SQL please.. If there are intersecting date range, rows which has longer date interval should get returned. i.e. One of the record for order 1 has date interval f...
Categories: DBA Blogs

ETL schemas on a production database

Sat, 2018-08-04 02:06
I need to support an ETL app that will use a production database clone as the data source. I'd like to put the ETL app's two schemas (target tables and pl/sql) on the production database, to limit ETL app set up that needs to be done on the clone aft...
Categories: DBA Blogs

Authenticate proxy user from windows credentials

Fri, 2018-08-03 07:46
I am trying to work out how to connect using a proxy but passing a windows credential in - like this: SQL> CONN proxy_user[domain\windows_user]/proxy_pass So far it doesn't seem possible. Do you know how this can happen? Thanks
Categories: DBA Blogs

The plan ignore my index

Thu, 2018-08-02 13:26
Good Afternoon, I have a table for the generation of a report by year and week but when I execute the query a TAF is marked. I tried to force the indexes but the execution plan ignores it. What can I do to take the index?, Is it necessary to cha...
Categories: DBA Blogs

How to grant v_$Session to a normal user, If we do not have access to sys user

Thu, 2018-08-02 13:26
How to grant v_$Session to a normal user, in a normal user we are using in a stored procedure. And we dont have access to sys user. By using select any dictionary privilege we can access but they do not want grant select any dictionary privilege to a...
Categories: DBA Blogs

PL/SQL query with NULL variables

Thu, 2018-08-02 13:26
What is the best way to handle a query with multiple variables, and some of the variables can be null, like: <code>FUNCTION GET_RECIPE(P_RECIPE_LIST IN VARCHAR2, P_OWNER_LIST IN VARCHAR2, ...
Categories: DBA Blogs

Global temporary table error

Thu, 2018-08-02 13:26
Hi AskTom, Can you please help me with this issue. Our application uses lot of global temporary table (GTT) has on commit preserve rows option. <code> CREATE GLOBAL TEMPORARY TABLE "ODR"."GTT_POINT" ( "POINT_ID" NUMBER(10,0) NOT NULL ENAB...
Categories: DBA Blogs

How to find all Mondays between two dates?

Thu, 2018-08-02 13:26
I have to find all mondays between two date range which can be parameterized or coming from two different columns of a table. Also need to generate a sql to get next 20 mondays from sysdate. can you please help me to get sql query for these 2 r...
Categories: DBA Blogs

Procedure having OUT parameter vs Function

Wed, 2018-08-01 19:06
Thanks for taking up this question. Are there any guidelines regarding when to use a procedure(OUT parameter) vs Function. Both structures can be used to achieve the same objective in specific situation. I have created a function F1 and a procedu...
Categories: DBA Blogs

returning top 10 records based on previous records counts

Tue, 2018-07-31 06:26
Hi, I have an ordered table of products and customers as follows: <code>rn product customer 1 859274 A 2 859267 A 3 859250 A 4 863592 B 5 862250 B 6 862700 B 7 862694 B 8 862120 B 9 863592 C 10 862250 C 11 862120 D 12 86...
Categories: DBA Blogs

Library Cache Locks and Compiling New PL/SQL Code

Tue, 2018-07-31 06:26
Hello! I have a friend that told me that compiling a new piece of Pl/SQL code will put library cache locks on the dependent objects, and that there's a risk for database contention when new code is deployed. In other words, if I go out to a datab...
Categories: DBA Blogs

Performance Tuning: Tracing Over a DB Link

Mon, 2018-07-30 12:06
hi Team, currenlty in our environment i see many query execute on dblink . we face performacne problem over their , can you let us know how we can trace sqlid of those sql's which are running remotely and how to figure out their bind variables...
Categories: DBA Blogs

CLOB and BLOB datatypes !

Mon, 2018-07-30 12:06
Hi Tom, How are you ? My Question to you is as follows :- If you have a column of type CLOB or BLOB in a table Is it possible to view the contents of this column in the select statement ? Is it possible to insert data into these col...
Categories: DBA Blogs

Iterating Logic

Mon, 2018-07-30 12:06
Hi I am trying to write a SQL query that computes the values of a column(mmwl) as follows. For the DDL and DML scripts please check the livesql link <code> create table t (y date, value_pvt int, mmwl int); --sample data to generate a rec...
Categories: DBA Blogs

Pages