Tom Kyte

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

Packages are invalid -- ORA-04061

Fri, 2018-12-14 02:46
Hi, In our DB, few packages became invalid. And when we verified it we saw that there are no errors related to them. I was expecting sessions to run this package without any error, however when it was executed for first time, we got ORA-04061 erro...
Categories: DBA Blogs

Full-Text Index (Table, Index and LOB files) Size Creep

Fri, 2018-12-14 02:46
Q: How do we "manage" the size of a table's associated SYS_LOB files? Background: I have a table [SRCH_CACHE] that was setup as a lookup table, because the two base tables could not be joined/filtered with sufficient speed. The table is pretty ...
Categories: DBA Blogs

How to forecast archive generation for a 350 GB table with no large objects.

Fri, 2018-12-14 02:46
Hi Gurus, Thanks for helping us whenever we need you. I have a task to import a 350 GB table. I will create index later. Its a 12.2 CDB with one single PDB and have a standby DB with force_logging enabled. Is there a method so that I can f...
Categories: DBA Blogs

How to compare date duration of first row with second and so on.. and convert it to rows to column with indicators

Fri, 2018-12-14 02:46
Hi Tom, I have below table <code>CREATE TABLE PRDCT (ID NUMBER(10,0), PRDCT_CD VARCHAR2(1), START_DT DATE, END_DT DATE);</code> With data in this like - <code>Insert Into PRDCT Values (1,'A',to_date('01-May-2017'),to_date('31-Jul-2017')...
Categories: DBA Blogs

SQL Plan Directives

Thu, 2018-12-13 08:26
Team, reading this article <u>https://blogs.oracle.com/oraclemagazine/on-learning-from-mistakes</u> and setting up an demo like this in my local database. In the below example pretend OWNER as "state" and OBJECT_TYPE as "city" (similar to the examp...
Categories: DBA Blogs

To arrive alphanumeric value from a string

Thu, 2018-12-13 08:26
Hi Tom, I have a problem to arrive unique alphanumeric value I have to arrive unique short name(5digit) from names and insert into the table. We have to check existing short names and arrive next heirarchy. Eg.The names are like below ...
Categories: DBA Blogs

List tablespaces and usage

Thu, 2018-12-13 08:26
how can List all the tablespaces in the ORCL database by displaying the available space, used space, status and type. Also permanent and temporary files related to those tablespaces in a separate query.
Categories: DBA Blogs

Inconsistent behavior with CROSS APPLY and OUTER APPLY

Thu, 2018-12-13 08:26
This question is also asked on http://stackoverflow.com/questions/42593148/inconsistent-behavior-with-cross-apply-and-outer-apply by a Graphql collaborator who is developing pagination for supporting Oracle db. have a schema in Oracle-12c similar...
Categories: DBA Blogs

Quick Question for Oracle Set define off query

Wed, 2018-12-12 14:26
Hi All, I just want to know that if i turned off the feature i.e. "SET DEFINE OFF" from my oracle database, will there by any consequences / disadvantages with my data or database, as i really need to turn it off for some queries. Please advi...
Categories: DBA Blogs

how to correctly identify the correct ASCII values for the special characters.

Wed, 2018-12-12 14:26
Hi The column Text contains the apostrophe as special characters. Did a dump. you can see the results below. How do I correctly search for these special characters and then replace them with apostrophe ? You see the first row has the ASCII valu...
Categories: DBA Blogs

SQL Window function to skip and add rows dynamically

Wed, 2018-12-12 14:26
<code>create table test1 (col1 number,col2 varchar2(5),col3 date,col4 number,col5 number,col6 number); --Positive scenario sample data insert into test1 values(1,'A',trunc(sysdate),0,5.5,2); insert into test1 values(1,'A',trunc(sysdate)+...
Categories: DBA Blogs

Converting CLOBS TO VARCHAR

Wed, 2018-12-12 14:26
Can you give me a solution for converting CLOBS datatype to VARCHAR datatypes, all the documents I refer to talk about converting BLOBS to VARCHAR and when I try and apply the examples to CLOBS, get errors
Categories: DBA Blogs

What is object_type program

Wed, 2018-12-12 14:26
Tom, I was recently looking at distinct object_type in dba_objects and noticed that one object_type was PROGRAM. Where can I find more information on object_type PROGRAM? Thanks...
Categories: DBA Blogs

Switch Insert to temp Segment Merge

Tue, 2018-12-11 20:06
Hi, some of our parallel direct path insert Statements suffer from enq: HV contention. These are big, straight forward inserts. Contention can be up to 60% of the db time. We did some tests and realized that temp Segment merge would get rid of t...
Categories: DBA Blogs

Oracle client 12.1 with the new OracleCredential class

Tue, 2018-12-11 20:06
Our SW includes component that uses ODP.NET, we upgraded ODP.NET to 18c to benefit from the new <i>OracleCredential</i> class. Problem: According to the Oracle.NET team, ODP.NET 18c would support only 18c clients, yet, we still need to support oth...
Categories: DBA Blogs

Most effective way to Delete large number of rows from an online table on a daily basis

Tue, 2018-12-11 20:06
I have a need to write a cleanup script that would delete old data (1-2 Million rows) based on a date on a daily basis. Almost equal amount of rows are inserted into the same table daily as well. Any suggestions on the most efficient way of doing th...
Categories: DBA Blogs

Dynamic sort order

Tue, 2018-12-11 20:06
Hi, Query 1: SELECT * FROM (SELECT 10 a,20 b FROM dual UNION SELECT 50,30 FROM dual) ORDER BY CASE 1 WHEN 1 THEN a ELSE b END DESC; output: A B ------ 50 30 10 20 Query 2: SELECT * FROM (SELEC...
Categories: DBA Blogs

What should a PL/SQL Developer should learn in 2019

Tue, 2018-12-04 04:46
Hi Tom, I worked for around 14 years as Oracle developer, over SQL, PL/SQL, Forms Reports and APEX. Now since 2018 we are observing major changes like people are using Rest API rather direct backend coding. So what should a traditional Oracle ...
Categories: DBA Blogs

bulk collect through dynamic sql

Mon, 2018-12-03 10:26
I have written a procedure which extracts data from tables into a csv file. The tables and columns to be extracted are stored in two tables. My code picks up the table name and column name builds a dynamic query and then writes it to csv file. I have...
Categories: DBA Blogs

Create/Replace procedure command

Mon, 2018-12-03 10:26
Hi Tom, Are there any issues with issuing a "create or replace procedure" command when the application that uses the database is up and running in a production environment ? What happens when you fire this script when this same procedure i...
Categories: DBA Blogs

Pages