DBA Blogs

Getting data from Multiple Users

Tom Kyte - Mon, 2016-08-08 09:46
Hi Tom, Please find the below problem statement and kindly provide the solution. Problem Statement: I have a schema and that schema contains a table T1. T1 table contains 2 columns i.e. UserId and CityId. T1 ------------- USERID CITYID -...
Categories: DBA Blogs

RMAN restore into a new database

Tom Kyte - Mon, 2016-08-08 09:46
Hi.. 3 days before I took Level 0 and Level 1 backup from my database( name:sample) by using RMAN .Yesterday my target database was crashed.So I created another new database(sample1). Now I want to use those backup files and restore that all data in...
Categories: DBA Blogs

Lost control files

Tom Kyte - Sun, 2016-08-07 15:26
I have couple of questions regarding controlfiles 1.I am connected through catalog and lost all the control files of database how can i recover the database and get all control files back please suggest 2.If in lost of all control files will I be a...
Categories: DBA Blogs

Updating / Resetting columns

Tom Kyte - Sun, 2016-08-07 15:26
Hey Guys, I have a process where I need to update one column with the value of another if the 1st column is null, when I transfer the value I need to set the value of the second column to null, I am doing this in a single update statement, see exa...
Categories: DBA Blogs

MVIEW Dependencies

Tom Kyte - Sun, 2016-08-07 15:26
Where are the MVIEW dependencies stored? SQL> create view y1 as select 1 as y1 from dual; View created. <code>SQL> create materialized view y2 as select y1 as y2 from y1; Materialized view created. SQL> create view y3 as select y2 as y...
Categories: DBA Blogs

Selecting rank wise records

Tom Kyte - Sun, 2016-08-07 15:26
Hi Tom, Its been such a long time to visit here and resolve the doubts but this is the first time I am asking. There is an Employee table with Name, Department, Salary and Date of Joining in it. I need to write a query which will give the outpu...
Categories: DBA Blogs

Oracle Text CONTAINS with NULL input string

Tom Kyte - Sun, 2016-08-07 15:26
Hi Tom, I am on Oracle 11gR2. I have a table where I have created a text index. I have written a query as below: SELECT * FROM my_transactions t WHERE 1=1 AND CONTAINS (t.search_transactions_flag, '%'||:str||'%') > 0; It prompt...
Categories: DBA Blogs

out-of-place refresh of a materialized view

Tom Kyte - Sun, 2016-08-07 15:26
I am researching how to improve the availability of data in an MV. The doc says that while doing a complete refresh of MV using the out-of-place option that the data in the mv is still available, which I find is true. But it also says the mv is av...
Categories: DBA Blogs

Oracle

Tom Kyte - Sun, 2016-08-07 15:26
TABLEA ****** C1 C2 C3 (COLS) * - - - * - * - * - * * I have 6(*) in the above table? But when i select my table i get the count 6 result how? sir? From my side i have done like select count(c1) from tb...
Categories: DBA Blogs

plsql trigger

Tom Kyte - Sun, 2016-08-07 15:26
create or replace trigger foremp before insert on emp declare day char(10); begin if (to_char(sysdate,day) in ('sunday')) then raise_application_error(-20300,'not allowed on sunday'); end if; end; / I created this trigger t...
Categories: DBA Blogs

Are we really create table aumatically as per our requirement

Tom Kyte - Sun, 2016-08-07 15:26
Hi, I want to create a table automatically as per based of various column data types and various number of column. Please ! dont cconsider the constraint. for example: table name :table1 required output is : DDl structure of crea...
Categories: DBA Blogs

PLSQL syntax

Tom Kyte - Sun, 2016-08-07 15:26
VARIABLE NAME CANNOT START WITH A) NUMBER B) SPECIAL CHARACTER C) CHARACTER D) ALL OF THE ABOVE I KNOW THAT WE CANNOT START A VARIABLE USING NUMBER ALSO SPECIAL CHARACTERS EXCLUDING(&,#,_). THIS IS A QUESTION I GOT FOR AN EXAM AND I SELECTED...
Categories: DBA Blogs

Partner Webcast – Oracle REST Data Services Communication for Cloud and Mobility

Representational State Transfer (REST) is a style of software architecture for distributed hypermedia systems such as the World Wide Web architecture delivering web services with a significant impact...

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

Is there a way to report on PROCESSES, SESSIONS AND CONNECTIONS that were killed using the kill command?

Tom Kyte - Sat, 2016-08-06 21:06
Is there a way to report on PROCESSES/SESSIONS/CONNECTIONS that were killed using the kill command?
Categories: DBA Blogs

Timestamp comparison in Oracle

Tom Kyte - Sat, 2016-08-06 21:06
Hi Guys, I need to fetch almost 2 years old records for 1 day i.e if today is 5 Aug I want to fetch records of 25 July. For this I wrote below query select * from Employee where employee_start_ts between add_months(sysdate-12, -24) and ad...
Categories: DBA Blogs

IOPS in oracle database

Tom Kyte - Sat, 2016-08-06 21:06
Hi Team, We need to check the read write iops in oracle database how can we check. Thanks
Categories: DBA Blogs

Do tons of sequences create any kind of problems?

Tom Kyte - Sat, 2016-08-06 21:06
Do sequences take up much memory or cause any other trouble if you end up with 10s of thousands of them? ========================= Unnecessary details: We have to design a service to generate unique IDs. We can do to a couple ways - One i...
Categories: DBA Blogs

Deleting large number of records

Tom Kyte - Sat, 2016-08-06 21:06
I have a table with 1 Million records. Because of undo log size, i can not delete this 1M records in one shot. So, need to create a PLSQL block which will delete the record in batches (let's say undo log can sustain .1 M records at one shot). Could y...
Categories: DBA Blogs

Sql trace and identify problem

Tom Kyte - Sat, 2016-08-06 21:06
My main question is how to identify cause of sql? alter session set timed_statistics=true alter session set max_dump_file_size=unlimited alter session set tracefile_identifier='MYSESSION_TODAY' alter session set events '10046 trace ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs