Tom Kyte

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

Sysdate with TimeZone

Fri, 2017-09-22 08:06
Hi, I am trying to get the date with time zone in the below format (ISO 8601 I GUESS) YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00) where: YYYY = four-digit year MM = two-digit month (01=January, etc.) DD ...
Categories: DBA Blogs

Schema - Objects - Internal Tables

Thu, 2017-09-21 13:46
In studying aspects of oracle a realize that the schema concept is quite elaborate and that they are many internal tables that I can reference. For example, I came across this code SELECT table_name from all_tables where owner = 'My_Schema_Na...
Categories: DBA Blogs

Modify an attribute to having GENERATED AS IDENTITY

Thu, 2017-09-21 13:46
I created a table x with PK x_key_id as NUMBER. I also created a sequence to be able to increment this key. Now, we have upgraded to 12C and I want to take advantage of oracle's embedded "sequence" Creating a new table y with the surrogate key ...
Categories: DBA Blogs

Time difference

Thu, 2017-09-21 13:46
Hi Tom, <code>select '21.00' - '21.30' from dual</code> I need the time difference as .5 instead of 0.30 for my calculation. Regards, Rajarajan.M
Categories: DBA Blogs

Removing # from the columns returned by regexp_substr

Thu, 2017-09-21 13:46
Hi, I have following sql query: <code>with texttab as ( SELECT ROOT_CAUSE as text from cust_bug_data where ROOT_CAUSE is not NULL ) SELECT regexp_substr(text,'\#[a-z0-9_]+',1,level,'i') as tag FROM texttab CONNECT BY regexp_substr(te...
Categories: DBA Blogs

extract numbers from varchar2 upto first occurrence of a non-numeric value

Thu, 2017-09-21 13:46
On Oracle12 database, we have this table tbl_house_number that has one column "house_number" which is a varchar2 and has data entries of all different combinations. And all I need is the numbers from left to the first occurrence of a non-numeric li...
Categories: DBA Blogs

How to Group Rows into Buckets Based on Chunk Size

Thu, 2017-09-21 13:46
Hi Chirs/Connor, LiveSQL: https://livesql.oracle.com/apex/livesql/s/flfiwlt4m3yqb6v2tync6gr4o I have a table test, from that i want to get the details as follows: Get the no of chunk having file size <= 2GB E.g. id = 1,2,3 sum of these three ...
Categories: DBA Blogs

Changing Select_Catalog_Role Privileges

Thu, 2017-09-21 13:46
Hello, I need to customize the Select_Catalog_Role taking out some Grants but don't have the necesary previledges. Can you please tell me what previledge I need? Thanks Patrick
Categories: DBA Blogs

How to track long running sql query

Thu, 2017-09-21 13:46
Using V$SESSION_LONGOPS View, How can DBA self check long running query and also help to find out most resources consume?
Categories: DBA Blogs

Joining multiple tables inside a UPDATE statement

Thu, 2017-09-21 13:46
Hello Tom, Need your feedback on joining multiple tables inside UPDATE statement Scenario: i want to update a column value of table 'a' based on the reference/key value in table 'd'. Below is my query. It's working good for small number of record...
Categories: DBA Blogs

Golden Gate to AQ

Wed, 2017-09-20 19:26
I am trying to find the solution on sending the Data from Golden Gate Streams to Oracle Advanced Queue. Please advise what are the steps i need to follow.
Categories: DBA Blogs

Error while compiling java source

Wed, 2017-09-20 19:26
Hi, My question is While compiling java source I am getting warning as : JAVA SOURCE compiled Warning: execution completed with warning Failed to resolve object details Code: <code>create or replace and compile java source named "DirTestLi...
Categories: DBA Blogs

Invoke Remote stored procedure that has an Array of varchar's as IN parameter

Wed, 2017-09-20 19:26
I have the following scenario: 1 ? Two databases, SOURCE e TARGET, connected by a dblink. 2 ? On TARGET database, i have a procedure that invokes another procedure on database SOURCE 3 ? The procedure on database SOURCE has an IN parameter that ...
Categories: DBA Blogs

What are the datatype restrictions, while moving tables via the ALTER TABLE <table_name> MOVE command

Wed, 2017-09-20 19:26
Hello, We are running, 12.1.0.2 db. I have been tasked to move all the tables,spread across various schemas, from un-encrypted tablespace to encrypted tablespace. I am planning on using the ALTER TABLE <table_name> move command, to move most...
Categories: DBA Blogs

Query Performance Large SQL

Wed, 2017-09-20 19:26
Hi Tom, Thanks for taking time to read my question. I have a query that joins about 36 tables. Driving table has about 43 million records. 5 other tables have about 9 million records. Rest of the tables are small. Its a combination of inner and l...
Categories: DBA Blogs

Golden Gate, Streams

Wed, 2017-09-20 01:06
Hi Chris/Connor, Can you please help to clarify below query: We have a Archival requirement, where we need to : 1. Copy certain tables data (older than 7 years) from Primary DB to secondary DB 2. Delete those copied data from Primary DB. S...
Categories: DBA Blogs

need to extract numbers from a varchar upto a non-numeric character

Wed, 2017-09-20 01:06
Hi, I have a column called house number which is a varchar2, and it has all kinds of combinations of data entered (incorrect format) as shown below: House_Number ------------- 416-A 416-A 1573A 131# A23 133 A-21 133 A22 13320A 133A-21 1...
Categories: DBA Blogs

transfert files,delete&loadData

Wed, 2017-09-20 01:06
Hello, I would like to do these 3 steps automatically one time per day with oracle: 1) transfert .csv files. 2) Delete data from table. 3) Load table again with sqlldr from .csv files transferred. So if you could please let me know what would ...
Categories: DBA Blogs

Database is very slow....

Wed, 2017-09-20 01:06
Hi, I have a database in Oracle 10.2.0.4. My database is being used by JDE application and Block size set to 16KB. We are using FATA disk to store the datafiles of the database. We are maintaining separate tablespaces for data and indexes. We are us...
Categories: DBA Blogs

XMLQuery ORA-19114: XPST0003 - error during parsing the XQuery expression:

Wed, 2017-09-20 01:06
I'm doing examples from workbook. I created table and insert couple of records. Below is my code: Create table: <code>CREATE TABLE test_Pracownicy (IDPracownika NUMBER(3), Dane XMLTYPE); Insert record to the table: INSERT INTO test_Pracow...
Categories: DBA Blogs

Pages