Tom Kyte

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

Supported database services

Wed, 2022-11-16 16:46
Will this service be available for Exacs?
Categories: DBA Blogs

CREATE VIEW . . . BEQUEATH DEFINER vs. WITH GRANT OPTION

Wed, 2022-11-16 16:46
I have a view, cust.viewname, that references a table in a different schema I'll call "data". According to Oracle documentation for CREATE VIEW, the optional clause BEQUEATH DEFINER causes the view to run using the permissions of the view owner. This is the default if not specified. The view schema has SELECT privs to a table in the data schema, but not WITH GRANT OPTION. The view owner has access to the data table, but when I try to create the view, it throws <b>ORA-01720: grant option does not exist for 'DATA.TABLENAME'</b> <code>CREATE OR REPLACE VIEW cust.viewname BEQUEATH DEFINER AS SELECT field1, field2, ..., fieldN FROM data.tablename;</code> If the view operates as the owner, and the view owner has access to the referenced objects, then why should grants to objects in other schemas require WITH GRANT OPTION? Is there a way around WITH GRANT OPTION? Our cloud provider refuses to allow WITH GRANT OPTION, but we have an ongoing need to create views in CUST that reference objects in other schemas and will be used by users other than CUST. Thanks in advance.
Categories: DBA Blogs

Index MIN/MAX Optimization

Wed, 2022-11-16 16:46
Dear Team, I have a logging table with the following structure: <code> SQL> desc t Name Null? Type ----------------------------------------------------- -------- ------------------------------------ LOG_ID NUMBER TRACEID VARCHAR2(250 CHAR) TYPE VARCHAR2(250 CHAR) NODE VARCHAR2(250 CHAR) URL VARCHAR2(4000 CHAR) TOKEN VARCHAR2(4000 CHAR) METHOD VARCHAR2(250 CHAR) TIMESTAMPREQ VARCHAR2(100 CHAR) BODY CLOB RESPONSE CLOB TIMESTAMPRES VARCHAR2(100 CHAR) REC_DATE DATE </code> In addition to other indexes, there is one index on columns (Trunc(Rec_Date), Type, Node, Method). I try to get min or max of trunc(rec_date) but surprisingly it doesn't uses the index min/max optimization. Below is the command and trace file output: <code> SELECT min(trunc(rec_date)) FROM t where trunc(rec_date) is not null call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.09 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 94.40 567.28 256176 507776 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 94.43 567.38 256176 507777 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 242 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 SORT AGGREGATE (cr=507776 pr=256176 pw=0 time=0 us starts=567286694) 4176565 4176565 4176565 TABLE ACCESS FULL T (cr=507776 pr=256176 pw=0 time=86468 us starts=709057596 cost=86468 size=34748199 card=3860911) SELECT min(trunc(rec_date)) FROM t where rec_date is not null call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 5.84 17.41 5 259917 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 5.84 17.41 5 259918 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 242 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 SORT AGGREGATE (cr=259917 pr=5 pw=0 time=0 us starts=17415568) 4184139 4184139 4184139 TABLE ACCESS FULL T (cr=259917 pr=5 pw=0 time=86242 us starts=106246737 cost=86242 size=34748199 card=3860911) </code> Can you please help me understanding why t...
Categories: DBA Blogs

How to call external sql script from within a PL/SQL block

Wed, 2022-11-16 16:46
Hi Tom, This is probably super simple once you show me how, but I haven't been able to find the answer in the documentation. How do I call an external SQL script from within PL/SQL? I know I can embed the actual SQL text inside the PL/SQL block, but I would rather keep the SQLs in a separate file to be called externally when I need to outside the PL/SQL code. E.g. BEGIN -- Check for some condition. -- if condition true then -- execute foo.sql END; / In SQL*PLUS, we execute external scripts using one @, e.g. @foo.sql. And within a script, we execute other scripts with two @, e.g. @@bar.sql. Not sure how to do the same within a PL/SQL block. Thanks, -Peter
Categories: DBA Blogs

Oracle Identity Access Manager for .NET

Tue, 2022-11-15 22:26
Hello, I need some guidance on using Oracle's Identity Access Manager and an OAuth Provider in an ASP.NET Core/Framework application? Could you please point me in the right direction, either examples or docs. Thanks.
Categories: DBA Blogs

Analyze and DBMS_STATS

