Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 17 hours 15 min ago

Backup and Restore records from DB Objects (Table)

Sun, 2018-06-24 15:26
Wanted to check if there is some easy way to create a backup of the tables/schema and use some script that will load back the data from the backup file. I have DBCS access so can run the sh script as well. Please let me know if there is a simpl...
Categories: DBA Blogs

Spot the diffs between two database schemas inside SQL and PL/SQL code

Sun, 2018-06-24 15:26
Hi : I have two Oracle databases (say, PROD and TEST), and in a given schema (present in both DBs) I must assure that the same SQL code (inside views) and/or PL/SQL code (triggers, procs, funcs, packages) exists, disconsidering the non-functional dif...
Categories: DBA Blogs

Making API calls from Oracle database

Sun, 2018-06-24 15:26
In our office, Environment 1: we have an oracle database with Oracle APEX installed. Environment 2: We have a PCI complaint application with some NON-PCI APIs exposed via Kong We want to call the Non-PCI APIs from oracle database and were to...
Categories: DBA Blogs

Cancelling long running queries

Sun, 2018-06-24 15:26
Dear Tom, I'm wondering about how a 'cancel' in Oracle works. For example, I have a long running query and I define a timeout on application level. Once the timeout is reached, the application sends a 'cancel'. What i observed is, that the canc...
Categories: DBA Blogs

materialized view problem while refreshing

Sun, 2018-06-24 15:26
Hi We have have an ORACLE 8.1.7 database on suse linux 7.2 and we have a materialized view with joins and created a primary key constraint on the mview. The refresh mode and refresh type of the created mview is refresh fast on demand. ...
Categories: DBA Blogs

converting TIMESTAMP(6) to TIMESTAMP(0)

Fri, 2018-06-22 08:26
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...
Categories: DBA Blogs

Mail Restrictions using UTL_SMTP

Fri, 2018-06-22 08:26
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...
Categories: DBA Blogs

DBA_HIST_SQLSTAT and GV$SQL

Wed, 2018-06-20 19:46
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...
Categories: DBA Blogs

Need help in formulating query to fetch previous quote times

Wed, 2018-06-20 19:46
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...
Categories: DBA Blogs

Partitioned table cleanup

Wed, 2018-06-20 19:46
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...
Categories: DBA Blogs

how to reset a sequence

Wed, 2018-06-20 01:26
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.
Categories: DBA Blogs

Log of switchover/failover/open

Wed, 2018-06-20 01:26
What data dictionary view can be used to determine the number of times that a switchover/failover/open has occurred for a standby database?
Categories: DBA Blogs

connection pooling

Wed, 2018-06-20 01:26
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...
Categories: DBA Blogs

How to detect if insert transactions in oracle db are really slow?

Tue, 2018-06-19 07:06
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...
Categories: DBA Blogs

How i can optimize this operation DELETE if the values ares setted in codehard.

Tue, 2018-06-19 07:06
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...
Categories: DBA Blogs

problem of inserting a long string of characters

Tue, 2018-06-19 07:06
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...
Categories: DBA Blogs

Use RESULT_CACHE in subqueries

Tue, 2018-06-19 07:06
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...
Categories: DBA Blogs

database links

Tue, 2018-06-19 07:06
how can i create database links to access remote databases. please tell me the procedure of creating database links.
Categories: DBA Blogs

How to avoid repeated function call for multiple columns' values.

Tue, 2018-06-19 07:06
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 ...
Categories: DBA Blogs

PL/SQL Procedure - Catching "ORA - 01013 - User Requested Cancel of Current Operation"

Mon, 2018-06-18 12:46
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...
Categories: DBA Blogs

Pages