DBA Blogs

TABLE Function for custom type

Tom Kyte - Thu, 2017-04-27 05:26
Hello , I have this collection: a nested table built from a custom record that holds three varchars fields, and two numeric ones. The collection is filled with data inside a procedure. I need to sort that collection and I also need to choose...
Categories: DBA Blogs

Date in a nearest leap year

Tom Kyte - Thu, 2017-04-27 05:26
I've got the problem to solve. To find nearest(next or previous) date of the event. It happens every leap year at the particular date. Let's say 1st of May. How to generate such dates and get the nearest of them? Thank you. Our request for more ...
Categories: DBA Blogs

Get Primary Keys of Affected Rows in Update Statements

Tom Kyte - Thu, 2017-04-27 05:26
Question is simple Is there any way where I can get primary keys of a table which have been updated by update statements ? By doing that I want to leverage a select for an update Example There is a table EMPLOYEE with EmployeeID,EmployeeNam...
Categories: DBA Blogs

Big Data SQL Cloud Service now Available!

Oracle Big Data SQL Cloud Service has been released! Big Data SQL is Oracle’s breakthrough approach to simplifying access to big data sources. Oracle Big Data SQL provides the ability to query all...

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

Big Data SQL Cloud Service now Available!

Oracle Big Data SQL Cloud Service has been released! Big Data SQL is Oracle’s breakthrough approach to simplifying access to big data sources. Oracle Big Data SQL provides the ability to query...

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

truncate table reuse storage

Tom Kyte - Wed, 2017-04-26 11:26
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

Tom Kyte - Wed, 2017-04-26 11:26
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

Tom Kyte - Wed, 2017-04-26 11:26
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

Tom Kyte - Wed, 2017-04-26 11:26
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?

Tom Kyte - Wed, 2017-04-26 11:26
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.

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

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

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

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

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

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

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

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

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

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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs