AskTom - Hot Questions

Subscribe to AskTom - Hot Questions feed AskTom - Hot Questions
These are the questions on Ask Tom that have the most comments
Updated: 1 hour 21 min ago

Difference between named sequence and system auto-generated

Mon, 11/12/2018 - 00:00
Hello, Guys. A new db (12c) will have lots of tables with sequence used as PK. What is the difference between named sequence and system auto-generated in Oracle 12c? What would be the best approach?

Oracle Sequence vs GUID

Wed, 11/07/2018 - 00:00
We're developing a new system in for the company with .NET and Oracle Database 12cR2 using RAC. It basically does the following: receives invoices in XML format, then validates it according to business rules. A requirement is to give to the clien...

Hierarchy Validation - Incoming hierarchy vs Expected hierarchy

Wed, 11/07/2018 - 00:00
Hi Experts, I have a requirement to validate the hierarchical structure of flat files which are loaded into the database against an expected structure that is known. I have created some sample data to demonstrate what I would like to do: <code>...

Manual dataguard

Wed, 10/31/2018 - 00:00
hi there we are using oracle database 11g ( Standard Edition one) for our production db i have gone through the docs to setup automatic log apply which is available only for Enterprise Edition.. i saw the notes from the doc saying ...

Is there way to convert user defined type to JSON?

Fri, 10/12/2018 - 00:00
Is there way to convert user defined types/collections to JSON (the same way as it's possible to convert them to XML via XMLTYPE)?

Tablespace sizing for datawarehouse

Sun, 10/07/2018 - 00:00
Hello Team, my question is a bit similar to but I will need further clarification. I am loading data on monthly partitioned tables from flat files using sqlloader ev...

Is the Oracle regular expression supporting this character extraction?

Fri, 10/05/2018 - 00:00
Hi Oracle SQL experts, I am using Oracle regular expression to deal with some characters. My db is 12c. I have a string every line like this input like this 1234adhefd#123 345bheufs15# ... output will be from the first alpha lette...

SQL Query to Convert Ten Rows with One Column to Five Rows With One Column

Thu, 10/04/2018 - 00:00
i have a table with column name as value. There are 10 rows in the table. The desired output of this to be displayed as two columns first 5 rows as one column A and rows 6 to 10 as column B , next to each other as 5 rows of data like this <code>A B...

Query on dba_hist_active_sess_history query taking too long

Wed, 10/03/2018 - 00:00
Hi, I'm using the below query to fetch details from dba_hist_active_sess_history which matches a specific wait event occurring at a specific hour of the day within the last 90 days: select USER_ID, PROGRAM, MACHINE from dba_hist_active_sess_his...

Migrating Oracle 10g on Solaris Sparc to Linux RHEL 5 VM

Wed, 09/19/2018 - 00:00
Hi, if i will rate my oracle expertise i would give it 3/10. i just started learning oracle, solaris and linux 2months ago and was given this task to migrate. yes our oracle version is quite old and might not be supported anymore. Both platforms ...


Tue, 09/18/2018 - 00:00
Hi Tom, I installed Oracle Database 11G in the folder (E:\DB11)and successfully imported a user to it. But when you install the developer 10G IN FOLDER(E:\DEV10). and TRY TO DO a user import into the database ORACLE 11G, i am receiving the follo...

Parse string then flatten into columns

Thu, 09/13/2018 - 00:00
Hi, LiveSQL link not accepted by the form: I have this situation (see link): <b>ID String</b> Id1 Thing1: Sub1, <br>Thing2: Sub7 ,Sub8 , Sub9 <br>Thing3: Sub12 Id1 Thing...

Database link queries

Tue, 09/11/2018 - 00:00
create or replace procedure P_POP_ILC (P_POL_NO VARCHAR2 DEFAULT NULL) is cursor c1 is select * from rsds_locn_exposure@dmn_rsk_150 where RLE_ULM_NO = NVL(P_POL_NO , RLE_ULM_NO); begin DELETE FROM IDS_LOCN_CLM@dmn_rsk_150; comm...

How to use partial indexes for PK/UK keys

Fri, 09/07/2018 - 00:00
LiveSQL link: not working post link into LiveSQL link, but link works Hello all, we have a BI solution (Oracle 12c + ODI11). We have a lot of partitioned tables (daily and mont...

Regexp_Replace Help- Column Level Data Masking

Tue, 09/04/2018 - 00:00
Hi Tom, I have this requirement but regexp_replace is behaving odd here. It gives me all NULL Requirement for function- Suppose a number 9845-089160 ? function logic should convert one digit at a time and not entire number in one go. ? First ...

sql performance

Tue, 09/04/2018 - 00:00
Hi Tom,I have met a SQL that with the latest statistics and the execution plan is in the wrong estimate-rows ,that's to say,it's a large difference between the estimate and the actual,the sql statement and execution is below: <code>select t1.card_no...

Storage XMLs in Oracle 12cR2 database

Fri, 08/31/2018 - 00:00
Hello! I created a database in order to store XMLs. I'm going to storage a large amount of data. I was told that Oracle 12cR2 allows to storage XMLs and handle them very good. I created a table with a xmltype column. <code>CREATE TABLE test...

Want to use single select statement instead of various union all's for fetching previous quarter data

Fri, 08/31/2018 - 00:00
I have 5 select statements using union all(showing a few in here) want to use a single statement to fetch the revenue from a table . So based on the condition that AND period_id>=1009 AND Opp_Created_Date_Period_Id<=1009(for every previous quart...

TABLE function and where clause parameters retrieving

Fri, 08/31/2018 - 00:00
Hi there, My goal is to return fieldX or fieldY depending on the where clause (which I don't have any control on since it's generated by a third party software). The TABLE() function is very close to what I want to achieve : select * from T...

Issue with Trigger creation

Wed, 08/29/2018 - 00:00
I am having issues with Trigger. Trigger creation is successful but Once user try to test it, It is failing. Before trigger creation, tester is able to add new email to email_t table. But Once I place trigger, they are getting this : <code> ...