Tue, 2022-11-15 04:06
Tom, Could you please tell me if there are any other important differences, advantages with DBMS_STATS over ANALYZE other than the points listed below. 1. DBMS_STATS can be done in parallel 2. Monitoring can be done and stale statistics can be collected for changed rows using DBMS_STATS. Thanks, Suresh
Categories: DBA Blogs

How to recover all users

Mon, 2022-11-14 09:46
I was deleting some dump file inside oracle user, accidentally I hit rm -rf /* , then permission denied error came, after that i am unable to login db, all db's property file deleted, now i am login to oracle user its coming link -bash-4.2$. inside oracle home only trace file is there other's are deleted, please help me to recover.
Categories: DBA Blogs

Variable usage in SQL_MACRO function causing ORA-00984 error

Sat, 2022-11-12 02:46
I recently had a need to see some table data at regular 10 minute intervals throughout the day. I thought I would be able to use the following query: <code>WITH TIMES(TIME) AS ( SELECT TRUNC(SYSDATE) TIME FROM DUAL UNION ALL SELECT TIME+ INTERVAL ?10? MINUTE FROM TIMES WHERE TIME < SYSDATE ? INTERVAL '10' MINUTE ) SELECT TIMES.TIME,T.* FROM TIMES CROSS JOIN AS_OF(TBL,TIMES.TIME) T</code> Making use of a SQL_MACRO function called AS_OF that parameterizes the timestamp part of an AS OF TIMESTAMP flashback query: <code>CREATE FUNCTION AS_OF (p_tab in DBMS_TF.TABLE_T, p_time in DATE) RETURN VARCHAR2 SQL_MACRO IS BEGIN RETURN `SELECT * FROM p_tab AS OF TIMESTAMP(p_time)?; END AS_OF; /</code> But the AS_OF function throws an error ORA-00984: column not allowed here. It was my understanding that p_time would be treated like a bind variable, but that doesn't appear to be the case here. Could you clarify what's going on with non-table parameters to SQL_MACRO functions behind the scenes, and help me understand why this error gets thrown?
Categories: DBA Blogs

Using tables from back end.

Fri, 2022-11-11 08:26
It's a constant discussion. I work with a lot of back end developer and They maintain that firing DML statements from their applications is the same as using calls to procedures or functions. I insist that there are reasons of practicality, security, performance, etc. but I need the opinion of someone authorized as you are. Please I need your opinion. Thank you very much for sharing so much knowledge. Regards MIGUEL DE BELLIZ
Categories: DBA Blogs

give grants through procedure

Fri, 2022-11-11 08:26
My problem is the following: I have created a procedure, in which I assign roles to users. For example: <code> SET serveroutput ON; declare vsqlsql varchar(360); permiso varchar(360):='PROBA'; BEGIN vsqlsql:='GRANT '||UPPER(permiso)||' TO ueueue001'; EXECUTE IMMEDIATE(vsqlsql); END; / --DROP PROCEDURE ROL; create or replace PROCEDURE ROL (permiso in VARCHAR2) AS vsqlsql varchar(360); BEGIN vsqlsql:='GRANT '||UPPER(permiso)||' TO ueueue001'; EXECUTE IMMEDIATE(vsqlsql); END; / exec ROL('PROBA'); select * from dba_role_privs where grantee='UEUEUE001' AND GRANTED_ROLE='PROBA'; </code> Everything is correct, until I execute the procedure and it tells me: <code> Procedimiento PL/SQL terminado correctamente. Procedure ROL compilado Error que empieza en la linea: 28 del comando : BEGIN ROL('PROBA'); END; Informe de error - ORA-01924: el rol 'PROBA' no se ha otorgado o no existe ORA-06512: en "IVAN.ROL", linea 8 ORA-06512: en linea 1 01924. 00000 - "role '%s' not granted or does not exist" *Cause: Either the role was not granted to the user, or the role did not exist. *Action: Create the role or grant the role to the user and retry the operation. GRANTEE GRANT ADM DEL DEF COM -------------------------------------------------------------------------------------------------------------------------------- ----- --- --- --- --- UEUEUE001 PROBA NO NO NO NO </code> When I execute the block the role is assigned correctly, but when I call the procedure it tells me that the role does not exist. Why?
Categories: DBA Blogs

New tables creation and their behavior

Fri, 2022-11-11 08:26
Hello, We have created 5 new tables ( Table_A - around 80K records, Table_B- around 15K records, Table_C - around 35K records, Table_D- around 55K records, and Table_E- around 130K records) These tables have been used in a view using a UNION ( The view had another set of tables from which it would fetch the data and now these new tables have been unioned) We find that soon after the data load, it takes a very long time to fetch result from the view and the application is causing a timeout.. We figured that this might be due to the Oracle statistics getting generated at later point, so we generated the stats manually soon after the data load, still the same problem. Indexes are in place. Can anyone please point out what might be the issue here? or any inputs on how we can figure this out is appreciated.
Categories: DBA Blogs

Which current client package does formally deliver the tools mkstore and orapki ?

Fri, 2022-11-11 08:26
Hello Ive installed an Instant Client 21 local copy , but i cant find the mkstore and orapki modules in any of the offered packages... Which package should provide them ? Ive decompressed all of the below, but none have them Basic JDBC SDK sqltools The best i could find is the following info https://ogobrecht.com/posts/2020-07-29-how-to-use-mkstore-and-orapki-with-oracle-instant-client/ Objective is to create a token to avoid needing to provide password to a module/shell script that will perform some oracle automation
Categories: DBA Blogs

Index cannot extent while tablespace have enough space

Wed, 2022-11-09 19:46
I got an error when running a Form. ORA-01654: unable to extend index SYSTEM.ARAID_INVOICE_SEQ_ID by 512 in tablespace ar_idx When I check the database, I found the following Size Used Free Tablespace in MG. in MG. in MG. -------------------- ------ ------ ------ ------ ------ AR_IDX 250 204 (82%) 46 (18%) INDEX_NAME TABLESPACE INITIAL_EX NEXT_EXT Max Use M-U MAX_SIZE USED_SIZE ------------------------------ ---------- ---------- ---------- ---- ---- ---- ------------ ------------ ARAID_INVOICE_SEQ_ID AR_IDX 15360K 1024K 249 3 246 263.00M 17.01M Segments that cannot extend =========================== Seg OWNER Name Tablespace NEXTEXT LARGEST --------------- ------------------------------ --------------- --------- --------- SYSTEM ARID_SALES_RECAP AR_IDX 1054720 819200 SYSTEM ARID_REVENUE_CENTRE 1054720 819200 SYSTEM ARID_CODE_YEAR 1054720 819200 SYSTEM ARID_ACCOUNT_NO 1054720 819200 SYSTEM ARAID_CODE_YEAR 1054720 819200 SYSTEM ARAID_INVOICE_SEQ_ID 1054720 819200 SYSTEM ARAID_SALES_RECAP 1054720 819200 SYSTEM PK_CMSTMP_BATCH 1048576 819200 SYSTEM IDX_ARID_MC 1054720 819200 It seems that there is plenty of space for the indexes. What is the reason for the error? Best Regards
Categories: DBA Blogs

TDE Wallet

Wed, 2022-11-09 01:26
Good morning Tom TDE and generating a new Master Key - what happens in the background I have seen an answer where you are explaining that issuing a new Master Key does not have an impact on the table (or data) being encrypted as such. I understand that. In my understanding there are 2 keys involved - Table Key and the Master Key. The Table Key is actually used for encryption of the tables and not the Master Key. The Master Key is used to encrypt the Table Key and this encrypted Table Key is then stored in the Oracle data dictionary. So when one ReKey (set a new Master Key), in my understanding what happens is that the encrypted Table Key must first be decrypted with the existing Master Key, after which this decrypted "open" Table Key is encrypted with the newly generated Master Key. For example: Current Master Key(CMK) : w5du8xs Table Key (TK) : tktktktk (actually a random string) Encrypting TK with MK : w5du8xs (enc) tktktktk -> a8j4n3gh which is stored in Oracle data dictionary So when generating a new Master Key (nMK), Oracle must first decrypt a8j4n3gh with the CMK (w5du8xs) to get the TK of tktktktk, after which the new Master Key (nMK) of for example zxcvbn275 is used to encrypt the Table Key and this new encrypted value gets stored again. So IF above mentioned understanding is correct, does that mean that one will not be able to generate a new Master Key if for some "funny" reason (it will not be funny!), the wallet has been damaged and Oracle cannot get hold of the current Master Key? Or also stated otherwise, to generate a new Master key, there must be an existing activated Master Key. Thanks for allowing me the opportunity to ask.
Categories: DBA Blogs

Data Redaction

Wed, 2022-11-09 01:26
Hi, Greetings. I would like to apply data redaction policy on a table to accomplish the following business requirement. The table contains PII filed SSN. The security(Data Redaction Policy) should be defined as below. 1. Person with Manager Role can see all the parts of SSN.( No redaction). 2. Person with Supervisor Role can see the last 4 digits SSN.( partial redaction). 3. Person with call center representative role can see nothing.( Full redaction). From looking through the documentation and examples out in the web world, I understand we can apply redaction policy like a switch on for particualr set of roles and off for the rest of the roles. Any suggestions and Guidance on how we can implement the above security in place would be highly appreciated. Thank you so much for your time. Sincerely CT
Categories: DBA Blogs

'direct path read' is much slower in Oracle database 19c in comparison with 11g

Tue, 2022-11-08 07:06
Hello Could you please help me with the following issue I'm developing a procedure that gets and processes large blob data from a table. It is much slower in my dev database 19.16 in comparison with a dev database 11.2.0.4. I noticed in the trace file that 'direct path read' takes much longer time in 19.16 (ela field). Do you know where the timing difference come from? The both databases have the same SGA. They're placed on the virtual machines with same computing resources that use the same SSD disk. 11.2.0.4: <code>WAIT #3061052452: nam='direct path read' ela=3 file number=2 first dba=16173 block cnt=1 obj#=3862810 tim=1665396576739512</code> 19.16: <code>WAIT #139622124454344: nam='direct path read' ela=105 file number=44 first dba=194052 block cnt=1 obj#=3635694 tim=167978375852</code> Here is the test case that I used to generate the trace files: <code>CREATE OR REPLACE DIRECTORY BLOB_DIR AS '/tmp'; CREATE TABLE test_blob_tab ( id NUMBER, blob_data BLOB ); DECLARE l_bfile BFILE; l_blob BLOB; l_dest_offset INTEGER := 1; l_src_offset INTEGER := 1; BEGIN INSERT INTO test_blob_tab (id, blob_data) VALUES (1, empty_blob()) RETURN blob_data INTO l_blob; l_bfile := BFILENAME('BLOB_DIR', 'myzipfile.zip');--the zip file size is 109MB DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly); DBMS_LOB.loadblobfromfile ( dest_lob => l_blob, src_bfile => l_bfile, amount => DBMS_LOB.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset); DBMS_LOB.fileclose(l_bfile); COMMIT; END; / declare l_blob blob; begin for i in 1..10000 loop for c in (select dbms_lob.substr(tbt.blob_data,2000,i) blob_data from test_blob_tab tbt) loop l_blob:=c.blob_data; end loop; end loop; end; / </code> Here are the DBMS_METADATA.GET_DDL on table test_blob_tab for both environments 11.2.0.4 database: <code> "CREATE TABLE "APPS"."TEST_BLOB_TAB" ( "ID" NUMBER, "BLOB_DATA" BLOB ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "APPS_TS_TX_DATA" LOB ("BLOB_DATA") STORE AS BASICFILE ( TABLESPACE "APPS_TS_TX_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING ) " </code> 19.16 database: <code> "CREATE TABLE "APPS"."TEST_BLOB_TAB" ( "ID" NUMBER, "BLOB_DATA" BLOB ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "APPS_TS_TX_DATA" LOB ("BLOB_DATA") STORE AS SECUREFILE ( TABLESPACE "APPS_TS_TX_DATA" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES ) "</code> Thank you, Alex
Categories: DBA Blogs

To report Rooms By Periods By Student

Tue, 2022-11-08 07:06
Hello- Required to report room numbers for every period of the day the student is in (like a sort of pivot format). I have the query that pulls the required information: -- <code> Select s.schoolid scid, s.student_number stnu , s.lastfirst na, cc.expression, sec.room rm , s.grade_level grl from students s, sections sec, cc cc where s.id = cc.studentid and cc.sectionid = sec.id and sec.schoolid = 87 and cc.termid = 3200 </code> Here is the sample of 5 student records returned by the above: <code>SCID STNU NA EXPRESSION RM GRL 87 100 AAA 3-5(A-E) E003 12 87 100 AAA 9(A-E) Patti12 12 87 100 AAA 6(A-E) E207B 12 87 100 AAA 8(A-E) E002B 12 87 100 AAA 9(A-E) PattiAll 12 87 100 AAA 7(A-E) ECaf 12 87 100 AAA 2(A-E) EGym 12 87 100 AAA 1(A-E) E002B 12 87 101 BBB 9(A-E) MularzAll 12 87 101 BBB 1(A-E) E201 12 87 101 BBB 7-8(A-E) 12 87 101 BBB 2(A-E) EGym 12 87 101 BBB 6(A-E) ECaf 12 87 101 BBB 3-5(A-E) E003 12 87 102 CCC 8(A-E) 12 87 102 CCC 2(A-E) E200 12 87 102 CCC 6(A-E) E209 12 87 102 CCC 1(A-E) EGym 12 87 102 CCC 7(A-E) ECaf 12 87 102 CCC 9(A-E) 12 87 102 CCC 3-5(A-E) E008 12 87 103 DDD 3-5(A-E) E106B 12 87 103 DDD 2(A-E) E207A 12 87 103 DDD 8(A-E) 12 87 103 DDD 6(A-E) E209 12 87 103 DDD 1(A-E) EGym 12 87 103 DDD 7(A-E) ECaf 12 87 103 DDD 9(A-E) 12 87 104 EEE 1(A-E) EGym 12 87 104 EEE 8(A-E) E208A 12 87 104 EEE 6(A-E) E207B 12 87 104 EEE 7(A-E) E306 12 87 104 EEE 2(A-E) E208A 12 87 104 EEE 5(A-E) ECaf 12 87 104 EEE 3(A-E) E110 12 87 104 EEE 4(A-E) E001B 12</code> Expect to have an out put like so: <code>STNU NA GRL Per 1 Per 2 Per 3 Per 4 Per 5 Per 6 Per 7 Per 8 Per 9 Per 10 Per 11 Per 12 100 AAA 12 E002B EGym E003 E003 E003 E207B ECaf E002B PattiAll 101 BBB 12 E201 EGym E003 E003 E003 ECaf MularzAll 102 CCC 12 EGym E200 E008 E008 E008 E209 ECaf 103 DDD 12 EGym E207A E106B E106B E106B E209 ECaf 104 EEE 12 EGym E208A E110 E001B ECaf E207B E306 E208A </code> A student can attend a max of 12 periods in a day. Student can attend a two period class (Ex:7-8(A-E)) or a three period class (Ex:3-5(A-E)) or a single period class (Ex: 1(A-E)) Thanks for any assistance! Venkat --=========== <code>create table t ( SCID number (4) , STNU number(8), NA varchar2(30), EXPRESSION varchar2(10), RM varchar2(10), GRL number(2) ) ;</code> <code>insert into t values ( 87,100,'AAA','3-5(A-E)','E003',12); insert into t values ( 87,100,'AAA','9(A-E)','Patti12',12); insert into t values ( 87,100,'AAA','6(A-E)','E207B',12); insert into t values ( 87,100,'AAA','8(A-E)','E002B',12); insert into t values ( 87,100,'AAA','9(A-E)','PattiAll',12); insert into t values ( 87,100,'AAA','7(A-E)','ECaf',12); insert into t values ( 87,100,'AAA','2(A-E)','EGym',12); insert into t values ( 87,100,'AAA','1(A-E)','E002B',12); insert into t values ( 87,101,'BBB','9(A-E)','MularzAll',12); insert into t values ( 87,101,'BBB','1(A-E)','E201',12); insert into t values ( 87,101,'BBB','7-8(A-E)','',12); insert into t values ( 87,101,'BBB','2(A-E)','EGym',12); insert into t values ( 87,101,'BBB','6(A-E)','ECaf',12); insert into t values ( 87,101,'BBB','3-5(A-E)','E003',12); insert into t values ( 87,102,'CCC','8(A-E)','',12); insert into t values ( 87,102,'CCC','2(A-E)','E200',12); insert into t values ( 87,102,'CCC','6(A-E)','E209',12); insert into t values ( 87,102,'CCC','1(A-E)','EGym',12); insert into t values ( 87,102,'CCC','7(A-E)','ECaf',12); insert into t values ( 87,102,'CCC','9(A-E)','',12); insert into t values ( 87,102,'CCC','3-5(A-E)','E008',12); insert into t values ( 87,103,'DDD','3-5(A-E)','E106B',12); insert into t values ( 87,103,'DDD','2(A-E)','E207A',12); insert into t values ( 87,103,'DDD','8(A-E)','',12); insert into t values ( 87,10...
Categories: DBA Blogs

refactoring

Mon, 2022-11-07 12:46
How to transform a with statement into a pipeline function? I have a with statement with n parts. I have to reuse the parts in other sql statements. In order to avoid rewriting the parts. I transform the parts in pipeline functions. It means that I have to 2 type pro part: a record and a table of this record. In my with statement, part is generally defined like this: <code> partN as( select parNminus1.* , t.f1.....t.fk from t join partNminus1 on ......... ) </code> I don't want to rewrite all the field of partNminus for the record of partN. If I use a object, not a record, I can defined that <code> type o_partn is object ( partnminus1 o_partnMinus1, f1 typeoff1 ............... ) </code> If it works I would like to select each field of partLastN <code> select t.*,t.partLastminus1.*,t.partLAstMinus2.* from f_partLast </code> But the object are print and I don't want that.
Categories: DBA Blogs

"PL/SQL - only" ways to do JSON Token validation

Mon, 2022-11-07 12:46
We have a requirement to validate a JSON Web token (JWT) from within the Oracle database (19c) As I couldn't find any direct ways in PL/SQL which could do JWT validation, a Java program was created which does this job. This Java program and referenced jar files were then successfully uploaded to the DB using the "loadjava" utility. A PL/SQL function was then built which internally will call the Java program. But for some reason the Java program, when invoked via the PL/SQL function returns the error "Cannot obtain jwks from url https://dev-uk.apis.allianz/azuk/ds/uam/identity/oauthv2/v1/.well-known/jwks.json" I had the same error when the same Java program was executed from my Windows machine. This was rectified by copying relevant certificate file to "\jre\lib\security" and then importing it using the "keytool" utility. Questions --------- 1) Does Oracle use its own JVM or the one installed in the Database server, when a stored Java program is invoked from PL/SQL? 2) Similar to what I did in Windows, if we import the certificate to enable connection to the required URL under the "\jre\lib\security" path in the DB server, will it impact any other apps using this JVM? 3) Is there any "PL/SQL - only" ways to do JSON Token validation? Code snippets for your reference -------------------------------- 1) Java Program public static String isValidToken(String domain, String encryptedToken) { DecodedJWT jwt = null; try { try { jwt = JWT.decode(encryptedToken); } catch (JWTDecodeException e) { return e.getMessage(); } JwkProvider provider = new UrlJwkProvider(domain); Jwk jwk = null; try { jwk = provider.get(jwt.getKeyId()); } catch (JwkException e) { return e.getMessage(); } Algorithm algorithm = null; try { algorithm = Algorithm.RSA256((RSAPublicKey) jwk.getPublicKey(), null); } catch (IllegalArgumentException | InvalidPublicKeyException e) { return e.getMessage(); } algorithm.verify(jwt); return "VALID"; } catch (Exception e) { return e.getMessage(); } } 2) PL/SQL Function CREATE OR REPLACE FUNCTION is_valid_token ( domain IN VARCHAR2, token IN VARCHAR2 ) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'jwt.checks.JsonWebTokenChecks.isValidToken(java.lang.String, java.lang.String) return java.lang.String' ; 3) Calling PL/SQL function (and thereby Java function) declare result varchar2(2000); url varchar2(1000):= 'https://dev-uk.apis.allianz/azuk/ds/uam/identity/oauthv2/v1'; begin result := is_valid_token(url,'the-token-the-token'); dbms_output.put_line(result); end; 4) Result Cannot obtain jwks from url https://dev-uk.apis.allianz/azuk/ds/uam/identity/oauthv2/v1/.well-known/jwks.json
Categories: DBA Blogs

DRCP default settings, why a dynamic Connection Pool size?

Mon, 2022-11-07 12:46
It has been stressed by Oracle, for example by The Oracle Real World Performance Team, that Dynamic Connection Pools can be a killer for database performance and should be avoided. We therefore strive to persuade application server administrators to set up Static Connection Pools, where minSize=maxSize. The Database Resident Connection Pool have default settings that gives a dynamic connection pool. The default for MINSIZE = 4 and the default for MAXSIZE = 40, indicating that a Dynamic Connection Pool is advised. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-processes.html#GUID-ABBE6941-73AC-4343-B320-A1400451B44F Why is this? Is the DRCP so integrated with the database that a Dynamic Connection Pool is OK for DRCP? Or should we configure DRCP with MINSIZE=MAXSIZE? I haven't been able to find any documentation, blog posts or Whitepapers discussing this topic. Do you have any thoughts/advice on this issue? regards from Lars Johan
Categories: DBA Blogs

Pages