DBA Blogs

DDL Trigger to mimic same changes to other tables

Tom Kyte - Sat, 2017-04-15 00:26
Hi Tom, I was trying to write a DDL trigger that will apply the same DDL to other tables when 1 particular table is bieng altered. That trigger is working fine when I add column without any NOT NULL constraint on column but gives error when try...
Categories: DBA Blogs

How to track deleted records data in a file

Tom Kyte - Sat, 2017-04-15 00:26
Hi Tom, we have some exercise to delete records in > 300 tables depend on few requirements. We are using dynamic sql delete statement iterating through tables list. Is there any chance can I track what data is deleting from back-end, I want inse...
Categories: DBA Blogs

Plan Hash Values Changed For the Same SQL ID || How to determine why ?

Tom Kyte - Fri, 2017-04-14 06:06
Hello Team, I have a particular sql query which runs on a daily basis for around 40 mins on an average. Today it has been running since 4:50 BST and has not completed at all. I analysed what went wrong today and have detailed out what I could :...
Categories: DBA Blogs

the confusion about db_domain

Tom Kyte - Fri, 2017-04-14 06:06
Hi: Today I read the link below about the db_domain https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams052.htm#REFRN10036 it is said that Range of values of this parameter can not be null IS that right? because my datab...
Categories: DBA Blogs

RMAN - "report unrecoverable" command not listing the expected tablespace

Tom Kyte - Fri, 2017-04-14 06:06
I have added some rows to a table TAB_1 with NOLOGGING option after taking an RMAN backup. The tablespace TBS_1 is used for the table. When I tried to find the unrecoverable tablespaces using the command "REPORT UNRECOVERABLE", the expected tablespac...
Categories: DBA Blogs

Constraints

Tom Kyte - Fri, 2017-04-14 06:06
Is there a way I can drop a table without cascade if I have foreign keys pointing to the primary key of the table? Any idea?
Categories: DBA Blogs

CAST TABLE type not working with EXECUTE IMMEDIATE

Tom Kyte - Fri, 2017-04-14 06:06
Hi, I have created below TABLE type of VARCHAR2 - create or replace TYPE VC_ARRAY is table of varchar2(4000); and tables create table tb_test_src as select object_name, object_id from user_objects where rownum <= 20; create table tb_test...
Categories: DBA Blogs

UNUSABLE status of indexes , index partitions and index subpartitions

Tom Kyte - Fri, 2017-04-14 06:06
How can i find the unusable date of indexes, index partitions and index subpartitions , only with sqlplus , not looking in the alert log. - How can i find the date that the status of an index has became unusable ? - How can i know the date that t...
Categories: DBA Blogs

Data Encryption And Decryption In Tables

Tom Kyte - Fri, 2017-04-14 06:06
Team, As part of my request please consider the below create and insert statements : Version of DB being used is : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit create table bo_master(ssn number, bo_code number...
Categories: DBA Blogs

PLsql code to search and find pdf from folder and send in email as attachment

Tom Kyte - Fri, 2017-04-14 06:06
HI Oracle, i Have a requirement - search and find pdf from folder/directory and send via email to the particular receipient. here is more info about scenario: we have pdf files generated already in one folder/directory we need to send pdf f...
Categories: DBA Blogs

Merger the records with same Id

Tom Kyte - Fri, 2017-04-14 06:06
Hi Tom, I have a below scenario.This is not fixed and it is dynamic in nature APPLICANT_NAME,Renewal_NAME,NUMBER,TYPE,SUBTYPE,ETHNICITY, GENDER,ZIP_CODE,QUESTION_id,ANSWER_Id,Answer JAN,LR-001352966,LPN.039005.MEDS-IV,Licensed Practical,,White,Fem...
Categories: DBA Blogs

sub queries

Tom Kyte - Fri, 2017-04-14 06:06
Hi Tom, SQL> select * from customer_det; CID NAME DOP ---------- -------------------- --------- 1 Chandan 01-JAN-84 2 Ramu 01-MAR-84 3 Raghavendra 17...
Categories: DBA Blogs

Partner Webcast – Smarter Diagnostic and Insights for Enterprise IT Assets with Oracle Management Cloud

Across the globe, companies have embraced cloud computing and the agility it provides. By constantly recombining technology and cloud solutions, enterprises are able to provide an ongoing stream of...

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

Deploying a SpringBoot Application to Oracle App Container - Part 1/2

In this post I am going to create a simple SpringBoot Application and deploy it to Oracle's Application Container Cloud Service. Oracle ACCS is a polyglot application hosting environment for a number...

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

Unable to fast refresh after rebuild of master table.

Tom Kyte - Thu, 2017-04-13 11:46
Hi, I have: - master table and mlogs on 11g EE DB - fast refresh materialized view on 12g EE DB - those two databases are connected with dblink After rebuild was done on master table, I wasn't able to fast refresh my materialized view. ...
Categories: DBA Blogs

assign sqlplus value to shell script variable

Tom Kyte - Thu, 2017-04-13 11:46
Hi Tom I am trying to get the f_name value to FILE_VAR. My proc is having 2nd and 3rd parameter as OUT parameter. I want to take its "f_name" variable's value to Shell script variable "FILE_VAR" I tried below but its not working. <code> FILE_...
Categories: DBA Blogs

Adaptive execution plans inside Stored Procedure

Tom Kyte - Thu, 2017-04-13 11:46
I have a stored procedure that using a global temp table called ITEMS with delete on commit (session based stats). Inside the stored procedure, sometimes 1 row gets added to ITEMS, sometimes 200,000 rows to that table. ITEMS is then used in a few ot...
Categories: DBA Blogs

Regex for comma separated strong with predefined list of words that are allowed.

Tom Kyte - Thu, 2017-04-13 11:46
Hello, With this regex : not REGEXP_LIKE (categories, '[^(subcat1| subcat2 |subcat3|null; )]', 'i'), i can specified what kind of values i can be used and this are separated with ';'. but i can do that <code>insert into regexText Values(';') ...
Categories: DBA Blogs

Need to call Restful API using Oracle PL SQL

Tom Kyte - Thu, 2017-04-13 11:46
Hi, I am new to hitting Restful APIs from Oracle. I have this huge Xml (> 4000 characters) which I need to post to a remote restful api endpoint. Please let me know how to accomplish this. Below is my sample code that I am playing with right now....
Categories: DBA Blogs

ORA_HASH Value Collision.

Tom Kyte - Thu, 2017-04-13 11:46
Hi Tom, I am attempting to assign unique value to a an expression value which is distinct and derived from the concatenation of multiple fields. Here's the usage: Query1: create table Table2 parallel 16 as select /*+ parallel(a,16)*/ disti...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs