Tom Kyte

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

Space Fragmentation in a table which has only inserts

Sat, 2018-08-18 14:46
Hi Tom, This is Praveen. 1)I have table x which has only inserts in it and updates rarely,so my doubt is till now i have heard and read that there will be space fragmentation for delete operation done on a table and i couldn't find out how the ...
Categories: DBA Blogs

definition of v$lock table

Sat, 2018-08-18 14:46
please find below query output.. select OWNER,OBJECT_NAME,OBJECT_TYPE from DBA_OBJECTS where OBJECT_NAME in ('V$LOCK'); ---PUBLIC V$LOCK SYNONYM as per above query output V$LOCK is SYNONYM. so i checked in DBA_SYNONYMS for further informa...
Categories: DBA Blogs

Rownum = 1 no longer returning results after upgrade to 12.1

Sat, 2018-08-18 14:46
Hi, I have a view that I created in 11g, after we upgraded the database to 12.1 it is no longer returning the dates for the view. I pulled the SQL for the view and confirmed that it no longer works. It does return the comment. I need to use the r...
Categories: DBA Blogs

Subtract hours and show milliseconds for a TIMESTAMP(6) column

Fri, 2018-08-17 02:06
I want to SELECT a TIMESTAMP(6) with milliseconds, but at the same time I need to subtract 3 hours (0.125 of a day) from that TIMESTAMP to convert it to my timezone. So I tried: <code>SELECT To_Char(UTCSCANTIME-0.125,'YYYY-MM-DD HH24:MI:SS') AS LO...
Categories: DBA Blogs

Unable to gather table stats in parallel

Thu, 2018-08-16 07:46
Hi, We're running gather_table_stats with the following arguments: dbms_stats.gather_table_stats(ownname=>'&owner', tabname=>'&table',estimate_percent=>0.1,block_sample=>true, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>fals...
Categories: DBA Blogs

Merge 2 PDF/png/jpg Files?

Thu, 2018-08-16 07:46
Good Evening Tom Is it possible to merge 2 binary files using oracle PL/SQL? I have seen examples using Java but not PL/SQL. Thanks for your time.
Categories: DBA Blogs

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

Pages