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

Find all the Fully Connected Subgraphs

Mon, 07/08/2019 - 00:00
Hi Team , <code> with data ( p1 , p2 ) as ( select 'a' ,'b' from dual union all select 'a' ,'c' from dual union all select 'a' ,'d' from dual union all select 'b' ,'c' from dual union all select 'b' ,'d' from dual union all ...

Best debuging steps for PLSQL procedure AND SQL Querys

Fri, 07/05/2019 - 00:00
Hi Team, In my project, we have a 100 of the procedure and those procedures we have 1000 to 5000 lines of codes. we need to debugging those procedures. Is there any best methods, techniques for debugging plsql procedure and SQL queries. this...

Query is taking much time (not using index)

Thu, 07/04/2019 - 00:00
Hello, Ask Tom. I have a delete statement that involves three tables. All id are pk (indexes are present on each pk by default). There is also an index on t2.columnA but in the plan it says that there is a table access full on table2. The query i...

What is efficient way to paginate on the large table?

Sun, 06/30/2019 - 00:00
Hi Tom, We are using the following query in java application, for getting the query results in the pagination. To speed up the task we are executing 15 queries at the same time. The queries are being executed on different machines but targeting th...

Commit / rollback in nested procedures calls

Fri, 06/28/2019 - 00:00
Hi, The situation: Procedure Master and procedure Slave, both include transactions. Procedure Master calls procedure Slave in its body. External code could calls both procedures. The target: When external code call procedure Slave the transac...

WITH Function Returns Results that Do Not Meet Constraint

Sun, 06/23/2019 - 00:00
Hi Tom et al, Code below uses a function declared inside of a WITH clause. Function calls dbms_random and returns the result to my WITH table. Final SELECT queries WITH table using a constraint, but I get results that do not meet the constraint....

Usage analysis of Stored Procs

Thu, 06/13/2019 - 00:00
Hi, Please can you help with tracking of execution of stored procedures in DB. One legacy DB is running, application owner wants to know how many of the SP's are actually used. He is looking for usage report on SP's We have tried to get data...

Substr, to_char gives a difference of 1 hour from actual time

Wed, 06/12/2019 - 00:00
Hello, 1) When we execute below query : <code>with rws as ( (SELECT (FROM_TZ(CAST(sysdate AS TIMESTAMP), 'Europe/Paris') AT TIME ZONE (select nvl(timezone,'Europe/Paris') date1 from dim.nloqt_top_sit_d where sit = 'KE' )) sysd FROM DUAL)...

Purging a huge table

Wed, 06/12/2019 - 00:00
I have a huge table with CLOB and BLOB two columns.The size of the table is 300 GB ( 80 GB Table Size + 220 GB LOB Columns). We have requirement to keep only 3 years of latest data.What would be the best way to achieve this ? My solution : 1. Di...

Value gets overwritten when using application context to be accessed by multiple sessions

Tue, 06/11/2019 - 00:00
Hi Tom! Running on Oracle 11g ( I try to use a global context to share parameters between user sessions. Like the docs says: "username set to a value - client_id set to NULL" means: This combination enables an application context t...

Update primary key column or update other columns in a hierarchy

Sat, 06/08/2019 - 00:00
I hava a table like the following structure: <code>create table table_a ( "ID_TABLE_A" NUMBER, --primary key column "PARENT_ID" NUMBER, "CONTENT_1" VARCHAR2(32), "CONTENT_2" NUMBER, "CONTENT_3" NUMBER, "CONTENT_4" VARCHAR2(256), "CONTENT_5...

Already fetched rows returned when previous fetch stops middle of duplicate values

Fri, 06/07/2019 - 00:00
Dear Team, We have a bit complex SQL including an outer join where we see an issue in preserving row order during multiple fetches. Structure of the SQL is as follows. <i>SELECT <column list> FROM <this includes sub selects & couple of inne...

Synchronizing database sequences during manual data replication

Mon, 06/03/2019 - 00:00
Hi Tom, Experts, I am in a need to replicate manually all objects from one schema to another (manually, because my schemas, both source and target, reside in the cloud and I have no access to any OS level utility, nor sql*net access to the databas...

DDL export from database

Wed, 05/29/2019 - 00:00
How to export only database structure from Oracle database?

anytype from java for anydataset

Wed, 05/22/2019 - 00:00
Hello TOM :) I try to make anytype in java stored procedure and use it to create anydataset in PL/SQL. But I get error ORA-22625. Why this error occures and how can I fix it? I do it according to guides, for example

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

Session level statistics on global temporary tables

Thu, 05/02/2019 - 00:00
I was excited to learn that since 12c we can have session level statistics on global temporary tables. Excellent. However when trying this out, I found there was an implicit commit happening - which wasn't so excellent. So much so - that I raised an ...

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