Tom Kyte

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

Convert JSON into Record

7 hours 14 min ago
HI, i have a json file, i have to insert into one table it's have multiple columns, How will convert into as record? Please see the below example <b>Example:</b> {"EMPNO":123,"ENAME":"TEST","JOB":"ENGINEER","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null} {"EMPNO":3,"ENAME":"harish","JOB":"developer","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null} {"EMPNO":555,"ENAME":"TESTTTT","JOB":"SOFTWARE","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null} {"EMPNO":102,"ENAME":"harish","JOB":"developer","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null} {"EMPNO":444,"ENAME":"TESTTTT","JOB":"ENR2222","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null} {"EMPNO":700,"ENAME":"PANIPOORI","JOB":"CRICKET","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null} {"EMPNO":101,"ENAME":"harish","JOB":"developer","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null} i want to insert into emp table or any other ways to create insert scripts based on the Json file? Thanks Praveen
Categories: DBA Blogs

ORA-04020: deadlock detected while revoking a priv from a procedure

7 hours 14 min ago
Hi Tom, While trying to revoke execute privilege on a procedure from inside of the same procedure the deadlock occurs. Could you please advise on this behavior: <code>create procedure test_proc as begin execute immediate 'revoke execute on test_proc from some_user'; end; exec test_proc; Error starting at line : 7 in command - BEGIN test_proc; END; Error report - ORA-04020: deadlock detected while trying to lock object TEST_PROC ORA-06512: at "TEST_PROC", line 4 ORA-06512: at line 1 04020. 00000 - "deadlock detected while trying to lock object %s%s%s%s%s" *Cause: While trying to lock a library object, a deadlock is detected. *Action: Retry the operation later.</code> Thank you!
Categories: DBA Blogs

Modelling question

