DBA Blogs

Update column in one table with grouped data from another

Tom Kyte - Thu, 2017-07-20 23:06
Hello, I was wondering if it is possible to group related records from one table to use as the data to be updated in another table. It seems logical to me but it is not working. I added a trimmed down version of the two tables with only the required...
Categories: DBA Blogs

Drop partition from MVIEWs

Tom Kyte - Thu, 2017-07-20 23:06
Hello Tom ! A question about PMOs on MVIEWs. It seems that PMO (split, merge, add, drop, rename, ... partition) operations on materialized views are working without need to drop materialized view to its prebuilt table. Also for FAST refreshabl...
Categories: DBA Blogs

"Fast" MERGE PARTITION

Tom Kyte - Thu, 2017-07-20 23:06
Hi Tom ! As of Oracle 12c there exists FAST SPLIT PARTITION. Is there something similar for MERGE PMO? The exact problem is this. As currently there is no way to increase HIGH VALUE of RANGE PARTITION (at least I'm aware of), the only way t...
Categories: DBA Blogs

Why do we have active redo log members, if a full checkpoint is performed at every log switch?

Tom Kyte - Thu, 2017-07-20 23:06
Dears, Hope you are fine I'm a little confused about active redo logfiles. I know that active redo log is not the current redo logfile being written to by LGWR. but the one that still have records needed by instance recovery. But the documentatio...
Categories: DBA Blogs

Break the results in select and put them in an XML

Tom Kyte - Thu, 2017-07-20 23:06
Hi Team, I have a table Employee as below <code>Employee_ID Employee_Name 1 Usha 2 Soma 3 Uma</code> . . . and so on I have 5000 records in the table I have a defined count only 1000 reco...
Categories: DBA Blogs

How to create time slice report based on two start and end date.

Tom Kyte - Thu, 2017-07-20 23:06
How to generate following report? <code>create table appointment_mas ( app_id number, app_start_date timestamp , app_end_date timestamp , doc_id number ); insert into appointment_mas values(1,to_date('19-Jul-17 09:00 AM','DD-Mon-YY HH1...
Categories: DBA Blogs

Active Data Guard different Block Sizes?

Tom Kyte - Thu, 2017-07-20 04:46
Dear Tom, Can we have different block sizes between active and pasiv databases, when using Active DataGuard? For example: Block size of 8K for Active and 64 for pasiv database which is in Read only mode? If so, what can be concicvences whe...
Categories: DBA Blogs

Understanding Regular Expressions

Tom Kyte - Thu, 2017-07-20 04:46
Hi Tom, I was trying to understang the regular expression from the below link https://community.oracle.com/message/13402428#13402428 WITH sample_data AS ( SELECT 'C,D,E' AS str, '[A-Z]?' AS pattern FROM dual ) SELECT LEV...
Categories: DBA Blogs

Quries slow at noon

Tom Kyte - Thu, 2017-07-20 04:46
Good day Please I need serious help, I have a table with 4 million records. Here is my query select * from table where custname like rownum <40 and ( '%WE%' or custnumber like '%WE%') These are both var2 even thou I say custnumber. This runs well ...
Categories: DBA Blogs

Partner Webcast - Oracle Exadata: Boosting Revenue

Join us to learn more about Oracle Systems, Engineered Systems and Storage. Learn what we understand by “Cloud Insurance” through our Systems Webcast Series for EMEA Partners. The...

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

Copying data from one partition to another in the same table

Tom Kyte - Wed, 2017-07-19 10:26
Hi Tom, we have a table that is daily partitioned based on activity date. On a daily basis, we do a bulk insert for todays (trunc(sysdate)) partition from previous days partition trunc(sysdate-1) with /*+ append*/ hint ( the value for partitionkey...
Categories: DBA Blogs

Privilege required for non DBA user to create the external table

Tom Kyte - Wed, 2017-07-19 10:26
Hi , What are the privilege required for a non DBA user to create the external table?
Categories: DBA Blogs

Skipping data corruption - Is there any way to skip past ORA-01427 in PLSQL Stored Proc?

Tom Kyte - Wed, 2017-07-19 10:26
Hi Tom I have a stored proc that dumps the content of view into a table. The stored proc simply uses "INSERT INTO - SELECT *" FROM statement to do its job. However, on one bad day, we had some data corrupted that resulted in ORA-01427. This Stor...
Categories: DBA Blogs

Optimizing minus funtion

Tom Kyte - Wed, 2017-07-19 10:26
Hi , I have a table with almost 500+ million records in it. i need to select distinct record based on the change code indicator , by which i mean , in my table for a given set business set key columns there can be many records with "A" , "U" an...
Categories: DBA Blogs

Display custom error using pragma

Tom Kyte - Wed, 2017-07-19 10:26
Hi, I am trying to associate a PL/SQL exception with a number and then use that to show my own error message to the caller. I keep on getting the default error message rather than my own error message. I have searched over the last couple of days ...
Categories: DBA Blogs

sql commands show different output from 11g to 12c

Tom Kyte - Wed, 2017-07-19 10:26
sql commands show different output from 11g to 12c Ex: 12.1.0.2 --------- SQL> sho parameter nls_date_format NAME TYPE ------------------------------------ -------------------------------- VALUE ----------...
Categories: DBA Blogs

Partner Webcast - Oracle Bare Metal for Oracle EMEA Partners

Oracle Bare Metal Cloud Services combine the elasticity and utility of public cloud with the granular control, security, and predictability of on-premises infrastructure to deliver high-performance,...

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

How to use DBMS_PARALLEL_EXECUTE to chunk over DB link

Tom Kyte - Tue, 2017-07-18 16:06
Hi Team, Can you please have a look at below: I tried copying table data from One DB to Other over DB link using DBMS_PARALLEL_EXECUTE. And it works as expected. Challenge I am facing here is: I have a table TB_TXN created on both Oracle D...
Categories: DBA Blogs

What type of role/grant that should be given to allow to grant permissions on other schemas

Tom Kyte - Tue, 2017-07-18 16:06
Hi, I have user, with name of "grant_schema". This user should be able to run below command. SQL> grant select, insert, update, delete on claims.table_name to CLAIMS_USER; What type of role/grant that should be give to my "grant_schema" to b...
Categories: DBA Blogs

Error in converting a date from one timezone to other

Tom Kyte - Tue, 2017-07-18 16:06
I want to convert a date in some time zone to date in the db timezone. Now for some reason i get UNKNOWN as the timezone_region when i run the query - <code>select extract(timezone_region from systimestamp) from dual;</code> But I was able to get...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs