Tom Kyte

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

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

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

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.

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>

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

Hello, I've noticed that sometime a query of an Insert statement written in ANSI SQL runs slightly slower than a query constructed using Oracle native syntax. But recently I had a case when a native SQL executed within seconds, while the ANSI SQL could not complete. I resolved the problem by converting the ANSI SQL to Oracle native, but wanted to understand why it was happening and whether any guidelines could be offered on when not to use ANSI SQL. The long running query was pulling data from one huge fact table and a a few not so big dimension tables. The fact table is partitioned by date range and sub-partitioned by list, and was created with PARALLEL 16 clause. The /*+ FULL */ hint in the query is used to force Exadata smart scan. The query looked like that: <code> SELECT /*+ FULL (fact) */ fact.partition_key_date, fact.subpartition_col, SUM(CASE WHEN fact.col = 'S' THEN (ABS (sale_amt)) END) OVER PARTITION (fact.col_a, fact.col_b) as SALE_AMT, SUM(CASE WHEN fact.col = 'O' THEN (sale_amt) END) OVER PARTITION (fact.col_a, fact.col_b) as OTHER_AMT FROM dim1 JOIN fact ON dim1.key = fact.attr1 JOIN dim2 ON fact.attr2 = dim2.key AND dim2.col = 'ABC' WHERE fact.partition_key_date = :dt_parm AND fact.subpartition_col = 'KYZ' AND fact.attr2 = 'X' AND NVL(fact.attr3, 'N') <> 'Y' AND NOT EXISTS ( SELECT 1 FROM dim3 WHERE fact.attr4 = dim3.key); </code> It was queued for parallel execution for more than an hour before starting the 32 parallel slaves. It was running for many hours and never completed. But when the analytical SUM function was removed from the selected column list it completed within seconds. Yet with the same analytical function and with Oracle SQL syntax, as shown below, the query also completed in 5 seconds. <code> SELECT /*+ FULL (fact) */ fact.partition_key_date, fact.subpartition_col, SUM(CASE WHEN fact.col = 'S' THEN (ABS (sale_amt)) END) OVER PARTITION (fact.col_a, fact.col_b) as SALE_AMT, SUM(CASE WHEN fact.col = 'O' THEN (sale_amt) END) OVER PARTITION (fact.col_a, fact.col_b) as OTHER_AMT FROM dim1, fact, dim2 WHERE fact.partition_key_date = :dt_parm AND fact.subpartition_col = 'KYZ' AND fact.attr2 = 'X' AND NVL(fact.attr3, 'N') <> 'Y' AND dim1.key = fact.attr1 AND fact.attr2 = dim2.key AND dim2.col = 'ABC' AND NOT EXISTS ( SELECT 1 FROM dim3 WHERE fact.attr4 = dim3.key); </code>

Team, Very Good Monday Morning !! The Version of Oracle we use is : <i>Oracle Database 11g Release 11.2.0.4.0 - 64bit </i> No peculiar data or table design requried over here . This request is on behalf of Data and DB Objects Migration in our project. <i>we have 2 users by name User_1 and User_2 present in the same DB Server. </i> Entire Data Migration including table data, procedures,function,views ,sequence etc ....apart from Scheduled jobs has been done. <i>User_1 has 5 Jobs by name Job_1,Job_2,Job_3 ,Job_4 and Job_5 These Jobs are available under the system table user_scheduler jobs, with their corresponding procedure names, start time ,time of execution, status etc. </i> Now I would like to move these 5 Jobs into User_2. Can someone just how to move these jobs from User_1 to User-2 <b>from a PL/SQl Developer Perspective</b>. Thanks and Regards !!

Which one is recommended in terms of performance(time taken to perform this activity) from below for a table with 18 GB data? https://oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition or https://oracle-base.com/articles/misc/partitioning-an-existing-table

Hello, I researched your Q/A bank before asking this question. What are some of the things to keep in mind, where a benefit to using a sql profile turns into a curse. What are the things that can cause the use of sql profile to go bad or problems that can go un noticed as a result of using the profile ? Thank you.

Hi Tom, On your website I didn't find anything about this topic that could help me, so I decided to write. We detect a situation in the execution of a procedure that is not expected to happen. The output parameters are returned with values, when it was not supposed to. The situation became even stranger, when after several tests we concluded that the way the package was compiled influenced the behavior of the function's execution in what refers to the output parameters. That is, if the package is compiled in debug mode, the results are as expected. The same does not happen if the package is not compiled in debug. The scenario: when executing a function that results in an error, it is not expected that the output parameter will be filled in (unless the code is not well written). But the case in point is that the code does not point to the output parameters being filled. Furthermore, if we manipulate the OPTIMIZE LEVEL defined in the database, the behavior on the output parameters is also influenced. The optimize levels 0 and 1 (corresponding to the debug), everything works fine. Level 2 always works poorly and with Level 3, depending on the complexity of the code, sometimes it works well and sometimes badly. Simple examples were created to highlight the anomaly detected. The attached script creates two functions and the call / execution of "FCOUTPUT_TEST_LEVEL_1" It should be noted that this situation was detected and tested in oracle 12c and oracle 19c (PDB) In advance I appreciate the help you can give.

GM, Do you guys know of a whitepaper or training that describes an approach to estimating how many Oracle DBA hours are needed to perform X, Y, Z? We are bidding on an effort, and I would prefer not to have to reinvent the wheel if something already exists. For instance, if a project has these requirements: DBA shall setup a three node 19c RAC cluster with ASM. DBA shall tune the system DBA shall be able to restore the database within a day with minimal data loss DBA shall It will need to be tuned. DBA shall setup a disaster recovery site using data guard DBA shall setup security to meet NIST-3029 I have started to break down all of the 50+ major tasks to satisfy the above requirements, and and threw in there rough daily estimates for each step. Database Security 10 days: o Database instance security hardening setup 3 o Database server security hardening implementation - 2 o Security scanner software setup and troubleshooting - 1 o Troubleshooting false positive security findings and waivers - 2 Oracle install and dB creation with RAC- 5 days o Clusterware setup- 3 days o RAC database creation- 1 o Licensing - .5 o Database Shutdown and Startup setup 1 Backup and Recovery Setup- 2 etc. Thanks, John

Hi Team, We have SSL certificates imported on database server using ORAPKI after creating wallet. We are using utl_http for external system communication from database and using utl_http.set_wallet to access the certificates. Now, we are en-queuing the messages to database queue and writing logic in middle ware to read message from queue and send messages to external system. but the problem is certificates are database server and the communication to external system is from middleware. Can we read the SSL certificate from database server and pass it to middleware? is there a way to pass the certificate from DB to middleware. Can you please advise. Thank You.

Hi, We just went live on Oracle a couple of weeks ago. I have a legacy process that includes running a script that was coded for Sybase. I have most of it converted to Oracle, but I'm having trouble with the Age field (it's the last piece I need to get working). I thought about just including the Age piece... then thought to include the entire script for context if nothing else. Thanks in advance for the assist! -Denise Current legacy code <code>SELECT DISTINCT meme.MEME_MEDCD_NO, meme.MEME_BIRTH_DT, AGE = CASE WHEN ( month(convert(datetime, meme.MEME_BIRTH_DT, 103))*100)+ day(convert(datetime, meme.MEME_BIRTH_DT, 103)) - ((month(getdate())*100)+day(getdate())) <= 0 THEN DATEDIFF(YEAR,convert(datetime, meme.MEME_BIRTH_DT, 103),getdate())</b> ELSE DATEDIFF(YEAR,convert(datetime, meme.MEME_BIRTH_DT, 103),getdate())-1 END, sbsb.SBSB_ID, mepe.MEPE_EFF_DT, mepe.MEPE_TERM_DT, mepe.MEPE_ELIG_IND, mepe.CSPI_ID, sbad.SBAD_COUNTY AS 'Member_County', pdpd.LOBD_ID FROM dbo.CMC_MEME_MEMBER meme INNER JOIN dbo.CMC_MEPE_PRCS_ELIG mepe ON mepe.MEME_CK =meme.MEME_CK INNER JOIN dbo.CMC_SBSB_SUBSC sbsb ON sbsb.SBSB_CK = meme.SBSB_CK INNER JOIN CMC_PDPD_PRODUCT pdpd ON mepe.PDPD_ID = pdpd.PDPD_ID INNER JOIN CMC_SBAD_ADDR sbad ON sbsb.SBSB_CK = sbad.SBSB_CK AND sbsb.SBAD_TYPE_MAIL = sbad.SBAD_TYPE WHERE mepe.GRGR_CK IN (1,3,8) AND mepe.MEPE_ELIG_IND = 'Y' AND mepe.MEPE_EFF_DT <= '09/01/2020' AND -- Match file date mepe.MEPE_TERM_DT >= '09/01/2020' AND -- Match file date meme.MEME_MEDCD_NO IN ( )</code>

I have a SQL statement can run by itself and get the result back over db link. But if I want to put result set into a table either using ?create table?.as..? or ?insert into ?? before the same select statement, I will get ORA-22992 error. What caused this? The SQL statement like: <CODE> Select a.m, a.n, a.o, a.p, b.q, b.r, b.s, c.t, c.u,c.v From a@remote a left join b@remote b on b.m=a.m and b.n=a.n Left join c@remote c on c.m=a.m and c.m=a.n Where a.yr=2019 a.class=1 order by a.m </CODE> table ?a? and ?c? don?t have LOB fields, table ?b? has a field ?Ldesc? which is CLOB, But it is NOT in the select list. Local version : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production Remote version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

I have recursive query on Oracle 11g table with undirected graph data. Each row represents one edge. The recursive query traverses all edges starting from given input edge. The idea of query is: - input edge is at the 0th level - for n>0, edge is on n-th level if it incides with node of some edge on (n-1)-th level. Query: <code>with edges (id, a, b) as ( select 1, 'X', 'Y' from dual union select 2, 'X', 'Y' from dual ), r (l, id, parent_a, parent_b, child_a, child_b, edge_seen) as ( select 0, id, null, null, a, b, cast(collect(id) over () as sys.ku$_objnumset) from edges where id = 1 union all select r.l + 1, e.id, r.child_a, r.child_b, e.a, e.b , r.edge_seen multiset union distinct (cast(collect(e.id) over () as sys.ku$_objnumset)) from r join edges e on (r.child_a in (e.a, e.b) or r.child_b in (e.a, e.b)) and e.id not member of (select r.edge_seen from dual) ) select * from r; </code> The query worked well with other inputs until two parallel edges between same node pair occured. In this case, there is edge 1 on 0th level of recursion (initial row). I expected edge 2 would be added to result on 1st level of recursion since join condition holds. Instead I get "ORA-32044: cycle detected while executing recursive with query". I know this error is reported when the row newly joined to recursive query result would be same as some existing row. What I don't understand is why Oracle treats row with same node ids but different edge id as duplicate. Adding <code>cycle child_a, child_b set iscycle to 1 default 0</code> clause gives iscycle=1 for new row, adding <code>cycle id, child_a, child_b set iscycle to 1 default 0</code> gives iscycle=0, which is both correct. <b>Is it some known Oracle 11g bug and what's the best way to handle it?</b> I cannot fill LiveSQL link form since LiveSQL supports only Oracle 19 and the problem is reproducible only in Oracle 11g which I can't migrate from. The dbfiddle equivalent is https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=43af3cfae920e31f9a2748c1c31b54ad . Thanks.

Hello, We have an application that monitors applications, detects anomalies, does correlation between metrics, performs Root Cause Analysis based on a few machine learning algorithms. We are planning onboard oracle monitoring for this application with a few metrics like below. Could you please suggest where we could get some baseline monitoring SQL's to plugin to our application, especially the SQLs that are used to generate ASH/AWR reports. We want to start small and expand over a period of time. Redo (Mb per second) Transactions per second Latency: Log file Sync, Log file parallel write, single block read all in Avg Ms IO MB/per sec Physical Reads MB/sec Physical writes MB/sec DB CPU % usage Network MB/sec Logons per sec Logical Reads Mb/sec File Sync(Avg/ms) RMAN IO mb/ms Waits Locks Top SQL?s Stale statistics on objects Top Objects by Size, growth, Avg growth per day, month Space growth (total vs used), Avg per day, month Thanks, Ravi B

Hello, This is more of a fundamental question, sorry i dont have any test cases. Does table fragmentation also imply index fragmentation for the same table. ?

my local db is a 19c i downloaded the latest version of the mobile server and while going through the installation, i came to this error, i have check my sqlnet.ora file, the tns configuration is good because i am able to connect with toad and sql developer. this is the sqlnet.ora #SQLNET.AUTHENTICATION_SERVICES= (NTS) SQLNET.AUTHENTICATION_SERVICES = (NONE) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) #SQLNET.ALLOWED_LOGON_VERSION=12 SQLNET.ALLOWED_LOGON_VERSION=9 WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY=C:\Users\TEKYI\Documents\wallet\oracle)))

HI, <code> CREATE TABLE TEMP_TEST ( ID NUMBER(10) ) ALTER TABLE TEMP_TEST ADD ( CONSTRAINT temp_test_pk UNIQUE (ID) ); </code> Scenario:1: <code> truncate table TEMP_TEST; ALTER SESSION ENABLE PARALLEL DML; INSERT INTO /*+ NOAPPEND PARALLEL(5) */ TEMP_TEST SELECT /*+ PARALLEL */DISTINCT BUCKET FROM source LOG ERRORS INTO ERR$_TEMP_TEST ('insert failed') REJECT LIMIT UNLIMITED; </code> Scenario:2: <code> truncate table TEMP_TEST; ALTER SESSION ENABLE PARALLEL DML; INSERT INTO /*+ NOAPPEND PARALLEL(5) */ TEMP_TEST SELECT DISTINCT BUCKET FROM source LOG ERRORS INTO ERR$_TEMP_TEST ('insert failed') REJECT LIMIT UNLIMITED; </code> Scenario:1 is failing with unique constraint error instead of error records inserting into error table, but scenario:2 error records are inserting into ERR$_TEMP_TEST? The only difference between these two is PARALLEL hint in select statement.