DBA Blogs

catalog database gets shutdown frequently

Tom Kyte - Thu, 2017-09-07 19:46
Greetings, A catalog database is present. It gets automatically shutdown freqently and we need to start it up manually. How should i know the reason behind the shutdown. Are there any logfiles or trace files i should refer to? Your's Sincerely, S...
Categories: DBA Blogs

Generating Date range depend on Week Days

Tom Kyte - Thu, 2017-09-07 19:46
Daer Tom I would like generate date range and insert into a table depend upon week days. Example: I have date range 01-09-2017 to 30-09-2017 and the week days I want choose is Mon,Wed Following thing I want to generate: 04-09-2017 06-09-...
Categories: DBA Blogs

Stored procedure management

Tom Kyte - Thu, 2017-09-07 19:46
Hi Tom, I have created 3 stored procedures, I need to run the stored procedure in such a way when the activities of 1st stored procedure completed then run 2nd, only when the activities of 2nd stored procedure completed then only run the 3rd. Please...
Categories: DBA Blogs

Concatenate the rows WITH NO column to group

Tom Kyte - Thu, 2017-09-07 19:46
Hello Experts, I am preparing some stored procedure and there is a need to group the result into a string with any delimiter. The challenge here is I dont have any column ( with same data ) to group. Below is my scenario. <code>CONTACT_NO ...
Categories: DBA Blogs

ORACLE 11 - 1-way hashing

Tom Kyte - Thu, 2017-09-07 19:46
I am using Oracle 11 and I need to use a Hashing-Function to store passwords. Oracle 11(DBMS_CRYPTO) supports only SHA-1. Since SHA-1 is pretty broken, I want to user other, more secure hashing functions. Unfortunately, I cannot use Oracle 12 in ...
Categories: DBA Blogs

Restricting Production access while granting Read-only Access to Replica DB (Data guard)

Tom Kyte - Thu, 2017-09-07 01:26
Hello Experts, We replicate our critical Production Databases using Data guard, which implies that all the security user roles are as well replicated in both the environments. As such what techniques one can follow to GRANT READ ONLY Access to Rep...
Categories: DBA Blogs

What overhead (if any) do pipelined functions have?

Tom Kyte - Thu, 2017-09-07 01:26
I have some situations where I have a report built in Apex with a huge query in the application. Ideally we like to keep as much code as possible on the database (for dependency checking purposes etc) however the reason these have been left in Apex i...
Categories: DBA Blogs

How to retrigger an external remote Job

Tom Kyte - Thu, 2017-09-07 01:26
TITLE: How to retrigger an external remote Job Oracle external Job fires only once (an External, Remote, and Recurrent job): INTRODUCTION: My External, Remote, and Recurrent Oracle Job fires only once, and then it remains RUNNING. Then, ?I...
Categories: DBA Blogs

Checkpoint processing

Tom Kyte - Thu, 2017-09-07 01:26
My Question: 1. When there is check point, data is written on Disk even if the transaction is not committed yet, when roll back issued, how does it happen..? I Mean end up with physical delete insert and update or logical operation like if ? Ins...
Categories: DBA Blogs

Error while sending the attachment using UTL_SMTP mail

Tom Kyte - Thu, 2017-09-07 01:26
Dear Tom, We are getting error Sqlcode : -29279 - Sqlerrm :ORA-29279: SMTP permanent error: 554 Transaction failed: Missing start boundary while sending the attachment using UTL_SMTP. please help us on this issue. Thanks in advance. ...
Categories: DBA Blogs

Exploring connect by

Tom Kyte - Wed, 2017-09-06 07:06
Hi Tom, I am new here, so if i ask wrong ques or not following the guidelines, apologizes. Suppose i have a table like this: <code>COL1 ---- A B C D E F G</code> I have to transform this columns in a single cell like this A,B,C,D,E,...
Categories: DBA Blogs

Performance issue.

Tom Kyte - Wed, 2017-09-06 07:06
we are in the process of performance tuning , the below program is taking time, Please guide us to tune further or can we write any other way to improve the performance, <code>CREATE OR REPLACE TYPE "RECO_FE" FORCE AS OBJECT ( o_o_id ...
Categories: DBA Blogs

Upper bound length constraint for list partitioned tables (ORA-14116)

Tom Kyte - Wed, 2017-09-06 07:06
Dear Tom, We do have some table where partitions will be created frequently: <code>CREATE TABLE partitioned_table ( someUsefulPartitionIndicator NUMBER (*,0), someData varchar2(4000) ) partition by list (someUsefulPartitionIndicato...
Categories: DBA Blogs

How to optimize query for multiple joined tables

Tom Kyte - Wed, 2017-09-06 07:06
Hi TOM, I have this simplified version of a query. I would just like to check if there are other ways to further optimize the query especially on the subqueries. I'm limited to SQL only. Reason for me thinking that this can be further optimized...
Categories: DBA Blogs

mutating table

Tom Kyte - Wed, 2017-09-06 07:06
hello, i've got a table MRC and a trigger on it (AFTER INSERT) thus, after an insert in the table MRC, this trigger has to determine if a new line must be inserted into an other table PLAN : for that, it does compare the :new values with the MO...
Categories: DBA Blogs

Tuning Question

Tom Kyte - Wed, 2017-09-06 07:06
Hi I have the following query: <code> SELECT cust_id, cosmos_cust_id, prot_id, customer_name, protocol_name, dist_order_no, cust_ivr_ref_no, ship...
Categories: DBA Blogs

Update 5 Million records in Database in least time

Tom Kyte - Wed, 2017-09-06 07:06
I have approx to 5 million records in a table and I need to update one column of this table from another Table. <b>I have used Bulk Collect with FORALL option ( limit = 500 ) but it is taking 6 to 7 hours</b>. Do we have any option available with ...
Categories: DBA Blogs

Retrieving just the 1st record from each group of accounts

Tom Kyte - Wed, 2017-09-06 07:06
I have records in a marketer table and an account table that I want just the 1st account for each marketer that was notified on a specific date. How can I do that with a query? <b>MARKETER 10033 COMPANY 1 10036 COMPANY 2 10044 COMPANY...
Categories: DBA Blogs

V$session contains KILLED sessions

Tom Kyte - Wed, 2017-09-06 07:06
Hi Oracle Masters, Is there any way to remove killed session rows from v$session Manually ? Fahd
Categories: DBA Blogs

Virtual Workshop - Oracle Identity Cloud Service FY18

Virtual Workshop - Oracle Identity Cloud Service FY18 Date: Tuesday,12th September, Time: 10am CET (09am GMT/11am...

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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs