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

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

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

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

Converting data types in where clause

Tue, 09/24/2019 - 00:00
Hi Tom, My question is regarding when a query is not having the right datatype in the where clause Example: -- Create table <code>CREATE TABLE mytable ( mynumber varchar2(20), primary key(mynumber));</code> -- Insert some rows <...

Inserting without a full list for field names

Tue, 09/24/2019 - 00:00
We have an issue when we perform an insert like this <code> INSERT INTO STS_RESP_LOG(STS_REQ_LOG_SYSTEM_ID,HSTRY_FLG, SNGL_STS, FRQNCY, CRT_DATE ) VALUES ( ?, ?, ?, ?, sysdate); </code> ?...

confuse at the order of execution plan table

Tue, 09/24/2019 - 00:00
As we were told that "The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is executed first." howe...

md5 vs natural key

Thu, 08/15/2019 - 00:00
Hello, I start a new data warehouse project. In the ETLs for the ins/upd steps, I suggest to use MD5 and not the natural key (my sources are txt files from a mainframe). I need to explain to my project manager why I want to use md5 and not the na...

Generate duplicate rows

Wed, 08/14/2019 - 00:00
Hi Connor / Chris, Could you please have a look ate below scenario and help in building SQL: I need to generate duplicate row based on column value in SELECT SQL to be executed is: <code> select emp.empno, emp.ename, emp.hiredate, emp.sal, e...

Select Performance Too Bad on Oracle

Wed, 08/14/2019 - 00:00
Dear Tom, I just faced an issue that I could not solve it. I have two virtual machines: Client: 32Core, 32G Ram, Virtual Machine on HPE Server Server: 96Core, 256G Ram, Virtual Machine on HPE Server I did write a simple Java application usi...

Truncate Table Command not able to recognize Table in EBR enabled Oracle database

Tue, 08/13/2019 - 00:00
We are using EBR enabled Oracle Database and hence have ability to have same name for a table and View. Following are two objects in FUSION schema: wsh_interface_errors TABLE FUSION WSH_INTERFACE_ERRORS VIEW FUSION Now when trying to run...

Write stored procedure inside a stored procedure

Tue, 08/13/2019 - 00:00
Hi Team Ask TOM, Recently, I came across some code where developers had written four or five stored procedures inside another stored procedure. The code compiles fine. I always thought that to write stored procedures like this, they should be in a p...

Oracle SQL Index Optimization : Index Hint not being used even on forcing

Mon, 08/12/2019 - 00:00
Dear Sir, I have created a table with a composite primary key and few indexes. In one of the SQL the where clause is a combination of Index Column and non-index columns, however the query is not using the index column and instead going for Full Ta...

Script to create dynamic partitions for multiple backup tables

Mon, 08/12/2019 - 00:00
Hi Team, We have a requirement wherein there are 30 backup tables which are currently non partitioned and each table have data around 15-30TB's. Size of the table is very huge. We have to write a script which will create partitioning of a backu...

SQL query using Rollback segment heavily

Thu, 08/08/2019 - 00:00
Dear Team, I am querying from a table where dml operations are continuously taking place in current financial year(19-20). But I am retrieving data of 17-18 financial year's and it was pretty slow. On investigating, I found that it was using rollbac...

TEMP and UNDO dramatic growth, RESET_PACKAGE suspected

Wed, 07/31/2019 - 00:00
Dear Tom, My question may be very basic, however I failed to find answer in the Net. I run Oracle DB 11g XE and Oracle APEX on Linux virtual machine for more than 2 years and all was great. Some 2 weeks ago the server rep...