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 6 min ago

Ora-12560: TNS: protocol adapter error

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

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

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

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.

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

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.

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

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

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

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)

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

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 ?

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

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...

Number of Chunks in DBMS_PARALLEL_EXECUTE

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

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

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

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...

Weighted Average Inventory calculation.

Wed, 03/06/2019 - 00:00
Hello, Hope you are at the best of you health. I am working in Microsoft Windows environment and installed database is Oracle 11gR2. I have following two table with sample data. <code>CREATE TABLE stock_master ( vno INTEGER, vdate DATE, vtype...

Index skip scan with high NDV leading column

Wed, 03/06/2019 - 00:00
Hello Tom, I have the situation: A table tbl (220 Mio recs) the following query <code>select col_a, col_b, col_c from tbl t where Mod(Dbms_Rowid.Rowid_Row_Number(t.rowid, 2) = 0 --pred 1 and col_d =<lit>; --pred 2</code> col_d is in...

Pages