Tom Kyte

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

Analytic Functions Drawbacks

Tue, 2020-03-31 04:06
Hi Tom, Analytic functions are really cool, and I can see great benefits from using it. Despite its benefits, does Analytics Functions has any drawbacks, like performance issues, or any details one should be aware of when choosing to use analytics...
Categories: DBA Blogs

500 internal server error

Tue, 2020-03-31 04:06
We have an application in Oracle APEX where we do not have a large database and the number of pages used in the application would be max 250. However, there is a 500 Internal Server error that we are encountering since the last 3-4 days and we have n...
Categories: DBA Blogs

What happens when there is no space in 1 diskgroup in ASM?

Tue, 2020-03-31 04:06
Hi, We have DB that is using ASM with normal redundancy. The space consumption is really high. There was an occasion when almost 1 TB space was consumed in a month. We have been adding disks when there the remaining space gets really low. Now, we ...
Categories: DBA Blogs

OGG-02030 Failed to set logmining server parameters back to default val

Tue, 2020-03-31 04:06
I`ve try to start integrated Extract: <code>REGISTER EXTRACT EXTR_R DATABASE ADD EXTRACT EXTR_R, INTEGRATED TRANLOG, begin now ADD EXTTRAIL ./dirdat/lt, EXTRACT EXTR_R START EXTRACT EXTR_R</code> with param file: <code>Extract EXTR_R USERIDA...
Categories: DBA Blogs

Uploading files in a Rest webservice using PlSql UTL_HTTP

Tue, 2020-03-31 04:06
Hi, I'm calling REST web services using PLSQL UTL_HTTP package, in JSON, with no problems. However, now i need to invoke a WS but attaching a file. I can do it with Postman in ?form-data? mode. In PLSQL, I've tried the same thing in the...
Categories: DBA Blogs

Distributed and in-doubt transactions

Tue, 2020-03-31 04:06
Hello Ask Tom, Team. I am struggling with distributed transactions. Oracle RAC 18c (18.6) and .NET app. I want to know a few things: The RECO background process of an Oracle Database instance automatically resolves failures involving distri...
Categories: DBA Blogs

Paginated queries

Mon, 2020-03-30 09:46
Bello, Ask Tom Team. I need to build a report that connects to a 18c Oracle database. There are big tables and I need to paginate the results. I want to show 50 rows per page in the report. I have read about Fetch, Offset features since Ora...
Categories: DBA Blogs

Index Rebuild for just 1 partition

Mon, 2020-03-30 09:46
Hi, We have partitions in our table. We want to build index for just one partition. If we do that, then does it affects the application? One time we got an error like index in unusable state. First we create index in unusable state like: <code>C...
Categories: DBA Blogs

No data found extracting information from xml

Mon, 2020-03-30 09:46
Why can't you find data in an XML when I have proof of its existence?
Categories: DBA Blogs

Combine create and insert with if-then or case-when in sql?

Mon, 2020-03-30 09:46
Hello people, I would like to ask for your help with a simple issue. I am srry if this is too common, but for me it is not easy at all: I have a table like this - p.sometable: <code> ID AMOUNT YEAR cusomter1 42476281.61 ...
Categories: DBA Blogs

UTL_FILE write and User permission in Unix directory

Mon, 2020-03-30 09:46
I am using UTL_FILE.FOPEN('Unix_Directory_path','FileName.txt','w') in my Pl/Sql package and the file is getting written successfully but the User and permission are different than the file generated using SPOOL, both are using the same batch file in...
Categories: DBA Blogs

ADD_MONTHS Function returning last day of the month

Mon, 2020-03-30 09:46
The question I have is regarding the ADD_MONTHS function. The live sql link for the test case is as below:- https://livesql.oracle.com/apex/livesql/s/JT1FDGOHXMRWN3LG94GEYLMPJ If we have a look at this link it has 3 components:- 1. A function ...
Categories: DBA Blogs

Ethiopian calendar support

Mon, 2020-03-30 09:46
In oracle databse 12c it says it suppirt ethiopian calander.in my database I have one employee table in that I have one column hiredate which date data type until i want to insert ethiopian calander date type data how it is possible?
Categories: DBA Blogs

Pass string values to stored procedure

Sat, 2020-03-28 02:46
Hello, Ask Tom Team. I have several DELETE statements and I want them all within a stored procedure to simplify execution. All these statements would use different string values in the where condition. DELETE FROM user1.table4 t4 WHERE t4.id...
Categories: DBA Blogs

Tuning query with function calls in select

Sat, 2020-03-28 02:46
I have 7M rows from legacy system. <code> insert /*+ append */ into TGT_DATA ( LST_NAM, FRST_NAM, MDL_NAM ) SELECT /*+ parallel(10)*/ func_text_clean(LST_NAM), func_text_clean( FRST_NAM), func_text_clean( MDL_NAM) FROM P_ONST; </...
Categories: DBA Blogs

Proxy user info

Fri, 2020-03-27 08:26
Where in v$ views is hiding info about PROXY_USERNAME ? I'am aware of little piece of information in v$session_connect_info and dbproxy_username column in unified_audit_trail. Most appropriate place will be in v$session, but i didn't find it. R...
Categories: DBA Blogs

Band Joins

Fri, 2020-03-27 08:26
Team, Was reading about Band join from the below link <u>https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/joins.html#GUID-24F34188-110F-4245-9DE7-43954092AFE0</u> <u>https://jonathanlewis.wordpress.com/2017/02/13/band-j...
Categories: DBA Blogs

Data Pump API to refresh top 10 partitions

Thu, 2020-03-26 14:06
I have a requirement where I have to refresh tables from PROD to DEV. But refresh needs to be done in such a manner that partitioned table should be refreshed with its latest top 10 partitions and non partitioned table should be refreshed full I a...
Categories: DBA Blogs

Grouping same value in different groups

Thu, 2020-03-26 14:06
Database: Oracle Database 12c Release 12.2.0.1.0 Following is my test case script: <code>create table test ( id number(1), sdate date, tdate date, prnt_id number(1) ); i...
Categories: DBA Blogs

Submitting PL/SQL procedure execution request via REST

Thu, 2020-03-26 14:06
I need to click a URL on GUI page. Once clicked, respective PL/SQL procedure execution request should be submitted to respective database in form of query API request- REST API. Once procedure is executed in database, status message (success or faile...
Categories: DBA Blogs

Pages