Tom Kyte

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

SQL JSON ORA-40459 Exception

Thu, 2018-08-09 10:46
I am trying to use JSON functions like JSON_OBJECT and JSON_ARRAYAGG to generate a JSON string through SQL. We have alot of columns in our tables that have alot more data than 4000 bytes which I am trying to parse through and generate JSON string. Ev...
Categories: DBA Blogs

Merge Two Rows Into One Row With 2 Columns

Thu, 2018-08-09 10:46
Is there a way to simply merge via a single pivot or analytical query the results from two rows into one? We have a legacy table that is essentially storing key value pairs in columnar format. As an test case, here is an example: <code>create tabl...
Categories: DBA Blogs

Design question around automatic data change notifications

Wed, 2018-08-08 16:26
Hello Oracle experts. Thank you in advance for going through my question. In our organisation, we are planning to implement a solution to automatically push the data that is changed in our databases to consumers. They are not a high traffic system...
Categories: DBA Blogs

PLS-00172: string literal too long

Tue, 2018-08-07 22:06
Hi Tom, When i try to insert huge data into a CLOB colum, i get "PLS-00172: string literal too long". Tried searching in web/metalink, but of no use. Please let me know if a workaround is available for this insert. drop table t9; create tabl...
Categories: DBA Blogs

Time periods determined from input user dates and table defined periods

Tue, 2018-08-07 22:06
Hi, I have a procedure that takes 2 parameters (start and end date) and using some additional data from a "settings" table and some sales transactions, populates an intermediary table. The settings table was initially as 1 interval / 1 set of d...
Categories: DBA Blogs

Query a collection of JSON data

Tue, 2018-08-07 22:06
I need to query a collection of json data for specific data values contained. In this example, I'm trying to count the number of json objects where the LNAME attribute = 'Mallard'. Based on the documentation at: https://docs.oracle.com/en/d...
Categories: DBA Blogs

Syntax checking without execution of query

Tue, 2018-08-07 03:46
Hi Tom, I have a front end screen where user can form a select statement.Once user will submit the syntax checking I should verify whether the query syntatically correct or not?.How should I achieve above by using SQL/PLSQL?. For ex, ...
Categories: DBA Blogs

Log buffer size tuning

Mon, 2018-08-06 09:26
Oracle documents say Log buffer is normally small compared with the total SGA size, its key ratio is the space request ratio: redo log space requests / redo entries. Could you tell me what is the key ratio we are looking when adjust log buffer in ...
Categories: DBA Blogs

ERROR MESSAGE ORA-04030

Mon, 2018-08-06 09:26
Pl suggest how to solve the problem related to ORA-04030. Thanks Ajay Goel
Categories: DBA Blogs

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

Pages