DBA Blogs

how to traverse the tree in reverse direction?

Tom Kyte - Fri, 2017-09-22 08:06
dear Tom, In a hierarchical query how do I traverse the tree from the branch level to the top? Say in the emp->manager case, given the empno, I want to find his manager, manager's manager etc. thanks,
Categories: DBA Blogs

Sysdate with TimeZone

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Quick Python script to backup remote directory using ftp

Bobby Durrett's DBA Blog - Wed, 2017-09-20 18:47

I looked around for some other ways to do this but decided to just code this up in Python. It connects to a remote Linux server using ftp and recursively copies all the files and directories back to a Windows machine.

Here is the source:

.gist table { margin-bottom: 0; }

This is an example of the ease of use of Python for quick scripting. It uses a low-level ftp library called ftplib.

Bobby

Categories: DBA Blogs

Partner Webcast – Data Management Platform for Innovation

The Oracle Cloud Platform enables customers to accelerate innovation while reducing costs and modernizing their infrastructure leveraging Oracle’s Data Management Cloud Solutions. They can...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Golden Gate, Streams

Tom Kyte - 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

Tom Kyte - 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs