DBA Blogs

Calculating partial table size

Tom Kyte - Mon, 2017-04-24 04:26
Hello, I need to copy 30% of SOME_TABLE data, which occupies 3TB in total. Is there a way to estimate "actual" size of 30% of table records? I know that it is ~900GB by using simple math, but this may vary due to CLOB datatype and etc... so I w...
Categories: DBA Blogs

Question on Buffer Cache Reads Avg Time in AWR

Tom Kyte - Sat, 2017-04-22 15:46
Hi Tom, In AWRs, under the section <b><i>"IOStat by Function summary"</i></b>, we find a statistic called <i><b>"Buffer Cache Reads Avg Time"</b> </i> According to my understanding it is the avg time taken to do a <b>"Buffer Get"</b>, Am I right...
Categories: DBA Blogs

CPU Utilisation

Tom Kyte - Sat, 2017-04-22 15:46
Hi team, Every Saturday DB Server CPU Become very high after to restart the db also it become high i checked awr where i found "cpu quantum" event then i check SQL> select client_name, status from dba_autotask_client; CLIENT_NAME ...
Categories: DBA Blogs

MV log missing records

Tom Kyte - Sat, 2017-04-22 15:46
Hi, During maintenance of fast refresh materialized views, 10000 MLOG$ records are lost. Can you suggest what are our options for restoring those missing records to our MV? Making dummy update of those records on master table is not allowed due...
Categories: DBA Blogs

Size of sga_target and sga_max_size exceeding the size of physical RAM. Is it possible?

Tom Kyte - Sat, 2017-04-22 15:46
Greetings Sir, For an example, i have total ram of 2GB. I have disabled the AMM ie memory_target=0. This allows me to resize the sga parameters. I just tried to set the sga_max_size=5G and sga_target=4G. I restarted the instance. It goes normally...
Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs