Tom Kyte

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

Inject variable value

Fri, 2021-06-11 17:06
Hi guys. I have a piece of code like this: <code> declare myvar number := 4; begin select rule into v_stmt from rules where rule_id=1; --v_stmt -> 'begin :a := my_function(my_var, 1), 1); end;' execute immediate v_stmt; end; / </code> Is there a way to inject my_var value to execute v_stmt?
Categories: DBA Blogs

SQL Loader header row attribute cannot be applied to detail rows in event of multiple header/detail transactions

Fri, 2021-06-11 17:06
How to load using Oracle SQL Loader values to it's related detail rows if the file has multiple headers and details rows. The issue is header row attribute cannot be applied to it's related detail rows in event of multiple header/detail transactions. It does works and load for 1 header, where header loader evaluation is done for very first header and applies to all detail rows, which is not expected to happen. I need to apply each header row attribute value to applied it's child detail rows. Here is the example. Each <b>H - Header</b> Record attribute value <b>1001</b>, <b>1002 </b>and <b>1003</b>, I need to stamp to each respective detail record while loading via SQL Loader. H ABC 1001 D XYZ 89.90 D XYZ 89.91 D XYZ 89.92 H ABC 1002 D XYZ 89.90 D XYZ 89.91 D XYZ 89.92 H ABC 1003 D XYZ 89.90 D XYZ 89.91 D XYZ 89.92 The expected results should be in database table after SQL loader is completed as follows, which does not happen. Any suggestions! H ABC 1001 D XYZ 89.90 1001 D XYZ 89.91 1001 D XYZ 89.92 1001 H ABC 1002 D XYZ 89.90 1002 D XYZ 89.91 1002 D XYZ 89.92 1002 H ABC 1003 D XYZ 89.90 1003 D XYZ 89.91 1003 D XYZ 89.92 1003 Thank you.
Categories: DBA Blogs

Using analytical functions for time period grouping

Thu, 2021-06-10 22:46
Hi Tom, I have a table like below: <code>GRP,SUBGRP,START_Y,END_Y 122,... 123,A,2010,2011 123,A,2011,2012 123,A,2012,2013 123,A,2013,2014 123,B,2014,2015 123,B,2015,2016 123,B,2016,2017 123,A,2017,2018 123,A,2018,2019 123,A,2019,2020 124,...</code> I would like to find start and end of all intervals in this table like so: <code>GRP,SUBGRP,MIN,MAX 122,... 123,A,2010,2014 123,B,2014,2017 123,A,2017,2020 124,...</code> A simple group by would show the results over the complete timeperiod but not over the different intervals: <code>GRP,SUBGRP,MIN,MAX 122,... 123,A,2010,2020 123,B,2014,2017 124,...</code> I think it should be possible with analytic functions but I don't get it.
Categories: DBA Blogs

Specified tablespace for IOT

Thu, 2021-06-10 22:46
I have two tablespaces named USERS and INDX, respectively. The dufault tablespace for current user is USERS. I created an IOT table whose name is tb_zxp. Since no need to specify a tablespace storing data for IOT, I'd like to put the whole index of tb_zxp on tablespace INDX? <code>create table tb_zxp (customer_id integer , store_id integer, trans_date date, amt number, goods_name varchar2(20), rate number(8,1), quantity integer, constraint pk_zxp primary key (customer_id,store_id,trans_date)) organization index including amt overflow tablespace indx; insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250); insert into tb_zxp values (11,25,date '2021-04-11',760,'Tower',3.8,200); insert into tb_zxp values (24,9,date '2021-05-11',5200,'Washing machine',5200,1); commit;</code> However, with this query, we can find the index is still assigned on default tablespace USERS: <code>select tablespace_name from user_extents where segment_name in (select 'TB_ZXP' c from dual union select index_name from user_indexes where table_name='TB_ZXP'); TABLESPACE_NAME ------------------------------------------------------------------------------------------ USERS</code> Then,I remove the INCLUDING OVERFLOW clause from table creation statement, and try it again? <code>create table tb_zxp (customer_id integer , store_id integer, trans_date date, amt number, goods_name varchar2(20), rate number(8,1), quantity integer, constraint pk_zxp primary key (customer_id,store_id,trans_date)) organization index tablespace indx; insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250); commit;</code> This time, the index falls upon tablespace INDX as expected: <code>select tablespace_name from user_extents where segment_name in (select 'TB_ZXP' c from dual union select index_name from user_indexes where table_name='TB_ZXP'); TABLESPACE_NAME ------------------------------------------------------------------------------------------ INDX</code> Could any guru kindly explain why the removal of including overflow can provide us desired result?
Categories: DBA Blogs

INSERT data in TIMESTAMP column having year less than 4713 i.e. timestamp like '01/01/8999 BC'

Thu, 2021-06-10 22:46
Want to insert data in TIMESTAMP column having year less than 4713 i.e. timestamp like '01/01/8999 BC' so here year is -8999 When I tries to insert it gives me error like 'YEar should be between -4713 and 9999'. BUt for some table I am having year less than 4713 as well like -8999 and -78888 So this limit is not applicable to timestamp data type. But then How to insert into timestamp for year -8999
Categories: DBA Blogs

Cannot alter Oracle sequence start with

Thu, 2021-06-10 22:46
I have tried altering the start with value of an Oracle sequence but I face [<b>ORA-02283: cannot alter starting sequence number</b>] error. I tried to find why Oracle does not allow the same but I could not find an appropriate answer for that. So my question is why is it that Oracle does not let you change a sequence start with value? (PS: I am hoping there should be a really solid technical reason behind this) Thanks in advance!
Categories: DBA Blogs

MultiThreaded Extproc Agent - max_sessions, max_task_threads and max_dispatchers parameters

Thu, 2021-06-10 04:26
Hello Team, Thanks for all the good work AskTOM is doing. Can you please help us to better understand about max_sessions, max_task_threads and max_dispatchers configuration parameters of MultiThreaded ExtProc agent and share tips on how to determine optimum values of these parameters? We have multiple clients on multiple DB servers. Each server has different H/W capacity and different load. Our understanding is that the final optimum values will depend on H/W configuration and number of external procedure calls. However, we are trying to arrive at an initial parameter configuration that can be fine tuned further based on actual situation. Thanks, AB
Categories: DBA Blogs

v$session query get garbled code

Thu, 2021-06-10 04:26
Hi,I'm using oracle 10.2.0.1 for studing, and I queried the v$session using pl/sql developer from a windows pc client ,but I found the garbled code from the results.just as following: <code>SQL> select osuser from v$session; OSUSER -------- SYSTEM ???? SYSTEM abc ??????????</code> then I ran the same command from the DB server,but I got the same results. Here are the characterset: <code>SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK SQL> select * from v$nls_parameters; PARAMETER VALUE ---------------------------------------------------------------- ---------------------------------------------------------------- NLS_LANGUAGE SIMPLIFIED CHINESE NLS_TERRITORY CHINA NLS_CURRENCY ? NLS_ISO_CURRENCY CHINA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE SIMPLIFIED CHINESE NLS_CHARACTERSET ZHS16GBK NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY ? NLS_NCHAR_CHARACTERSET UTF8 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 19 rows selected</code> I also set the environment variable in my windows os : NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK What's more,I tested my db as following: <code>SQL> select '??' from dual; '??' ---- ??</code> Now,Could you please help me,why I got some garbled codes such as ??? when querying osuser from v$session? thanks a lot.
Categories: DBA Blogs

How to Identify the MINVALUE AND MAXVALUE SCN in the flashback versions query

Thu, 2021-06-10 04:26
Hi TOM, I am facing an issue with flashback versions query. I have described my issue below. <code> Query 1: select versions_xid XID, versions_startscn START_SCN, versions_endscn END_SCN from employees VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2021-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2021-06-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') where employee_id='xyz' </code> the above query returns 2 records. <code> XID START_SCN END_SCN 0B0017008F7B0300 39280796004 39282671828 [INSERT] 2D001B0016420000 39282671828 (null) [UPDATE] </code> But on passing the versions_startscn value from the 1st query result in the filter condition of 2nd query, I got 0 records returned instead of 1 record. <code>Query 2: select versions_xid XID, versions_startscn START_SCN, versions_endscn END_SCN from employees VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE where versions_endscn = '39282671828' </code> the above query returns 0 records. Is there a way to identify the MINVALUE and MAXVALUE passed in the second query? On what cases the MINVALUE gets set?
Categories: DBA Blogs

Users Privileges

Tue, 2021-06-08 15:46
Hello, I am facing a problem and it goes like this: I have a schema named CML that we want to put common objects (Tables, View, Procedures, Packages, etc) used by the team. I would like to know what grants I should give to my user (eliasr) to create tables, views, procedures, packages in the schema CML. I want to also change existing packages and procedures.
Categories: DBA Blogs

Ampersand in input string

