Questions

Hello, Ask TOM Team.Scenario:1. We have to deploy a feature in one app. The change consists of avoiding duplicates (mix of three database columns).2. In the database site I have to add one column to a table (200+ millions) and then update it so the app can val...

Hi,I will be starting my development for an Archiving project. Aim is to move all the old data from Production database and make it lighter. It consist of 2 parts. One is to move all data except the latest 6 months into an Archiving database in Oracle from PRO...

hi tomi have a databse with 125 tables. i can find the total number of tables of the database by the sql select * from tab; now i would like to know the number of rows in each table of my database with out executing select count(*) from...; each time.please...

We have a UI based application where users come in and setup SQL's to get excel based reports back, there are multiple options to chose on the schedule of the SQL execution and other related parameters. It's a free text box, the expectation is that the users w...

Hi Tom.I'm a great believer that a backup you haven't tested isn't a backup, so I'm trying to ensure that our RMAN backups get automatically verified, however I've come across some weird behaviour with rman backup validation.My aim is to run full verification ...

Hi Tom,My first time asking a question here. Am querying a historical table which not only has START_DATE and END_DATE, it also has CURRENT_FLAG where CURRENT_FLAG = 1 corresponds to END_DATE is null.I assume that the CURRENT_FLAG column (all 0s and a 1) woul...

dbms_output.put_line has limitation of 255 characters per line. How can I get out of this limitation, so that I can use dbms_output.put_line to have 480 character per line ...

Hi TOM,Sorry for asking this basic question (After two hours of searching, I am unable to find the answer)Let's say I have run opatch in an ora19 home to patch the binaries.But then I "forgot" to run datapatch.Is there any dictionary view to tell me that I "fo...

HiI have a query that is spooling results to a text file via an automated process Monday to Friday. Below are the variables that are used in the query. The queries returns data in Oracle SQL Developer, but returns only the headers with no data when I use Toa...

I have an issue with the 19c data pump export and I'm wondering if there is a data pump export parameter or some other strategy for resolving it.When a table's primary key is created as part of the create table statement, a unique index is automatically create...

In my data management class, we've started talking about OLTP vs. OLAP and the access patterns of each. The main points are that OLTP requires high performance, as these are the systems that our users interact with, and there's often a large concurrent number ...

Hello Experts,For an upcoming audit requirement, am working on a query. I am facing performance issues in getting the data. In one table gl_import_references the transactional references are stored in reference columns. There is a index in this table for colum...

Hi. What is the technical reason why the package DBMS_DEBUG_JDWP is not available on the Oracle Autonomous Database? What does it do that makes calling it illegal in PL/SQL?Thanks,...

Hi,I am working on a Archiving application. Aim is to bring year and years of data from Production DB into another DB named as Archiving DB and then delete from the Production DB. Currently I am testing deletion on an Archiving DB to test my package performanc...

Why Do /*+ materialize */ Hints Generate Redo?What modifications are materialized subquery factoring clauses making that is needing to be protected by redo?Session A executes a SELECT statement six times. The first three executions are without the /*+ materia...

Hi there, masters.I'm facing a rather weird situation regading the TSTZ configuration. A full detailed description of my initial problem is here in this post I made some months ago: https://community.oracle.com/tech/developers/discussion/4490678/ora-39405-sta...

I have a table with two columns that need to be defined as primary keys, order_number and item_number. A table can have only one primary key, but I can define composite primary keys in the table_constraint syntax. How do I define two columns as composite pri...

Dear AskTom Team,first of all thanks for all the support you are giving all of as for some many years.I already read many of your answer about the topic <b>PL/SQL Collection</b> slowing sql execution.I alredy tried all the hints (such for example the <b>cardin...

Hi Tom,I have a question about having an ID column in all tables of an OLTP system. In my application, all tables have ID (Not sure why it was designed that way) column which gets populated by Sequence and this ID column value is not used as a referential key ...

hi tomcan u explain me in detail about "start with connect by" sql statement (tree structure).i know there is documentatin but it is very confusing. ...