Tom Kyte

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

Count number of occurances by date and by code.

Wed, 2016-12-14 14:46
Hi All- I need to create a report that shows number of occurrences of a student being suspended (by 2 different suspension codes) for a range of dates (usually first day of month and last day of month) by school. Here is the starting query, th...
Categories: DBA Blogs

Grouping with DateRange

Wed, 2016-12-14 14:46
Hi Tom I have the following Case: CREATE TABLE TEST_DATERANGE ( TITLE VARCHAR2(10) ,DATEFROM DATE ,DATEUPTO DATE ); INSERT INTO TEST_DATERANGE VALUES ('Test A',TO_DATE( '01.12.2016' , 'DD.MM.YYYY' ),TO_DATE( '05.12.2016' , 'DD.MM.YYYY' ))...
Categories: DBA Blogs

The year zero

Wed, 2016-12-14 14:46
When I try to create a date in the year 0 I get an error: <code> SQL> select to_date('1-Jan-0000AD', 'dd-Mon-yyyyAD') 2 from dual 3 ; select to_date('1-Jan-0000AD', 'dd-Mon-yyyyAD') * ERROR at line 1: ORA-01841: (full...
Categories: DBA Blogs

Search for values in all tables

Wed, 2016-12-14 14:46
Hi Please I want your help In database 10g I want to search for a value (number or char) I don't know it's stored in any table of database Like search for 'king' the result be table_name=employees , column_name=emp_id Thanks for your efforts
Categories: DBA Blogs

FORCE result cache for queries that are not cacheable?

Wed, 2016-12-14 14:46
Is there a way to force the result cache for ANY query and let the application handle invalidation? In an extreme (not very wise) case, do this: select /*+ force_result_cache */ * from dba_tables where table = 'XXX'; Basically, we want to c...
Categories: DBA Blogs

filtered hierarchical query

Tue, 2016-12-13 20:26
I have some hierarchical data (potentially ~80 million rows, if that's important): <code> drop table test; create table test (id number,pid number,t varchar2(20)); insert into test values (1,NULL,'Animalia'); insert into test values (2,1,'Mamm...
Categories: DBA Blogs

Identifying latest partition.

Tue, 2016-12-13 20:26
Hi Guys, I have this query to identify partitions to be moved to a new tablespace. select distinct 'ALTER TABLE ' ||table_owner ||'.'||table_name|| ' MOVE PARTITION ' ||PARTITION_NAME|| ' TABLESPACE TBS_NAME UPDATE GLOBAL INDEXES;' from dba_tab...
Categories: DBA Blogs

Partitions in 11g

Tue, 2016-12-13 20:26
We nee to partition existing tables having billions of records in 11g. In 11g there is no provision where we can alter table to add partitions. Will DBMS_REDEFINITION package can be used to do that. I have never used it, so could you please su...
Categories: DBA Blogs

ONLINE datafile defragmentation Oracle 11.2g

Tue, 2016-12-13 20:26
Hello Guys, In past few days I was trying to figure it how to defragmentate datafile. Tablespace has even more that one datafile. I thought that with method I will be able to move extents from the end of datafile to the begging. ALTER TABLE ...
Categories: DBA Blogs

Grouping on column to many coulmns

Tue, 2016-12-13 20:26
What up guys, I'm new here, I've been scavenging the internet looking for an answer to my challenge and to be honest its hard to articulate what I need. I figured this would be my next step. I have the following table which i need to self joi...
Categories: DBA Blogs

Query on application continuity

Tue, 2016-12-13 02:06
I'm thinking of configuring Application Continuity for my 2 node RAC (12c) database where my application is running on Weblogic server. Before I proceed with the setup of AC, I need to clarify/confirm few things regarding AC: 1. AC does not requir...
Categories: DBA Blogs

in wait event "disk file operations i/o", P1/p2/p3 is 8/0/8, the first 8 refers to the file type, what kind of file does the file type 8 stands for?

Tue, 2016-12-13 02:06
one of our DBs experienced a performance slow problem after a network issue, according to AWR report, the 2 bottlenecks are "disk file operations i/o" and "session login/logoff", since we have db audit turned on and it writes audit info to reboot os ...
Categories: DBA Blogs

Package Performance: Cloned vs Original

Tue, 2016-12-13 02:06
Hello Tom, This is my First POST. I experienced a performance issue with one of the package and in order to debug/troubleshoot the package, I cloned the original package and without changing any content of the cloned package; I ran a test for a...
Categories: DBA Blogs

Index Clustering Factor and Insert

Tue, 2016-12-13 02:06
HI Connor, I followed the below article by Tom, http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52asktom-1735913.html Lets ignore the CHAR, VARCHAR, and NUMBER context, and focus only on the clustering factor. Then, what woul...
Categories: DBA Blogs

APPEND hint in MERGE without INSERT clause

Tue, 2016-12-13 02:06
Hello, from the performance point of view: does it make sense to add the APPEND hint into the MERGE statement in that case there is only an UPDATE clause? If yes, what is the benefit? Thank you, Zdenek
Categories: DBA Blogs

Is there any way to convert an complex object type to JSON in 12c

Tue, 2016-12-13 02:06
Hello Tom We have a procedure which has an complex object type as output (we didn use Refcursor as we had lot of reusable codes in case of using object). This data is populated in Application layer for display and the same is moved back to a JSON...
Categories: DBA Blogs

ORA-02070: database does not support in this context

Mon, 2016-12-12 07:46
Hi, I am facing the below exception - ORA-02070: database does not support in this context Here is the query I am using - INSERT INTO table_1 (a,b,c) SELECT table_name , partition_name , subpartition_name FROM user_subpartitions; ...
Categories: DBA Blogs

Problem with PL/SQL, Cursor and trigger

Mon, 2016-12-12 07:46
Hi, I have some little problem with my exercises: i have these two tables CREATE TABLE PRENOTAZIONI ( CodPrenotazioni NUMBER(5) PRIMARY KEY, DataPrenotazioni DATE DEFAULT SYSDATE NOT NULL, CodProg NUMBER (3) NOT NULL, CodUtente VARCHAR2 (20) N...
Categories: DBA Blogs

Date Validation without function

Mon, 2016-12-12 07:46
Hi There, I've a string column and i would like to validate it to date between 01 Jan 1930 to 01 Jan 2030, Please help me. Date might be anything like below 1226547879 basdhbcus489 34^&dsulch56
Categories: DBA Blogs

Globally unique integers

Mon, 2016-12-12 07:46
My application uses NUMBER columns as primary keys, and uses sequences to populate the column (via a trigger with the typical "if :new.customer_id is null then select seq_customer_id.nextval into :new.customer_id from dual" construct). Now, I woul...
Categories: DBA Blogs

Pages