Tom Kyte

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

Change all procedures in a schema

7 hours 43 min ago
Is it possible to change a string to another string in all procedures in a schema? Use case: We have a function, say F_A. We want to place it within a package, say PG_B. Now we would like to change all calls to F_A in all procedures with PG_B.F...
Categories: DBA Blogs

Privilege required to execute a stored procedure

7 hours 43 min ago
Hi, There is a procedure named 'Update_emp_details' in our database which actually update the employees table present in the same database. I needs to give execute permission for a another user to this procedure 'Update_emp_details'. Is giving...
Categories: DBA Blogs

Locking issues on Table A with FKs on table X while a procedure inserts records in table B with FKs on table X too

7 hours 43 min ago
Hello, we use oracle 11.2.0.3 and following topic i would like to discuss here: Setup like this: Table A (partitioned) with an article column and FK defined to masterdata (article) keytable X Table B also with article column and FK define...
Categories: DBA Blogs

Spool file hide SQL statement

7 hours 43 min ago
I would like to know what Statement makes it, that the the SQL Statement is not printet in the spool file? The column names still should be printed. I'm calling the script from the SQL developer. I have the following script: <code>set CO...
Categories: DBA Blogs

Unused indexes Identification

7 hours 43 min ago
Hello Tom, i was looking for options available in oracle 12c by which i can find out Unused indexes in database. I have used v$object_usage. but it only gives me index is used? it does not display how many times it has been used. also, if i would ...
Categories: DBA Blogs

Grant Compile Packaged to Other User

7 hours 43 min ago
<b>Hi, I have a package in schema A. I want schema B to be able to compile the code for just this package and not any other packages that schema A owns. Can this be achieved? </b>
Categories: DBA Blogs

Self full outer join with additional predicateds

7 hours 43 min ago
Hi Tom, I was asked to create a query, counting the number of the columns with different names in 2 tables. Say in "Employees" and "Departments" we have DEPARTMENT_ID and MANAGER_ID columns with equal names, and the rest of the columns differs in na...
Categories: DBA Blogs

rouning inconsistency in oracle db

7 hours 43 min ago
Hi Tom, We use the below rounding logic in our applications but unfortunately the first query was rounding incorrectly. This we can see only when this code was executed from the application. But when I execute it in sql*plus it giving me correct o...
Categories: DBA Blogs

Procedure accepting more than 32K text as a parameter and inserting to CLOB coumn

Sun, 2017-01-15 16:46
Hi Team, We have one requirement where: 1) we have to write one proceudre which will insert into a clob column meant for email body. This procedure should accept text more than 32K. 2) the application or routine will execute this procedure by p...
Categories: DBA Blogs

Space required in UNDO tablespace for adding a new column with Default value not null.

Sun, 2017-01-15 16:46
Hi Tom, Recently we needed to add a new field in a table with a default value and a not null constraint, since the table was 45 million rows and 7GB, we ran out of space in UNDO tablespace. In order to avoid the issue in the future, we did some...
Categories: DBA Blogs

Hybridized Database after an upgrade of a retail Point of Sale application.

Sun, 2017-01-15 16:46
Background: Point of Sale product running on retail stores. Each store has a store primary database and there is a centralized database. The data from the stores get replicated real time to the central Database. The Front end application and the DB i...
Categories: DBA Blogs

Migrate a Oracle Database from a client server to an internal server through an intermediate server like EFT

Sun, 2017-01-15 16:46
Hello. I need to copy a customer database from their server to my server. I will get a copy using the Oracle Data Pump. But the client network is different that my network, so I need to use an intermediate server like an EFT. How can I do this? Thank...
Categories: DBA Blogs

Connnect by level

Sun, 2017-01-15 16:46
HI TOM, I need a small short and brief explanation of the below query. create table t as select dbms_random.string('a', 10) y from dual connect by level <= 10; ---Here can i know what does "connect by level" can you explain a bit ...
Categories: DBA Blogs

Oracle Index Internals

Sun, 2017-01-15 16:46
Team, Generally talking about the Indexes, it is considered as an object which assists in fetching the data faster or quickly. But i would like to know the internals of index, like how index is stored and index leaf node,branch node and index r...
Categories: DBA Blogs

Kill Oracle Session when closing client on browser (Forms 11g)

Sun, 2017-01-15 16:46
Hey guys, I am facing a problem, at the company I work for, we've noticed there were >60 active sessions without users "using" it. Then, we've noticed it was being caused because of the way the user was closing the application. The user was clic...
Categories: DBA Blogs

Best way to change the datatype of a column in a Table with 20 million

Sun, 2017-01-15 16:46
Hi Team, Wish you a happy new Year. Welcome to Maria Colgan. I have a requirement to change the datatype of a column of number type to Varchar2(35) and then rename the table. The table has huge data around 20 millions. Since already data is the...
Categories: DBA Blogs

Creating Partitions

Sat, 2017-01-14 03:46
Hi Team, Please have a look at table structure shared via LiveSQL Link - https://livesql.oracle.com/apex/livesql/s/ee1slk0wo53esxy30e4l0ac2x table is used in application using stmt_end_date - 1. Since stmt_end_date is a part of primary key, i...
Categories: DBA Blogs

Find the number in list which is not present in Database column

Sat, 2017-01-14 03:46
I have one requirement where I need to find the first missing number in the List which is not there in Database(Oracle) column. Scenario is like this : Table 1: Link to table image is here :- <code>https://i.stack.imgur.com/iXm6N.png</code>...
Categories: DBA Blogs

Create linenumbers for sqlplus queries on the fly.

Sat, 2017-01-14 03:46
Is there a quick an easy way to create sequential line numbers on the fly (without Creating a Sequence or using PL*SQL) for a SQLplus query executed inside SQLplus? Example: Here is a live link to output: http://www.videofame.com/fragrance.asp ...
Categories: DBA Blogs

Oracle Apex applicationson company DB

Sat, 2017-01-14 03:46
Hi, I am trying to create oracle apex applications for my company,the thing i am worried about is that the applications will be complex and would require a lot of space on the DB.Can you let me know what is the default DB size that is available if...
Categories: DBA Blogs

Pages