Tue, 2021-06-08 15:46
I have an input string I can't change. Unfortunately this input string contains an ampersand (&). I was hoping to be able to create a function which could translate an URL into something more readable text. The input string will change but an example could be as following: <i>'pw://pw.vd.dk:Vejdirektoratet/Documents/Bigger&space;projects/7x/Workflow.JPG' </i> and in this case the wanted output string would be as following <i>'Vejdirektoretet\Documents\Bigger projects\7x\Workflow.JPG'</i> My hope is to be able to create a function URL2PATH which could be called as: <code>select URL2PATH('pw://pw.vd.dk:Vejdirektoratet/Documents/Bigger&space;projects/7x/Workflow.JPG') from dual;</code> with the wanted result mentioned above. If that is not possible when the input string contains an ampersand, I secondary would like a workaround to call the function with some additional addons but I haven't been able to figure out any solution for that either. I have been looking at REPLACE, TO_CHAR, TRANSLATE etc. but until now without any succes. One of my challenges is, that this function has to be called from an outside program, so the solution by using 'set define off' ahead of the call doesn't seems to be an option.
Categories: DBA Blogs

“User created” PDB max before licensing multi tenant

Tue, 2021-06-08 15:46
Hey guys, Regarding https://blogs.oracle.com/database/oracle-database-19c-up-to-3-pdbs-per-cdb-without-licensing-multitenant-v2 And the documentation it refers to. I?m wondering if you can clarify how the 3 PDB limit (before additional licensing ) works with application containers. I?ve tried setting max_pdbs=3, then creating an application container and when I create the PDBs within that application container if I try to create 3 PDBs I get an error on creating the 3rd (max PDBs exceeded). The documentation isn?t clear on what a user created PDB is (in my opinion at least) when dealing with this type of set up so I?m not sure if the error is a bug or it?s enforcing things appropriately. Thanks ! <code> alter system set max_pdbs=3 scope=both sid='*'; ALTER SESSION SET container = CDB$ROOT; CREATE PLUGGABLE DATABASE App_Con AS APPLICATION CONTAINER ADMIN USER app_admin IDENTIFIED BY <pass>; ALTER PLUGGABLE DATABASE App_Con OPEN instances=all; ALTER SESSION SET container = App_Con; CREATE PLUGGABLE DATABASE PDB1 ADMIN USER pdb_admin IDENTIFIED BY <pass>; ALTER PLUGGABLE DATABASE PDB1 OPEN instances=all; CREATE PLUGGABLE DATABASE PDB2 ADMIN USER pdb_admin IDENTIFIED BY <pass>; ALTER PLUGGABLE DATABASE PDB2 OPEN instances=all; --below fails CREATE PLUGGABLE DATABASE PDB3 ADMIN USER pdb_admin IDENTIFIED BY <pass>; ALTER PLUGGABLE DATABASE PDB3 OPEN instances=all; </code>
Categories: DBA Blogs

Oracle apex - changing page authorization scheme on custom form

Tue, 2021-06-08 15:46
Hi, is it possible to change page authorization scheme ( or another page properties) in oracle apex on some custom build form . I need it for my application admin panel . I know which table is it , it is apex_200200.wwv_flow_list_items but on cloud its forbidden to edit WWV prefixed tables of apex_200200 user. I tried to find API - package to do it but cant find it. On cloud user ADMIN don't have select privilege on that table so this query is not working: select * from apex_200200.wwv_list_items [Error] Execution (1: 27): ORA-01031: insufficient privileges I tried to use this package but I'm getting no results : begin WWV_FLOW_API.update_page(p_id=>124,p_flow_id=>122,p_required_role=>'7.59043067032354E15'); end; Is there any way to do this through some other package in database.
Categories: DBA Blogs

PDB lock down profiles.

Tue, 2021-06-08 15:46
Team, Is it not possible to have multiple values in the lock down profile? Kindly advice. <code>c##sys@ORA12CR2> drop lockdown profile p1; Lockdown Profile dropped. c##sys@ORA12CR2> create lockdown profile p1; Lockdown Profile created. c##sys@ORA12CR2> alter lockdown profile p1 disable statement = ('ALTER SESSION') clause=('SET') 2 option=('CURSOR_SHARING') 3 value=('FORCE','SIMILAR'); alter lockdown profile p1 disable statement = ('ALTER SESSION') clause=('SET') * ERROR at line 1: ORA-65206: invalid value specified ORA-00922: missing or invalid option c##sys@ORA12CR2> alter lockdown profile p1 disable statement = ('ALTER SESSION') clause=('SET') 2 option=('CURSOR_SHARING') 3 value=('FORCE'); Lockdown Profile altered. c##sys@ORA12CR2></code>
Categories: DBA Blogs

