Feed aggregator

Query both DBA_FREE_SPACE and DBA_TEMP_FREE_SPACE on view

AskTom - Hot Questions - 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>https://asktom.oracle.com/Misc/free.html</i></b>. So I spent some time formatting and aligning it (BTW c...

Local temporary tablespace

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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], [], [], [], [], [], [], []

AskTom - Hot Questions - Wed, 10/23/2019 - 00:00
------------------------------------- Oracle Database 11.2.0.1.0 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

AskTom - Hot Questions - 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 ...

Remove a hint with DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR failed

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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?

AskTom - Hot Questions - 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...

Pages