Tom Kyte

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

truncate table reuse storage

5 hours 30 min ago
Hi Tom, I am getting to do same action why? TRUNCATE TABLE xyz where to_date('12/30/1899', 'MM/DD/YYYY') + dtimestamp < to_date('01/01/2017','MM/DD/YYYY') REUSE STORAGE; still getting messages like Error report: SQL Error: ORA-03291: I...
Categories: DBA Blogs

Oracle DBMS_SCHEDULER looping found error ORA-27478

5 hours 30 min ago
I've some script for running bat file windows using DBMS_SCHEDULER. But there is some error when there is looping with several data. <code>CREATE OR REPLACE procedure SEAT.ss_print_fundoshi_run_weld_tst (ptype varchar2) is cursor c_data is se...
Categories: DBA Blogs

migrating database among platforms of different endianness

5 hours 30 min ago
Greetings, I am trying to migrate a whole database from windows to aix based platform. The endianess of these OS are small and big consecutively. I have done just the tablespace migration using datapump utility and rman conversion of datafiles. ...
Categories: DBA Blogs

SPFILE READ IN 12C

5 hours 30 min ago
Hi team, I have seen alert log it is showing message like this. DB has not been restarted then why the spfile read it is showing because as per my understanding it shows message like this when we start the db. DB IS - 12c ASM also there. ...
Categories: DBA Blogs

Views perform better?

5 hours 30 min ago
We are displaying several complicated reports in our web application. These reports use joins in many Orcale tables. Tables may have 1000s of records. Do we gain any performance by making Views that perform complicated SQL. And then in our applicatio...
Categories: DBA Blogs

Find duplicates in String.

Tue, 2017-04-25 17:06
Hello- I have bunch of numbers returned via a report, as shown. What would be the best way to return numbers that occur more than once? For ex: In the first data set, I should return 11; in the second data set I should return 9 and 12 and so on...
Categories: DBA Blogs

Dynamic sql with case statement.

Tue, 2017-04-25 17:06
hello, i have wrote normally a long query and i use many case statements and i want to reuse this query. therefore i want to create a procedure with parameter to do that. At the end i want to create a view or a table with this query. therefore i hav...
Categories: DBA Blogs

REGEXP_REPLACE Match Parameters m and n.

Tue, 2017-04-25 17:06
I've been trying to write a query that will remove the comments from another query. Here's the small sample query-with-comments, from which I want to remove the comments: <code>WITH sample_SQL ( line#, txt ) AS ( SELECT line#, txt FROM dual ...
Categories: DBA Blogs

Index coalesce clean waits on enq: TX - row lock contention

Tue, 2017-04-25 17:06
Hi TOMs! I've noticed that periodically our 12.1.0.2 Asynchronous Global Index Maintenance job (PMO_DEFERRED_GIDX_MAINT_JOB) gets hung up from time to time and I've tracked it down to a long running insert causing the job to wait on enq: TX - row lo...
Categories: DBA Blogs

Reporting with SQL, How we can merge multiple execution of SQL

Tue, 2017-04-25 17:06
Hello TOM, we are generating report with SQL Query and output save to text file. I want to know if we can avoid multiple execution of SQL queries by using any other better way to achieve this and also not want to impact performance. we execute ...
Categories: DBA Blogs

using LIKE operator with IN Clause

Tue, 2017-04-25 17:06
Hi, Currently we have a query as given below, select count (distinct chk_ppl_val.serialnum) serialnum__ndv, count (distinct chk_ppl_val.individual_id) individual_id__ndv from chk_ppl_val where (substring(industry_code,1,4) in (SELECT distinct st...
Categories: DBA Blogs

P1TEXT, P1, P2TEXT, P2

Tue, 2017-04-25 17:06
Hi Tom, Want to understand what exactly P1TEXT, P1, P3TEXT, P2 denote in various v$ views like v$session, v$session_wait. Documentation says P1TEXT : Description of the first wait event parameter P1 : First wait event parameter (in decimal) ...
Categories: DBA Blogs

scheduler job not raise error

Tue, 2017-04-25 17:06
Hi askTome team. I have two database one name is prod and another is test. Their version are 12.2.0.1 I create a scheduler job on db:prod like this <code> begin dbms_scheduler.create_job( job_name=>'J_TEST_02', job_typ...
Categories: DBA Blogs

Trigger to capture before and after insert value of particular column

Tue, 2017-04-25 17:06
Hi Team, I have to create a trigger which will record the old value as well as new value in case of insert. Situation is that I have one application and on frontend part whenever some changes happened then the xyz_id got changed. At the backend, ...
Categories: DBA Blogs

Accepting SQL profiles

Mon, 2017-04-24 22:46
Hi - we are doing some data conversion of our database associated with a vendor product. This means migrating from one version of the vendor schema to another. so remapping the data. During a performance run, one of the SQLs was taking longer to run....
Categories: DBA Blogs

Correctly identifying Dynamic Sampling queries run by Optimizer

Mon, 2017-04-24 22:46
It is clear that dynamic sampling queries run by the optimizer contains <b>/* DS_SVC */ </b>clause in them (when traced). e.g. SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel...
Categories: DBA Blogs

Pivot and null values

Mon, 2017-04-24 22:46
Hi team, I have a table test having column as name,id,language.... <code>name id language a 1 eng b 2 eng c 3 fer d 4 (null) select * from TEST pivot (min(id) for language in('eng' as "english",'fer' as "french",)) </code> ...
Categories: DBA Blogs

RANGE Partition in DATE column DD/MM/YYYY HH24:MM:SS

Mon, 2017-04-24 22:46
Hi team, I need to partition TEST_PARTITIONS table on the basis of end_date using RAGE INTERVAL partition. <code>create table TEST_PARTITIONS partition by range(end_date) ( partition p2010 values less than (to_date('01-Jan-2011','dd-mon-yyy...
Categories: DBA Blogs

Why Exadata can not go for redundancy in Disk Storage ?

Mon, 2017-04-24 04:26
Hi , All MPP systems suffer from re-distribution at run time . The Fast data Loading is a Myth as u Load once but read as long u wish . Exadata with storage cells still constrained by shared disk if we consider RAC env . Why Oracle can n...
Categories: DBA Blogs

How to remove orphaned breadcrumb ?

Mon, 2017-04-24 04:26
Hello Tom, I have deleted a page in an APEX application. THat page was built with a breadcrumb. But I forgot to specify I wanted the breadcrumb to be deleted with the page. THe result is I have an orphaned breadcrumb... How can I delete it ? ...
Categories: DBA Blogs

Pages