Tom Kyte

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

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

v_x$kglob query takes longer time

Thu, 2020-03-26 14:06
Following query was taking longer time. <b>1)</b> We try to make markhot with following SQL <code>SYS.DBMS_SHARED_POOL.MARKHOT(hash=>e130457a4520f54c18acb0131777d76d,namespace=>0);</code> <b>ERROR at line 1: ORA-06550: line 1, column 42: ...
Categories: DBA Blogs

parallel database systems store

Thu, 2020-03-26 14:06
I am recently studying database and face such a question: Large-scale parallel database systems store an extra copy of each data item on disks attached to a different processor, to avoid loss of data if one of the processors fails. a. Instead of ...
Categories: DBA Blogs

ora-12954 on oracle xe 18c with less than 12gb user data

Thu, 2020-03-26 14:06
Dear Tom, about two weeks ago we moved a test database from 12.2.0.1 SE2 to 18c XE via data pump. We used schema import into a pdb with compression enabled (from the parameter file: TRANSFORM=TABLE_COMPRESSION_CLAUSE:"ROW STORE COMPRESS ADVANCED")...
Categories: DBA Blogs

Gaining low level and in depth understanding on switching through data base resource manager

Thu, 2020-03-26 14:06
I read that one way to switch the load plan is through scheduler. I guess scheduler will switch to a plan by taking switching decision based on time. It will not take switching decision based on need and availability of resources among various consum...
Categories: DBA Blogs

Guarantee a limit for total amount of resources used by all the sessions of users in a particular user group

Thu, 2020-03-26 14:06
Can we guarantee a limit for total amount of resources used by all the sessions of users in a particular user group so their total usage does not exceed 10% of CPU, 10% of IO, 10% of Memory? Can we ensure that a user group follows different resou...
Categories: DBA Blogs

Datapump schema export from max_string_size Standard, import to max_string_size Extended => ORA-01450: maximum key length (6398) exceeded

Wed, 2020-03-25 19:46
Hi, Team of Oracle Masters I have a schema in a source database version 12.1.0.2 with charset AL32UTF8 and max_string_size Standard. Some tables have columns VARCHAR2(2000 CHAR), which in principle could mean up to 8000 bytes, but here the max is...
Categories: DBA Blogs

ROWTYPE declaration throws ORA-16000 error in standby database

Wed, 2020-03-25 01:26
Hi Tom, Could you please clarify, why I get the following error in standby database. <code> Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select log_mode, open_mode, database_role from v$da...
Categories: DBA Blogs

equality predicate on clob

Wed, 2020-03-25 01:26
I've looked into 9i clob manual (wow, whole manual for one built-in datatype!) and still have a question: What index technique can be used to make effective clob comparison in a query like this: select * from my_table where clob_column = another_c...
Categories: DBA Blogs

Find all free time in student course schedule

Tue, 2020-03-24 07:06
I have been asked to provide a list of the times a student is not in class by day of the week. The tables involved look like this: sfrstcr - this table contains the term code (SFRSTCR_TERM_CODE) and course number (SFRSTCR_CRN) that the student ...
Categories: DBA Blogs

many to many joins

Tue, 2020-03-24 07:06
Hi there, Firstly, Thank you to you all for an amazing service you provide here. Now onto business, I am a complete novice at understanding the ins and out of Oracle, though I have been dabbling with it for years. I am currently working on...
Categories: DBA Blogs

Datapump from a pluggable database using an externally authenticated account ( ops$oracle ) prompts for a password

Tue, 2020-03-24 07:06
This is super weird. When using the following command from a pluggable database : expdp / dumpfile=ssss.dmp ( TWO_TASK is set obviously ) I am then prompted for a password. However - this is bogus - I simply press 'enter' and the export...
Categories: DBA Blogs

PGA Vs SGA in Oracle Based Data warehouse/OLAP

Tue, 2020-03-24 07:06
Firstly and simply what is PGA and SGA in context of Oracle database? What are role of PGA and SGA and how stuff works? Is it true that data warehouse need more PGA and less SGA? If true then what is rationale behind it?
Categories: DBA Blogs

Creating Remote Procedure

Tue, 2020-03-24 07:06
Hi Tom, We are able to execute remote procedures, but we are not able to create them remotely. Example: 12:59:54 vdr@rdmdev02 > create or replace procedure c2a.r@rmsdev06 2 as 3 begin 4 dbms_output.put_line( 'hello' ); ...
Categories: DBA Blogs

Pages