Feed aggregator

Using dbca -silent -createTemplateFromDB xxxxxx command to create template file?

AskTom - Hot Questions - Sun, 08/13/2017 - 00:00
hello, i use ' dbca -silent -createTemplateFromDB -sourceDB host:port:sid -templateName sidbak -sysDBAUserName xxxxxx -sysDBAPassword xxxxxx' to generate template file, for example, [oracle@orcl13 ~]$ dbca -silent -createTemplateFromDB -sourceDB o...

What is the directory in an impdp operation

AskTom - Hot Questions - Wed, 08/09/2017 - 00:00
I have new server and i installed oracle 10g same like production. and i Have databump full backup using sys user, i need to restore on the new server. I used the following command while database is not mounted but asks for directory p...

listagg gives ORA-01427: single-row subquery returns more than one row

AskTom - Hot Questions - Thu, 08/03/2017 - 00:00
I need to concatenate row field into one field and I'm trying to use LISTAGG, but I need values to be distinct in the list. I was able to do almost everything with regexp_replace as alternative, but when I have too many orders for a customer I would...

update bulk row based on case statement

AskTom - Hot Questions - Wed, 08/02/2017 - 00:00
Hello Experts, We have a requirement as per below example, For Example, Need to create a procedure which fulfill below condition, create or replace procedure as <declaration part> Begin select emp_id,emp_name,mgr_id, (case when dep...

SQL Execution Speed depending on Line Breaks in Query

AskTom - Hot Questions - Wed, 08/02/2017 - 00:00
Dear Tom, if I execute in Toad a query like: select * from SOMESCHEMA.TABLE_A A join SOMESCHEMA.TABLE_B B on B.A_ID = A.A_ID join SOMESCHEMA2.TABLE_C C on A.C_ID = C.C_ID join SOMESCHEMA.TABLE_D D on B.D_ID = D.D_ID where C.C_ID = 1234; ...

Same query from multipule sessions, but dont see Read By Multipule Session

AskTom - Hot Questions - Tue, 08/01/2017 - 00:00
Hi, I was going through the below link, and doing some testing of my own. http://oracleinaction.com/simulate-buff-busy/ Focusing only on the below. PROCEDURE RUNSELECT as cursor dummy is select * from t; c1 t_refcur; rec dummy...

ORA-01001: invalid cursor while using function returning ref cursor.

AskTom - Hot Questions - Wed, 07/26/2017 - 00:00
<code>Hi Team, I am getting these errors (1 - ORA-00932: inconsistent datatypes: expected - got - 2 - <b>ORA-01001: invalid cursor</b>) when I am using function returning ref cursor. Can anyone help me in this. These are the efforts...

GETTING ORA-29270: too many open HTTP requests error

AskTom - Hot Questions - Wed, 07/26/2017 - 00:00
Hi, Please find below procedure from which we are keep on getting "ORA-29270: too many open HTTP requests" error, Procedure: <code>CREATE OR REPLACE PROCEDURE test_task ( p_quote_number IN cct_quote.quote_number%TYPE, p_quote...

Load CSV into table and split column into different rows

AskTom - Hot Questions - Sat, 07/22/2017 - 00:00
I have to load a csv file data into a table in oracle. One of the columns of csv file has data more than 4000 characters which has many values. These values can be distinguished from one another by keyword "http" or "https". I need to split them int...

Not able to recover Space even after Shrink space

AskTom - Hot Questions - Thu, 07/20/2017 - 00:00
Hi Tom, I have DB with one tablespace and many datafiles whereas tables are spread over many datafiles. I used below query to check fragmented tables list. select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE...

Break the results in select and put them in an XML

AskTom - Hot Questions - Wed, 07/19/2017 - 00:00
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...

"Fast" MERGE PARTITION

AskTom - Hot Questions - Wed, 07/19/2017 - 00:00
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...

How to Create Missing Records with Analytical Functions

AskTom - Hot Questions - Fri, 07/14/2017 - 00:00
Hi AskTom Team, I am having some trouble figuring out a query to do the following: I have a staging table populated by an external system. This table stores information about how much an item sold during a day. If an item hasn't sold anything d...

How to group timestamps in 10 minute buckets and aggregate

AskTom - Hot Questions - Thu, 07/13/2017 - 00:00
I've been grappling with this problem for almost a week. Other threads on this site have gotten me close but not all the way. I created a table and populated it using sqlldr. The queries for that are below this question. I want dateValidFrom ...

SQL over PL/SQL

AskTom - Hot Questions - Tue, 07/11/2017 - 00:00
Hi Team, Could you please have a look at below scenario: I have 3 tables: select * from tab_login_details; select * from tab_request; select * from tab_access; Basically i need output as below: FK_TB_LOGIN_MASTER FK_TB_COMPANY_DETAILS FL...

my oracle database load is particularly high

AskTom - Hot Questions - Thu, 06/29/2017 - 00:00
Dear Mr Connor, my oracle database load is particularly high,and I don't connect it ,finally application developer reboot their application server to solve it! I export an AWR report for failure diagnosis in peroid time, <code> WORKLOAD REPOSIT...

DENSE_RANK - AGGREGATE function

AskTom - Hot Questions - Tue, 06/20/2017 - 00:00
Below is the the employee table, EID NAME SALARY COMM_PCT 100 Steven 24000 101 Neena 17000 0.6 102 Lex 17000 0.8 145 John 14000 0.4 If I do the below select to employee table I get the output as 3. SELECT DENSE_RANK(17000,0.6) W...

Unload Javascript web page contents to a text file to load in database table

AskTom - Hot Questions - Mon, 06/19/2017 - 00:00
Hello Experts, I have a requirement wherein I need to unload/export web page contents whose data is populated by javascript to a text/csv file; which in turn will be be loaded in the database table. I am not sure if utl_http will get the job d...

Pages