Tom Kyte

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

data guard drift

18 hours 16 min ago
I have setup DR with maximum performance mode, at any time of instance, how to find the duration of time of data difference between DC and DR.
Categories: DBA Blogs

DATABASE STORAGE USAGE IN ASM

18 hours 16 min ago
Hey Guys, I am working in a RAC environment and have been struggling to get a perfect query to view ASM Storage usage based on specific and each databases on the ASM. <b>Simply, i want a result that shows a database and how much size that databas...
Categories: DBA Blogs

Materialized View Add Columns

18 hours 16 min ago
Hi Tom, I have added a new column in my master table and i want this column to be reflected in my Materialized view is this possible without Re creating it i have to fetch all columns from my master table in my view.please suggest.
Categories: DBA Blogs

Event SQL*Net break/reset to client in refresh of materialized view

18 hours 16 min ago
Dear, I have had event of "SQL*Net break/reset to client" in session (job) with refresh of materialized view Same after kill it the session, kill the running job, the new session again remains with this event. The solution always is re-create th...
Categories: DBA Blogs

oracle utl_file encoding from utf8; missing character

Sat, 2018-08-18 14:46
I like to export large amount of text data from db to file. The characterset in db is UTF8. The excepted result in the file is ISO8859P2 or MSWIN1250. My db settings: <code> SELECT * FROM v$nls_parameters; 1 NLS_LANGUAGE HUNGARIAN 0 ...
Categories: DBA Blogs

How do I determine how much storage will be required for NUMBER(p, s)?

Sat, 2018-08-18 14:46
<i>Oracle Database SQL Language Reference</i> states, "Each NUMBER value requires from 1 to 22 bytes." I was curious how much space a NUMBER(10, 0) would take, so I did: <code>SQL> create table t (a number(10, 0)) 2 / Table created. SQL>...
Categories: DBA Blogs

Exception when executing Bulk Collect

Sat, 2018-08-18 14:46
I am using bulk collect with save exceptions, it runs fine sometimes and sometimes it errors out. The error is as follows - <code>BEGIN * ERROR at line 1: ORA-00028: your session has been killed ORA-00028: your session has been killed ORA-...
Categories: DBA Blogs

Performance issue after database upgrade from 11.2.0.4 to 12.1.0.2

Sat, 2018-08-18 14:46
Hi Team, We have recently upgraded a SAP database from 11.2.0.4 to 12.1.0.2, After encountered performance issues that did not exist on the previous version(11.2.0.4). Proper keeping sga_target/sga_maxsize did not help Proper keeping db_file_m...
Categories: DBA Blogs

DBMS_SQLTUNE_UTIL0

Sat, 2018-08-18 14:46
Hi Tom I have a question regarding DBMS_SQLTUNE_UTIL0 package. This seems to be helper package. Is it okay to use this package directly in code or use of such internal packages should be avoided? I did not find any documentation on this packa...
Categories: DBA Blogs

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

Pages