Tom Kyte

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

How to get current schema

Fri, 2020-07-03 02:06
I used alter "session set current_schema = xx" to change schema in a block of code. It is not working and I suspect that the schema has not been changed for some reason. How can I query the system to return the current schema I am in? Is there a p...
Categories: DBA Blogs

Week of Year in SQL - Confusing

Fri, 2020-07-03 02:06
Hi ASK TOM Team! I am a bit confused with the iw option from to_char function in SQL. I'm using version 9i and was looking for function to find the week of the year. I've noticed that the week of year for 01-Jan-2016 is 53 which I expected to be ...
Categories: DBA Blogs

Global variables vs parameters

Fri, 2020-07-03 02:06
Hi Tom, could you explain advantages and disadvantages of using global variables and parameters in Forms in terms of efficiency of application and system resourses. What would you recommend for passing values between blocks and program units in...
Categories: DBA Blogs

Not being utilized all the processors capacity

Wed, 2020-07-01 13:26
Dear sir, Thank you for your support I have Oracle 11g Database server which has 2 dual core processors. It is very critical database and very frequently we are getting performance issues. We noticed that the CPU utilization is not crossing 5...
Categories: DBA Blogs

Understanding purpose of undo for Global Temporary Tables DML operation

Wed, 2020-07-01 13:26
Hi TOM, I know Global Temporary Table (GTT) will generate undo data while any DML operations performed on it within that session before commit. But I want to understand why undo is needed for DML operations on GTT. Is that to support following kind ...
Categories: DBA Blogs

why dba_extents has no answer but dba_segments has results

Tue, 2020-06-30 19:06
I have a test environment which has oracle 10.2.0.1 installed, today I want to resize a tablespace named ANNEX which has a size:347921216KB because there is no usable data in it.the usage of the datafile Corresponding this tablespace is 0.7%, ...
Categories: DBA Blogs

Interactive Report pagination automatically stick/unstick to the bottom

Tue, 2020-06-30 00:46
Hi, Is there any way to make the interactive report pagination automatically stick and unstick to bottom of page like interactive grid does? I tried the following but it didn't work: <code>$('.a-IRR-paginationWrap--bottom').stickyWidget({ togg...
Categories: DBA Blogs

RMAN backup on Azure Blob Storage

Tue, 2020-06-30 00:46
When I deploy Oracle Database on Amazon EC2, I can backup to Object Store with RMAN, using "Oracle Secure Backup Cloud Module for Amazon S3". This is great. When I deploy Oracle Database on Azure, I would like to use Azure Blob Storage with RMA...
Categories: DBA Blogs

db_16k_cache_size, db_block_size parameter setting

Mon, 2020-06-29 06:26
This is about the db_block_size, db_16k_cache_size parameters. Oracle document says, Statement 1: DB_nK_CACHE_SIZE (where n = 2, 4, 8, 16, 32) specifies the size of the cache for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE...
Categories: DBA Blogs

Foreign Keys

Fri, 2020-06-26 23:46
Hi Tom, this question was put to me by a developer/designer and as I couldn't a) Find the Answer or b) make up a convincing answer I thought I would ask you. Basically it is .... HOW does Oracle make the changes to a child table when ...
Categories: DBA Blogs

OWA_UTIL.GET_CGI_ENV Unavailable

Fri, 2020-06-26 05:26
Hello everyone, I've tried to scour the internet and can't find the answer to this anywhere else. I have the use case where I'd like to be able to see the IP_ADDRESS of a user requesting data from an RESTful Service endpoint. Upon my testing, the ...
Categories: DBA Blogs

"ORA-03150: end-of-file on communication channel for database link ORA-02063: preceding line from SAP_INCR_LINK"

Fri, 2020-06-26 05:26
DB Link is failing with some connection issue when procedure is running.
Categories: DBA Blogs

Remote link "insert into select from" Gets Errors

Fri, 2020-06-26 05:26
I am trying to insert data from an Oracle 12c DB into an Oracle 11g DB by remote link. The remote link works fine for selects and normal DML operations, single inserts also work fine. When I use: insert into TableA@RemoteLink (col1,col2,col3) sel...
Categories: DBA Blogs

Inserting N rows from a cartesian

Thu, 2020-06-25 11:06
I have the following setup (test case below). How can the query be modified to INSERT N number of rows per employee_id. I want to add something like this but can't figure how to implement this for my test case ( select level dbms_random.value(5...
Categories: DBA Blogs

Index data file

Thu, 2020-06-25 11:06
Hi Tom, How can I find out which data file the index is stored in? What SQL statement can I use to do it?
Categories: DBA Blogs

READ ONLY Database

Thu, 2020-06-25 11:06
Hi Tom, Once I open the Database using ALTER DATABASE OPEN READ ONLY, how can I change the status to READ WRITE?? Can a Database that is in Read-Write mode to be changed to Read-Only and Vis-Versa?? Thanks and I appreciate your help.. Kan...
Categories: DBA Blogs

maximum length of sql statement

Thu, 2020-06-25 11:06
what is the maximum length of an sql statement you have seen so far ? (just for curiosity) as far as I know, in Oracle8 there was a limit of 64k allowed, in Oracle10 there is no limit at all ? (didn't find something in reference)
Categories: DBA Blogs

Refresh on Materialized View

Thu, 2020-06-25 11:06
Dear Tom I create materialized view like this : create materialized view dtl_budget_mv build immediate refresh fast enable query rewrite as select a.company, a.nu_budget_year, a.nu_version, b.nu_month, b.vc_stock_code, sum(b.nu_quantity...
Categories: DBA Blogs

Procedure overloading by different parameter names

Thu, 2020-06-25 11:06
Hello Tom. I have a question about procedure/function overloading in Oracle. I knew that we can overload a procedure using different types or different number of parameters. To my surprise I found we can also overload with different parameter na...
Categories: DBA Blogs

Not able to insert data in some tables. Unable to connect some times

Wed, 2020-06-24 16:46
I have 11G_XE installed in windows 10 64bit Operating system. When I See alert.log of the database, the following messages are repeatedly updating the file. The issues of the database like Procedures are hanging and not being completed, not able t...
Categories: DBA Blogs

Pages