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: 2 hours 24 min ago

Please help, can't drop a corrupt table

Wed, 01/09/2019 - 00:00
Hi all, we have a corrupt object named "TO_BE_DROPPED" in our development database. Previously it had a different name, but someone renamed it, because it cannot be deleted, only renamed. It cannot be dropped by any means we could find. Apparently...

Replicating Data to another database in the same server

Mon, 01/07/2019 - 00:00
Hello, Ask Tom Team. Happy New Year for all of you. I have two single instance databases (A and B) in the same server. I need to replicate from A to B (some columns of some tables) in order to customers can view the data through a web applicat...

Alphanumeric Counter

Mon, 01/07/2019 - 00:00
Hi, i would like to know if it is possible to generate a alphanumeric counter like below. A001 A002 . . A999 AA01 AA02 . . AA99 AB01 AB02 . . AB99 AC01 . . . ZZZZ how can we implement this in a function? i have tried couple o...

ORA-01031 in view DML with dual reference

Mon, 01/07/2019 - 00:00
At a customer I work for there is a design standard that each DB-view should start with a so-called 'comments' block. This is to ensure that comments are stored in the data dictionary in the DB. create or replace view xxxx as with comments as ( ...

how to equate substring of one table column to a value in a list from another table column as part of an exists?

Mon, 12/17/2018 - 00:00
Hello Oracle Masters, I was looking at 'connect by level' (https://asktom.oracle.com/pls/apex/asktom.search?tag=clever-sql) hoping I could make use of it, but don't seem to be able to. Perhaps there is another way? We have a table of products ...

Database Design for Invoices Table with Many Null Columns

Mon, 12/17/2018 - 00:00
Hello, Ask Tom Team. I have a 70 columns table storing valid invoices. There are cases where the invoices are rejected because business rules. When the invoices are rejected just a few columns are filled (up to 5), then all 65 remaining columns ar...

Perf : Insert into Select Vs For All

Mon, 12/17/2018 - 00:00
I need to perform db operations where I need to inserts .5 million reocrds from another complex query involving joins,exists etc: As of now I am using INSERT INTO SELECT inside a pl/sq package. but some time I am observing for same data set t...

How to compare date duration of first row with second and so on.. and convert it to rows to column with indicators

Thu, 12/13/2018 - 00:00
Hi Tom, I have below table <code>CREATE TABLE PRDCT (ID NUMBER(10,0), PRDCT_CD VARCHAR2(1), START_DT DATE, END_DT DATE);</code> With data in this like - <code>Insert Into PRDCT Values (1,'A',to_date('01-May-2017'),to_date('31-Jul-2017')...

Packages are invalid -- ORA-04061

Wed, 12/12/2018 - 00:00
Hi, In our DB, few packages became invalid. And when we verified it we saw that there are no errors related to them. I was expecting sessions to run this package without any error, however when it was executed for first time, we got ORA-04061 erro...

SQL Window function to skip and add rows dynamically

Wed, 12/12/2018 - 00:00
<code>create table test1 (col1 number,col2 varchar2(5),col3 date,col4 number,col5 number,col6 number); --Positive scenario sample data insert into test1 values(1,'A',trunc(sysdate),0,5.5,2); insert into test1 values(1,'A',trunc(sysdate)+...

Identify batch number for given dates.

Wed, 11/28/2018 - 00:00
Hi, I need a sql query to calculate batch_number based on the dates. For the same name and country we need to show same batch number up to 12 months. For next batch we have to start from that date and consider upto 12 months as next batch. Her...

What should a PL/SQL Developer should learn in 2019

Wed, 11/28/2018 - 00:00
Hi Tom, I worked for around 14 years as Oracle developer, over SQL, PL/SQL, Forms Reports and APEX. Now since 2018 we are observing major changes like people are using Rest API rather direct backend coding. So what should a traditional Oracle ...

How can I show result sets like this?

Wed, 11/28/2018 - 00:00
Hi Tom, I have two tables. One is test and another is test1. Each table has two columns, record_id and c1 and both are varchar2(10). Test content is below <code>record_id c1 ----------------- 1 null 2 3 3 + 4 ...

Joining date and timestamp partitioned tables but partition pruning seems to not to work

Wed, 11/28/2018 - 00:00
Hi We have date and timestamp partitioned tables. While joining them partition pruning seems to not to work. Is there any way to do this? <code> create table T_A( ORDERED DATE not null, C1 number(20) not null ) partition ...

oracle 11g place limits on memory to session/user

Tue, 11/27/2018 - 00:00
Hello ! Here is my question : Is it possible to allow a minmum amount of memory to a specific session or a user on Oracle 11g? If it is, could anyone, please, explain it to me ? :) My fundamental need is as follow : I have two applications th...

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 and EXPDP/Historical Data

Fri, 11/09/2018 - 00:00
Hello, Ask TOM Team. We are designing a new database and there are lots of tables with identity column. The sequence used by each table is a named sequence (we are not using the system-generated sequence). The default values of these tables is lik...

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

Pages