DBA Blogs

Currently I have a column with datatype TIMESTAMP(6) but now i have a requirement to change it to TIMESTAMP(0). Because we cannot decrease the precision, ORA-30082: datetime/interval column to be modified must be empty to decrease fractional sec...

Hi Tom, I have a requirement to send email to particular domain mail id?s. But My Mail server is global mail server we can send mail to any mail ids. Is there any options in Oracle to restrict the mail send as global. For example: My mail host is...

The Oracle PaaS Summer Camp is a one week training for cutting-edge software consultants, engineers and enterprise-level professionals. The #PaaSSummerCamp brings together the world’s leading...

We share our skills to maximize your revenue!

Hi, I was trying to create a dashboard comparing historical executions and current executions of multiple SQL statements. I have noticed some differences between stats in GV$SQL and DBA_HIST_SQLSTAT. Could you please help us to understand below po...

Hi AskTom Team, I have been a big fan of this site since 1999 around the time it came up. First of all, again a big Thank you for your support to Oracle Community since past two decades. I have immensely benefited from this. This time arou...

Hi I have a table that was created for debugging purposes. Every night a jobs kicks off creating a partition of the days inserts on the table based on date. Needless to say have the partitions grown rapidly and have taken up a lot space in the tab...

Create sequence with no options, and the current value of the sequence is 10. Specify the statements in order to reset the sequence to 8, so that the next value will be generated after 8 is 11. Find out logic.

What data dictionary view can be used to determine the number of times that a switchover/failover/open has occurred for a standby database?

Tom, What is connection pooling ? Please, can you give an example(s) that show thorough understanding of the subject matter as related to either ODBC OR JDBC application connections to the oracle database. Your site is more important and most val...

At work, I have an Oracle DB (11g) in which I want to detect if there's slow performance while inserting data. Here's the situation: Some production devices send data results from tests to Server A, this server is a important server and it replica...

Hi, I'm a bit new to the development of plsql. I would like to know how I could optimize the delete operation with a forall if my query is the following: DELETE FROM SCH.TA_DELETE WHERE FIACUM < 1 AND FIPAIS = 1 AND F...

Hello Team , I'm trying to insert into a table " TEST COM " the result of selecting rows of another table. I used the wm_concat function . /**********/ insert into COMMENTAIRE_TEST (SELECT wm_concat((DBMS_LOB.SUBSTR(COM_TEXTE,400...

Dear Tom, I am thinking to use the new feature "RESULT_CACHE" to optimize some search queries for my paginated pages. So far, for a search page I have : 1.) a count query and 2.) the query that returns a page from the result set Both 1 an...

how can i create database links to access remote databases. please tell me the procedure of creating database links.

Hi I'm refactoring an old procedure that calls a function for determining whether passed in values consist of only characters allowed in the front end app on top of the database. The procedure has a cursor that gathers all records it needs to ...

It may be a silly question but I am wondering if there is any way to catch this error "ORA-01013 - User requested cancel of current operation" in a PL/SQL procedure. The requirement that I have is to update a database record before exiting when t...

<code></code>I have a package with an overloaded function, wich accepts one parameter (INTEGER / VARCHAR2) and returns a booblean indicating if exists a register with the correspondient column having the same value as the parameter. In tests, w...

Hi Tom, i had some discussions with other colleagues about which driver should / could be used for connecting to an oracle database. I searched the web but found no sufficient answer for me (only a pretty old question from this site https://askto...

What's the best way to replace every character in string on random character and every number on other random number. I think about best performance. Input: MatijZ34 Output: sWirpt77

Hello Tom, I have 100 million records to load to a table. I am sqlldr to do so. I have been using the conventional load but is taking long. In the control file I have SQL functions to substring and other data manipulations. My question is, can I use...