Wed, 2020-09-30 09:06
Hi TOM, I need to accommodate this kind of object (subscription) in the database (not necessarily JSON): <code> { "subscriber": 12343, --user_id "subscr_data_start": 20200901, "subscr_data_end": "", "object_name": "a very fancy name", "object_event_type": "create", "object_type": "product", "object_event_match": "exact", "conditions": { "countries": ["IT", "DE"], "categories": ["computers", "HI-FI"] } }</code> That would translate as: Starting from `20200901` with no end date, the user `12343` would like to be alerted every time we `create` a `product` of kind (`computers` or `HI-FI`) in either country (`IT` or `DE`) whose name matches `exact`ly `a very fancy name`. Data modelling details in the LiveSQL link. Is it a correct/valid approach later on when i need to query it (using `subscriber` column as index? If not, should i go with the standard relational approach instead ? Thanks, Alex
Categories: DBA Blogs

Oracle Cloud Free Tier Apex Public Page Document upload

Wed, 2020-09-30 09:06
Sorry I didn't put this through livesql but it is not applicable for this question. I have created and OCI Free Tier ATP DB and I have installed Oracle Apex. I have created an application and a public page in the application with a form based on a table that has a blob column. If the page is not public I can successfully upload a document and open it. If I change the page to public and try to upload a document again when I click submit is says 'it is not possible to upload a file on a public page on this instance'. I have checked this document but I don't see anything that says I should not be able to do this. https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/apex-restrictions.html#GUID-E13D5044-B9DD-4168-8A12-C99532940DA9 So my next step was to check the APEX Instance ALLOW_PUBLIC_FILE_UPLOAD parameter using an admin account via SQL Dev and via my Apex Admin login and the sql commands screen (this latter option was never going to work but I tried it anyway) This is the output from running using my ATP DB admin account via SQL Dev <code> BEGIN APEX_INSTANCE_ADMIN.SET_PARAMETER('ALLOW_PUBLIC_FILE_UPLOAD', 'Y'); END; </code> Error report - ORA-20987: APEX - Instance parameter not found - Contact your application administrator. Details about this incident are available via debug id "125044". ORA-06512: at "APEX_200100.WWV_FLOW_ERROR", line 1132 ORA-06512: at "APEX_200100.WWV_FLOW_ERROR", line 1499 ORA-06512: at "APEX_200100.WWV_FLOW_INSTANCE_ADMIN", line 87 ORA-06512: at "APEX_200100.WWV_FLOW_INSTANCE_ADMIN", line 190 ORA-06512: at line 2 So all I am basically asking is it possible to upload a file publicly on the OCI Free Tier via a public apex page or is it just a missing parameter? I can understand the reasons if it is not possible to do this but I am trying to understand how I can check what is and isn't possible as the documentation that I have found doesn't appear to say it isn't possible. Any information is greatly appreciated.
Categories: DBA Blogs

Changing Workspace name

Wed, 2020-09-30 09:06
Would you please help me to change my Workspace ID without deleting (remove) the workspace. Thank you
Categories: DBA Blogs

Hide link or item by role

Wed, 2020-09-30 09:06
Hey! I have a number of permissions in the app that I can give users, One of the permissions is a manager - and I want to give it a different access to some of the functions in the app. I wanted to ask, how can a dynamic action process hide an item by role? For example: I want to hide a field from someone who is not a manager. I guess I need to check the role after login process but I thought maybe there is something ready that checks me: What is the role of the user who is now connected to the app?
Categories: DBA Blogs

Oracle 20c release timeframe

Wed, 2020-09-30 09:06
Hello masters, Do you know when the Oracle v20 will be officialy released for production purpose? We are almost October the first, winter is coming in France, I have no news... I am worried about a production release in 2020. Here https://docs.oracle.com/en/database/oracle/oracle-database/20/index.html I can read "Oracle Database 20c is available only for preview. It is not available for production use." Bests regards, David D.
Categories: DBA Blogs

Truncating digits before decimal in a decimal number

Tue, 2020-09-29 15:06
Hi, I am facing a problem in one of my update queries. There is a column I am updating whose datatype is NUMBER(9,5). So it can hold 5 places after decimal, and 4 before decimal. I am using a round function, so that takes care of places after decimal. But for some of the records the value is getting computed as say 123546.12345, and so i am getting -1438 overflow error while updating. I want a quick way (or function) to just get rid of the extra digits before decimal point in such cases. So, in the example above, I want the output as 1235.12345 Please suggest a suitable way. Thanks!
Categories: DBA Blogs

Oracle 18c DBMS_MVIEW.REFRESH_DEPENDENT "number_of_failures" OUT parameter is not returning value

Tue, 2020-09-29 15:06
We are using Oracle 18c and when I use DBMS_MVIEW.REFRESH_DEPENDENT procedure, "number_of_failures" OUT parameter is not returning any value. It is failing as I manage to catch the error in the exception. Any idea please? Is it returning number_of_failures on earlier oracle versions? <code>select * from v$version;</code> BANNER BANNER_FULL BANNER_LEGACY CON_ID -------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production 0 Version 18.5.1.0.0 <code>set serverout on DECLARE n_failures NUMBER(12):=0; BEGIN dbms_mview.refresh_dependent(number_of_failures => n_failures, list => 'MV_TABLE1', atomic_refresh => TRUE, nested => TRUE); dbms_output.put_line('Number of failures: '||n_failures); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error: '||SQLERRM||', Number of failures in EXCEPTION: '||n_failures); END; /</code> Error: ORA-12008: error in materialized view or zonemap refresh path ORA-01427: single-row subquery returns more than one row, Number of failures in EXCEPTION: 0 PL/SQL procedure successfully completed.
Categories: DBA Blogs

Procedure Inserting Dupes Despite Test for Existing Record Prior to Insert

Tue, 2020-09-29 15:06
Hi, I have a fairly simple pl/sql procedure invoked by a java program that itself is invoked when a user clicks a link on a website. The procedure takes in 2-3 parameters, and its main purpose is to insert 1 record (based on the params) into a table. (If needed, a version of the proc is in the LiveSQL link. Table and column names changed to protect the innocent.) Prior to the insert, the procedure tests whether the row it's about to insert already exists. This is necessary because we're only avoiding dupes for the specific values passed in by this program, whereas dupes involving other values are OK, so there is no unique constraint. If the row already exists, then the proc inserts this "failed" attempt into a logging table (this part is done by error handling, which I'm somewhat regretting and thinking of rewriting in a simple ELSE part of an IF, but it's unlikely this is relevant to my question). If the row does <i>not</i> yet exist, then it gets inserted, but I should note that there's a quick SELECT INTO between the test and the insert. Vast majority of the time this works fine, but a handful of duplicates have gotten into the table! The timestamps on the date_inserted field indicate that the dupes were inserted anywhere from 0 to a whole 18 seconds apart from one another. I have some ideas on why this may be happening (multiple clicks & network latency causing multiple sessions & procedure calls to fire simultaneously...perhaps first session committing between the time second session tests and inserts), but outside a unique constraint, is there anything I can do within the procedure to stop these dupes from sneaking in? Would it stop the dupes entirely if I test for the row's existence within the insert? Something like: <code>insert into user_demo with new_rec as ( select 1 as internal_id, 'ABC' as demo_code, 'blah' as user_demo_comment, sysdate as date_inserted from dual ) select * from new_rec where not exists (select 'x' from user_demo ud2 where ud2.internal_id = new_rec.internal_id and ud2.demo_code = demo_code); </code> Thank you! Phil PS. If you look at the proc in LiveSQL, please excuse the gross varchar2 type on what should probably be a date parameter. I'm aware this is not a best practice, but letting you know it's there in the interest of full disclosure. Thank you in advance, Phil
Categories: DBA Blogs

Custom domain for APEX

Tue, 2020-09-29 15:06
Hey! I'm connected to an Oracle cloud and using the latest version of apex. I want to know how I can exchange a domain for one unique one I have purchased This means that where it will be written to me for example: https://xxxxxxxxx.adb.eu-frankfurt-1.oraclecloudapps.com/ords/r/workspace/appname/ I want it to be: https://www.my-domain.com/ords/r/workspace/appname/l Where do you do it from? Will it affect other things to consider?
Categories: DBA Blogs

My CLOB is occupying more than double the size it should take.

Tue, 2020-09-29 15:06
<code>CREATE TABLESPACE av_ag_temp_tablespace LOGGING DATAFILE 'C:\Oracle\oradata\DEMO1\temptablespace.dbf' SIZE 500M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO / create table c ( x int, y clob ) TABLESPACE AV_AG_TEMP_TABLESPACE;</code> I inserted 1000 rows( each row has 500000 size ) into table C and then checked for the size occupied versus expected. Then dropped the table C and table space and recreated the both table space and table. Again I performed Insert 1000 rows, then deleted them and then inserted them again. and dropped again and created table space and table and then performed insert - update operation. Rows Clob Size DML Total Size(GB) Expected Size (GB) 1000 500000 Insert 1.14 0.5 1000 500000 Insert-Delete-Insert 1.81 0.5 1000 500000 Insert-Update 2.01 0.5 I got the total size using bytes from user_extents for that clob column. Why the size is being occupied more than it needs to be.
Categories: DBA Blogs

about wm_concat() in 12c

Tue, 2020-09-29 15:06
Hi tom, when i tried use this wm_concat() function in 12c i'm getting this error can you briefly explain? ORA-00904: "WM_CONCAT": invalid identifier 00904. 00000 - "%s: invalid identifier
Categories: DBA Blogs

How to improve Oracle data extraction throughput rate

Tue, 2020-09-29 15:06
For the last 17 years I've been focused on real-time apps where the number of rows retrieved from Oracle is usually 1 and almost always less than 10. I have a good reputation in being able to write very performant real-time applications, but suddenly I'm faced with reading millions (and possibly billions) of rows from a massive batch database server and am looking for ways to improve the speed. So here's the big question: What is hands down the absolute fastest way to extract data from an oracle table or view, given these constraints: 1) There is exactly one query being executed which traverses the entire table or view. 2) The rows being extracted from oracle need to be pre-processed by a formatting routine that will essentially convert the entire row into a fixed length record. The layout for the fixed length record is similar to a Cobol data layout, however, no packed binary, just good old plain ascii. 3) The fixed length records will be grouped into bundles and the bundles will be handed off to background threads that will further process the data and produce output files and/or summary information. Note that for our benchmark speed test, this last piece is omitted. We found this blub on the Driver page that states that using OCI over IPC is faster than a network connection and are trying to test the premise using a home-made Java based speed-test/benchmark program that just does steps 1 & 2 from the above constraints. https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html <code> An IPC connection is much faster than a network connection. </code> Our database hardware consists of a bare-metal AIX database server. We also have several other Linux based VM's in the same datacenter. The remote client for our testing purposed is just one of the Linux VM's in the same datacenter as the database AIX server. In the java speed-test app, we've set the statement fetch size to 4196 rows. The results are are confusing me, see table below. According to our results, using JDBC's thin driver (over the network) is faster than OCI over IPC. Note, we set a max records value to stop the benchmark at a fixed point rather than traversing the entire table. <code> Mib/ Library Protocol Connect String Client Records Seconds Second jdbc ojdbc jdbc:oracle:thin:@MyDbServer:1521/MySID remote 3,629,265 453.569 9.195 oci sql*net jdbc:oracle:oci8:@MyDbServer:1521/MySID remote 3,629,265 631.424 6.605 oci ipc jdbc:oracle:oci8:@ local 3,629,265 667.554 6.248 </code> Due the the poor Mib/Sec rate we see using the Java App, I decided to write a C++ OCCI program and it is much, much faster, but alas, the database server is AIX and I can't for the life of me figure out how to compile the program on AIX. Will the program be faster if I run it on the database server? Is there conceivably another method of data extraction that might be faster than the Java or C++ programs we've written? In the C++ program, I've set the prefetch memory size to 1Gib and have played with different fetch row sizes. The client is a remote Linux server in the same datacenter as the database server itself. <code> Prefetch Prefetch Mib/ Memory Max Rows Rows Second 1,073,741,824 100,000 10 2.529 1,073,741,824 100,000 100 11.135 1,073,741,824 100,000 1,000 29.115 1,073,741,824 100,000 10,000 32.064 1,073,741,824 100,000 100,000 23.067 1,073,741,824 1,000,000 100,000 32.970 1,073,741,824 10,000,000 10,000 33.358 1,073,741,824 3,000,00...
Categories: DBA Blogs

impdp truncate date imported in timestamp

Wed, 2020-09-23 12:26
Hi, I created a table with a date column in an Oracle 18 database and I filled it with some date values: create table test_date (col1 date); insert into test_date values (sysdate); insert into test_date values (sysdate); select * from test_date; COL1 ------------------- 22.09.2020 16:33:05 22.09.2020 16:33:15 The same table is created in an Oracle 19 database but with a timestamp(6) column: create table test_date (col1 timestamp(6)) If I import the table from 18 to 19 the date values are truncated (I used a database link but the error occurs also with a dumpfile): impdp system/...@ora_instance CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=TRUNCATE TABLES=user_in_18db.test_date network_link=db_link_from_19_to_18 remap_schema=user_in_18db:user_in_19db Import: Release 19.0.0.0.0 - Production on Tue Sep 22 16:45:26 2020 Version 19.5.0.0.0 ... . . "USER_IN_19DB"."TEST_DATE" 2 Zeilen importiert Job "SYSTEM"."SYS_IMPORT_TABLE_01" erfolgreich um Di Sep 22 16:45:37 2020 elapsed 0 00:00:10 abgeschlossen but select * from test_date; -- in the Oracle 19 database COL1 ------------------- 22.09.2020 00:00:00 22.09.2020 00:00:00 For output I am using: NLS_TIMESTAMP_FORMAT = 'DD.MM.YYYY HH24:MI:SS' NLS_DATE_FORMAT = 'DD.MM.YYYY HH24:MI:SS' YS Nicola
Categories: DBA Blogs

how to use profiler (similar to MS SQL Server) in Oracle SQL Developer

Wed, 2020-09-23 12:26
how to use profiler (similar to MS SQL Server) in Oracle SQL Developer
Categories: DBA Blogs

What are the alternatives for ORA_EXCEL (Oracle Excel) is a PL/SQL package for Oracle® database that produces Excel XLSX documents?

Wed, 2020-09-23 12:26
I wish to generate reports from my Oracle 11g database in an MS excel document(multiple tabs). Do we have a solution or a product from Oracle for this task? Thank you, Sunil
Categories: DBA Blogs

Split Records into Groups of N Rows

Wed, 2020-09-23 12:26
Got a SQL from the table with 100,000 + records , one particular varchar2 field contains strings like '12345', '56789', '1111'. I would like to create a table with split / chunks with appropriate comma seperate example in the live link ::: select listagg(id, ',') within group (order by id) from (select course_id as id from ad.AD_STUDENT_COURSE_DETAILS) Example :: 100 records each record has the following ids <code>1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99</code> Now Assume based on the above result sets I should be able to make my sequence dynamic and insert group of records into comma separated values (listagg) , on my own definition If I give 22 it should get values and distribute into 4 chunks and the remaining left based on the select query results and make the respective chunks of sequence. Here in this example the 99 records from my select query have been loaded into new table with 5 records (5 sequence number) each record carry the 22 records and the remaining. New Table <code>seq_number list_of_ids 1 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22 2 23,2425,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44 3 45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66 4 67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88 5 89,90,91,92,93,94,95,96,97,98,99</code> Is it possible to achieve the same in SQL query to who the results in above format or PL/SQL block? Appreciate your help.
Categories: DBA Blogs

Outer join inside decode syntax

Tue, 2020-09-22 18:26
Hi Tom, Could you please clarify why the following simple sql is returning the result? <code> select a.dummy, b.dummy, decode ( a.dummy, 'X', null, b.dummy ) from dual a, dual b where decode ( a.dummy, 'X', null, b.dummy (+) ) = a.dummy ; </code> At first it looks like satisfied null = 'X' condition. Is it true that using (+) after default value in decode is an equivalent of left outer joining on entire decode? Is the following query the equivalent of the former one? <code> select a.dummy, b.dummy, decode ( a.dummy, 'X', null, b.dummy ) from dual a left outer join dual b on decode ( a.dummy, 'X', null, b.dummy ) = a.dummy ; </code>
Categories: DBA Blogs

Grant privileges to add constraints on a table to other users

Tue, 2020-09-22 18:26
Hello, I create a table and grant ALL privileges on it to user B. User B can delete, select, insert, update on the table, but When user B uses ALTER TABLE ... ADD CONSTRAINT...PRIMARY KEY OR UNIQUE, it has a SQL Error: ORA-01031: insufficient privileges. How can I fix this error? Thanks, Duong
Categories: DBA Blogs

Pages