Full Fast Index Scans with predicates

Fri, 2021-06-04 20:06
You have helped me previously setting up skinny indexing here: QUESTION_ID:9542855000346335758 This has been implemented, and is now in production and working really well and is super fast; thanks for all the previous help with this. I have a follow up question. Although most of the orders tables have millions of rows, unprocessed orders are generally only a few thousand rows, and so only hitting skinny indexes by indexing virtual columns only holding data for unprocessed orders as you explained in the question above has worked brilliantly. In most cases, a FAST FULL SCAN is performed on the indexes as they are relatively small, and we select most of the data held in those indexes. What I find odd is when I look at the explain plan in SQL Developer, if I add a predicate to the query, it's not shown in the explain plan. The explain plan remains the same i.e. just a FAST FULL SCAN. If I don't see the predicate in the explain plan, where is that being processed? I know a FAST FULL SCAN grabs all the data from the index in one go. Would I not still see the predicate in the explain plan. I can send screen shots from sql dev if that helps. Thanks.
Categories: DBA Blogs

Blocking TRUNCATE on partitions

Fri, 2021-06-04 01:46
We have a scenario where we are considering using partitions on a control table. The control table will be hit with regular CRUD operations coming from triggers on transaction tables hence we want to address fragmentation by running regular TRUNCATE table sql. My questions are - (1) What is a good strategy to handle fragmentation in high usage tables? (2) Is it possible to lock a partition in a table and execute a TRUNCATE on that partitions. Thanks Deepankar
Categories: DBA Blogs

How to restrict a user with DBA role from directly executing a package?

Fri, 2021-06-04 01:46
Hello Asktom team, Thanks for all the good work you are doing. Can you please help us with the following questions? <b>A.</b> Can we restrict or impede a user having DBA role from directly executing a particular package? This package is defined in another user's schema and can invoke third party libraries. We need to do restrict it's execution because of a security requirement imposed by the third party. One option that we are aware about is DB vault. However, looks like this option will impact the full DB. <b>B.</b> Is it possible to define something like an access control list on the package to ensure that it can only be used by specified users? Let's say, User1 has defined lots of packages and among them there is a special package named SecurePackage1. User2, User3 and User4 execute all of the User1's packages using "Execute Any Procedure" privilege. Do we have any mechanism using which we can specifically define the users that can execute the SecurePackage1 package? Thanks, AB
Categories: DBA Blogs

Querying V$DIAG_ALERT_EXT on another instance

Thu, 2021-06-03 07:26
Our application connects to the database via the Oracle Instant Client using the scan address on RAC, we therefore end up connecting to an arbitrary instance. We would ideally like to be able to query the alert log directly from our application, there is a V$DIAG_ALERT_EXT but no corresponding GV$DIAG_ALERT_EXT. Is there any way that we can query V$DIAG_ALERT_EXT on the other instances that we are not connected to?
Categories: DBA Blogs

Planing on moving many databases to new RACs

Thu, 2021-06-03 07:26
Hello, We are planning on moving over 90 Oracle databases from standalone servers into 3 new RACs. The question is how to make three groups of databases achieving the minimum CPU-I/O usage at any time (probabilistically). These groups should be composed of databases with complimentary loads in time. We think maybe we could base the matching around the metric 'Database Time Per Sec' (V$SYSMETRIC_HISTORY), analyzing how it behaves for each db during business hours, outside business hours, in a week, in a month, etc. According to https://www.oracle.com/technetwork/database/manageability/db-perf-tuning-ow08-131582.pdf 'Database Time Per Sec' does not include "Background Time Per Sec" Should we include "Background Time Per Sec" in our analysis? Thing is the METRIC_UNIT for "Background Time Per Sec" is not a time unit, and it is a little bit confusing ("Active Sessions"), how should we interpret this? Do you have any suggestion about this?, any other metric (or a calculation of metrics)? If you know of any step-by-step guide regarding this tasks or capacity management, we'll be very grateful. Thank you.
Categories: DBA Blogs

How to obtain the actual numerical value of the TIMESTAMP MINVALUE or TIMESTAMP MAXVALUE Keyword

Wed, 2021-06-02 13:06
Hello Tom, In the SELECT statement for FLASHBACK VERSION QUERY, one can successfully use "VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE" clause. I am looking for some way to obtain the numerical value of the TIMESTAMP MINVALUE or TIMESTAMP MAXVALUE keywords using SELECT TIMESTAMP MINVALUE FROM DUAL like syntax or some other way if possible. Please help if it is possible to view the actual numeric value of these keywords. Best Regards
Categories: DBA Blogs

Pages