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: 54 min 59 sec ago


Wed, 11/13/2019 - 00:00
Hello Connor & Chris :-), A few days ago I found this fantastic SQL script checking Tablespace Free Space being written by Tom Kyte - <b><i></i></b>. So I spent some time formatting and aligning it (BTW c...

Local temporary tablespace

Tue, 11/12/2019 - 00:00
Since Oracle 12.2, Oracle has introduced a new feature of local temporary tablespace. The documentation does not provide any information on the pros and cons of this new feature. Can you please provide some information on why this feature was introdu...

Generating DDL in Oracle SQL Developer

Fri, 11/08/2019 - 00:00
Dear AskTom-Team! Is there a possibility in the Oracle Developer to suppress duplicate DDL code? For example when generating the DDL from my relational model the foreign key constraint is generated twice, i.e. for both tables that are involved in th...

Constraining JSON Data

Wed, 10/23/2019 - 00:00
Hello AskTom-Team, is there a possibility to contrain and check JSON data? For example: <code>insert into test(questionnaire_id, var_val, year) '{ "f1": "2571", "f11": "38124", "f31": "332.64", "f4...

Move Basic LOB to SecureFile LOB

Wed, 10/23/2019 - 00:00
Hello, I have table with 30M rows which has BLOB column. BLOB column has about 700GB of data. I have tried dbms_redefinition package and DBMS_REDEFINITION.start_redef_table failed after 3 days due to ORA-1555. I have increased undo_retention to 600...

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [1384], [1270], [2885], [], [], [], [], [], [], []

Wed, 10/23/2019 - 00:00
------------------------------------- Oracle Database Archive Log Mode RMAN Backup is performed daily, with backup control file and backup archive log file 1 time to disk. ------------------------------------ Hi Ask Tom t...

SQL to return 12 months of this year

Wed, 10/23/2019 - 00:00
select (to_char(add_months (sysdate,level-10),'Month')) as Month ,to_char(TRUNC(add_months(sysdate,level-10),'month'),'mm/dd/yyyy') as firstdayofthemonth ,to_char(last_day(add_months(sysdate,level-10)),'mm/dd/yyyy') as lastdayofmonth from dual ...


Tue, 10/22/2019 - 00:00
Hello Masters, I am testing the two packages DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR and they work fine : for exemple I can remove an ORDER BY from a SELECT. But, there is always an exception, I have problems with Hints : I want to remove...

XML Aggregation

Tue, 10/22/2019 - 00:00
Consider: <code>with data as (select 'MH' initials, to_date('01092019','ddmmyyyy') cal_date, 23 quantity from dual union all select 'MH' initials, to_date('02092019','ddmmyyyy') cal_date, 18 quantity fro...

Oracle query running slow

Fri, 10/11/2019 - 00:00
Hi Team, we have a SQL query which is a source query for the ETL load job, this take around 3 hours to run, could you please help us how we can make it run faster. The row count of the tables involved are as follows. D_PERSON 4618595 ...

alert logs are not generating

Fri, 10/04/2019 - 00:00
I have check one production 12 DB environment. On this environment, there is not alert_<SID>.log files. Is the alert log file should there or missing?? if missing can we re-generate ??, So, it is difficult to monitoring the DC-Dr sync alerts and othe...

Stat gather impact on production environment

Fri, 10/04/2019 - 00:00
On OLTP production environment, during huge transaction period, what is an impact if we run the stat gather of used schema for transaction???, It will missed any indexes, and other operation issues???

Best practice for "archiving" legacy tables and their data

Fri, 10/04/2019 - 00:00
Hi, I recently removed the last piece of front-end functionality that relied on a table, and am certain that that table and its data is no longer needed for the application to function. We'll have more similar tables in this situation in the near ...

Export Dump

Thu, 10/03/2019 - 00:00
Hi, I am trying to take a dump of my database instance using expdp data pump utility to a mapped drive which is pointing to remote drive. I have the access to that drive. Both Oracle is started with a user and remote desktop is also logged in w...

Where is SQL Developer tool in Oracle Database 19c

Sun, 09/29/2019 - 00:00
Dear sir ! I download Oracle Database 19c with file WINDOWS.X64_193000_db_home. After installing, i dont see SQL developer tool for manipulate (operate) database. Before i use Oracle Database 18c, i have seen this tool on menu Thanks for all co...

Event Based Job is not working

Fri, 09/27/2019 - 00:00
Hi, I am struggling several days with following issue. I am trying to implement event based job. At start all was working fine. But after several payload type modifications + several times recreated queue + recreated scheduled job ... schedu...

V$Active_Session_History view in Oracle 12CR2

Thu, 09/26/2019 - 00:00
Hi, The column TM_DELTA_TIME shows null value for some sessions even though the session is WAITING or onCPU when I query the view v$active_Session_history in oracle 12CR2.What does it indicate and When is the column NULL?Can TM_DELTA_TIME be more th...

Restrict Application access to developers in same workspace

Wed, 09/25/2019 - 00:00
How to show a user only certain amount of applications in app builder(i.e user should not be able to see all applications in app builder)? In a Oracle Apex workspace, I need to create a new user(admin role) such that the new user can only see sele...

Is there a maximum number of schemas that can be included in a datapump par file?

Wed, 09/25/2019 - 00:00
I've been tasked with migrating a very large warehouse database (9TB) from hardware in one data center to new hardware in a different data center. For various reasons, the method I've selected for the migration is datapump. I'm breaking up the data...

Pivot with list of rows

Tue, 09/24/2019 - 00:00
We have a table which contains db_name and usernames. In the output we need list of users per DB i.e. number of columns will be equal to distinct db_name. sample output format: <b>DB1 DB2</b> USER1 USER4 USER2 USER5 USER3 Database version:...