Feed aggregator

Ora-12560: TNS: protocol adapter error

AskTom - Hot Questions - Mon, 05/13/2019 - 00:00
I use a single instance 12.2C 64-bit Oracle database on a window server 2012R2. suddenly this error <i>ORA-12560: TNS:protocol adapter error</i> began to show when I try to enter the sqlplus. whatever I have searched the internet a lot for a soluti...

on premises vs cloud

AskTom - Hot Questions - Thu, 05/02/2019 - 00:00
How the life of PL/SQL developer changes after the things start moving from on-premises to cloud? What are all tools we need and environment etc?

Sequence behavior skipping values

AskTom - Hot Questions - Thu, 05/02/2019 - 00:00
Hello, Ask Tom Team. I have a table in a 2-node RAC with an identity column. The sequence is generated by default, cache 3000 and noorder option. I see something weird. The first insert was id 1, which means that it came from instance 1, then...

Rebuilding local non prefix index raises ORA 02149: Specified partition does not exist

AskTom - Hot Questions - Mon, 04/29/2019 - 00:00
I truncated the partition on a table with local partition index. After inserting the records again in the same partition If I try to rebuild the index on that partition it gives ORA 02149: Specified partition does not exist. However i can see the res...

Stored Procedure behaves randomly - sometimes takes 15 minutes where it should take 15 milli-seconds.

AskTom - Hot Questions - Fri, 04/19/2019 - 00:00
We developed a procedure to be used in database-A and in database-A. In this procedure we send an input parameter on the basis of which a select statement searches the data from a table located in remote database-B. The selected columns are set in Ou...

Create Object with Column type attributes

AskTom - Hot Questions - Fri, 04/12/2019 - 00:00
Hi Tom, Please help me on one of our prod issue. We have an object and all the attributes in the object(EMP_OBJ) are columns of a table(Lets say EMP) in diff schema. So when we defined the object we gave the datatype and size of the object attr...

How lob columns are transferred by Oracle Net Services.

AskTom - Hot Questions - Wed, 04/10/2019 - 00:00
Hi Oracle manual states: 'Starting with Oracle Database 11g, Oracle Net Services optimized bulk data transfer for components, such as Oracle SecureFiles LOBs and Oracle Data Guard redo transport services. The SDU size limit, as specified in the n...

Mismatch between XML IsNumber and APEX JSON IsNumber

AskTom - Hot Questions - Tue, 04/09/2019 - 00:00
Hello, <code> DECLARE x xmltype; y CLOB; b varchar2(6); BEGIN WITH a AS (SELECT '<adr><str>bachstr.</str><tel>- 087</tel><tel1>-87</tel1><tel2>-087</tel2></adr>' t FROM dual ) SELECT xmltype(t) INTO x FRO...

Buffer Limit

AskTom - Hot Questions - Wed, 04/03/2019 - 00:00
Hi, The Buffer size we give for dbms_out.put_line is different from Data base buffer cache in SGA or same? and can you show me am example on how to use dbms_out.getline function. Thanks in Advance.

Subtotal, Grand Total, ordering and breaking on different fields

AskTom - Hot Questions - Thu, 03/28/2019 - 00:00
Hi Tom I was looking through the already asked questions about Subtotals and Grand totals , but couldn't really find anything that suits my problem. I have a table with the following fields <code>DATE REGION REG_NUM AMOUNT 0...

Infidelity when storing XMLType data elements (spaces)

AskTom - Hot Questions - Wed, 03/27/2019 - 00:00
When saving XMLType data into table. The data fidelity is is not maintained for fields containing only spaces (see LiveSQL). Before save, spaces are present in <COL> </COL> After reading the saved data, spaces are gone and we see empty tag <COL...

SQL query to find FK IDs with a series of values

AskTom - Hot Questions - Wed, 03/20/2019 - 00:00
we have below tables <code>create table bca(id number(2)); insert into bca(10); insert into bca(11); insert into bca(7); create TABLE abc( di NUMBER(2), fk_id NUMBER(3), yek VARCHAR2(20), elv VARCHAR(15...

How to move to a new tablespace a column LOB of the dataype ANYDATA ?

AskTom - Hot Questions - Wed, 03/20/2019 - 00:00
Hello Masters, I have a table with a PUBLIC.ANYDATA column. <code>SQL> desc XL_EURO_AXAIA.EXT_TCV_TCH_VALEUR Nom NULL ? Type ----------------------------------------------- TCH_ID NOT NULL NUMBER ID NO...

cannot access objects in different schema

AskTom - Hot Questions - Tue, 03/19/2019 - 00:00
I am the admin user and can create tables and procedures in any schema. I have few tables in Schema B which I am referencing in a package i am creating in Schema A however upon compiling it does not see the tables in Schema B. Schema B does not ha...


AskTom - Hot Questions - Wed, 03/13/2019 - 00:00
Hi TOM, I am having a data movement activity from one table to another table. Due to Huge data size in both tables, i have planned to use DBMS_PARALLEL_EXECUTE functionality, so that i can finish it quickly. I am creating chunks based on ROWID. ...

Need rank based on person_no column

AskTom - Hot Questions - Tue, 03/12/2019 - 00:00
Hi All, I have column ord which is common for all the records , then person_no. This can be repeated. I have a column called flag which is a sequence. I was trying populate a new column(NEW_FLAG) which will basically rank the person_no according...

Using connect by level to generate dates and times

AskTom - Hot Questions - Mon, 03/11/2019 - 00:00
I have two SQL Queries: SQL Query 1: <code>select to_date(:sDate,'dd-mm-rrrr')+(level-1)DateRange from dual connect by level <= ((to_date(:endDate,'dd-mm-rrrr')-to_date(:sDate,'dd-mm-rrrr')) + 1);</code> SQL Query 2: <code>select level,to...

How to decide to create index on column

AskTom - Hot Questions - Mon, 03/11/2019 - 00:00
Dear, I talk about single table contain approx 20 to 25 columns. And in production database this table used with different column combination or single column also used in different where clause at different query. When some time query run slow s...