DBA Blogs

Redolog file

Tom Kyte - Sat, 2017-04-22 15:46
Why is writing to Redolog file is faster then actually writing changes to datafiles ?
Categories: DBA Blogs

remote startup

Tom Kyte - Sat, 2017-04-22 15:46
Hi tom, For the testing purpose, I have a remote DR database, how to startup the DR database from DC using the static listener registration method?? Please, provide me the sample listener.ora and tnsnames.ora file with connection to remote datab...
Categories: DBA Blogs

Use case statements to compare two columns

Tom Kyte - Sat, 2017-04-22 15:46
Hi Tom, Can you please suggest the best way to use case statements while comparing fields between two tables. create table t1(id number, first_name varchar2(20), last_name varchar2(20), birth_date date); insert into t1 (id, first_name, las...
Categories: DBA Blogs

Deleting old records from a Very big table based on criteria

Tom Kyte - Sat, 2017-04-22 15:46
I am having one table (Table A) that contain <b>300 Million Records</b>, want to do a data retention activity on basis of some criteria. Concerning the performance, I planned to create a new table(Table B) with the oldest 10M records from Table-A....
Categories: DBA Blogs

Exporting sequences along with table using DBMS_DATAPUMP package.

Tom Kyte - Sat, 2017-04-22 15:46
I am exporting some table of my schema 'KNK' using following code. <i> <b>declare h1 number; v_schema VARCHAR2(32); v_time VARCHAR2(32); v_job_state varchar2(4000); begin SELECT to_char(SYSDATE, 'DD_MM_YYYY_hh24_mi_ss') INTO v_tim...
Categories: DBA Blogs

Order rows of a table in the same order they have inserted

Tom Kyte - Sat, 2017-04-22 15:46
Hi Tom, Is there any way to get the rows of a table in the same order they have inserted, without using any sequence number or timestamp (trying to find out if oracle has some inbuilt functionalities to perform the action)?
Categories: DBA Blogs

Query Writing Differently (Relational Division)

Tom Kyte - Fri, 2017-04-21 21:26
<code>create table developers (name varchar2(30), skill varchar2(30)); create table projects (name varchar2(30), skill varchar2(30)); insert into developers values ('SMITH','ORACLE'); insert into developers values ('SMITH','JAVA'); insert i...
Categories: DBA Blogs

How this query returns value??

Tom Kyte - Fri, 2017-04-21 21:26
When I execute the following query , It returns the values. <code>select * from TABLE_1 where FIELD1 in ( select FIELD1 from Table2 where FIELD2 = 'AAAAAAA' and FIELD3 = 'SSSSSS') and FIELD2 in (select FIELD2 from TABLE3 where FIELD3 in ('XXXXX...
Categories: DBA Blogs

COUNT Aggregate Function and null

Tom Kyte - Fri, 2017-04-21 21:26
Hi, I created a table called Temp in that i inserted 2 NULL Rows so now total rows is 8. INSERT INTO TEMP VALUES(NULL,NULL); When i executed SELECT COUNT(*) FROM TEMP giving Result as 8 it also counted 2 NULL Rows as per my knowledge COUNT igno...
Categories: DBA Blogs

Link the value of entities from different object types in Oracle 12c

Tom Kyte - Fri, 2017-04-21 21:26
Let's assume we have the following TYPEs: <code>CREATE TYPE customer_rec_tp AS OBJECT ( customer_id NUMBER(10) , first_name VARCHAR2(20) , last_name VARCHAR2(20) , store VARCHAR2(50) , err_msg VARCHAR2...
Categories: DBA Blogs

Love Your Data Conference in NYC on 31st May

Pakistan's First Oracle Blog - Fri, 2017-04-21 19:11
In this InfoEra, its all about data.Whether its in the cloud or on-premises everything is truly revolving around and is for data. Pythian understood that decades ago and loving the data of their customers since day one. They are showcasing this love on 31st May in NYC.

http://promo.pythian.com/love-your-data-conference/


To help you turn your organization into a truly data-driven business, this interactive 1-day event in New York City on May 31, 2017, combines presentations, practical interactive panel sessions and open discussions across business and technical tracks.

This event is for CIO’s and IT Business leaders interested in learning how to better empower their company to drive business outcomes with analytics. Pythian’s Love Your Data Conference will focus on practical ways to:
  • Transform your organization using data and self-service analytics
  • Align IT to the business by giving all users access to data
  • Add data intelligence and automation to business decisions
  • Get a 360-degree view of your customer and promote innovation 
If you want to attend only one event this year then this must be the one.
Categories: DBA Blogs

Oralce Smart Flash Cache

Tom Kyte - Fri, 2017-04-21 03:06
Hello Team, Could you please explain why Smart Flash Cache is limited to Solaris and Oracle Linux platforms? https://oracle-base.com/articles/12c/database-smart-flash-cache-12cr1 Thanks.
Categories: DBA Blogs

Expected Date format coming as 00:00:00 using DD/MM/YYYY HH24:MI:SS

Tom Kyte - Fri, 2017-04-21 03:06
Hi Team, I am using the below query to get the records; which is working successfully. However when I am trying to get the time stamp in the column CREATED DATE then the 19/04/2017 00:00:00 & 3/4/2017 0:00. select distinct b.transaction_id as ...
Categories: DBA Blogs

Someone Delete data

Tom Kyte - Fri, 2017-04-21 03:06
In my office, someone deleted the Procedure. I want to know who did it? What time did it? Please tell the query to find the person information's like IP Address or Name.
Categories: DBA Blogs

Table Backup

Tom Kyte - Fri, 2017-04-21 03:06
Hi Team, I have had gone through one question in interview,I could not find answer to that. If you can help me with that. Let say,there is one table employee with millions of record and we want to increase the column size address_col(10) to add...
Categories: DBA Blogs

External table preprocessor

Tom Kyte - Fri, 2017-04-21 03:06
Hello, I have to read a file via external table but this file it's located into another directory inside the ASM. As first step I have to move the file So, I decided to use the preprocessor feature running a script Here's my table (premise: ...
Categories: DBA Blogs

How to get rid of double quotes from get_ddl

Tom Kyte - Fri, 2017-04-21 03:06
Hi Team, Is there any way to get rid of the double quotes from the output of dbms_metadata.get_ddl. Following is the sample output of dbms_metadata.get_ddl('TABLE','TEST') : e.g., CREATE TABLE "TEST" ( "F1" NUMBER(4,0) NOT NULL ENAB...
Categories: DBA Blogs

ROWID not incrementing. Need an alternative for deduping

Tom Kyte - Fri, 2017-04-21 03:06
Hi Tom, I have a SQL script which (runs daily) inserts data into a table and then de-duping is done based on the rowid. But recently I have observed that rowid is not incrementing with inserts and instead Oracle is re-using space to generate rowid...
Categories: DBA Blogs

How to create a stored procedure only when a particular table exists

Tom Kyte - Fri, 2017-04-21 03:06
Hi Tom, I'm trying to create a stored procedure only when a particular table already exists, using the following code: SET SERVEROUTPUT ON DECLARE v_row_count INTEGER := 0; e_wrong_db EXCEPTION; BEGIN S...
Categories: DBA Blogs

Is there a self-reference to my PL/SQL program's name?

Tom Kyte - Fri, 2017-04-21 03:06
Hi Tom, Is there a PL/SQL variable equivalent of the $0 variable in Unix. Within a procedure/function if I want to know the name of the program that I am running, can I get it from a system variable. I plan to write into a log table and I want to pa...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs