DBA Blogs

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

Tom Kyte - 1 hour 54 min ago
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

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

Tom Kyte - 1 hour 54 min ago
how to use profiler (similar to MS SQL Server) in Oracle SQL Developer
Categories: DBA Blogs

Split Records into Groups of N Rows

Tom Kyte - 1 hour 54 min ago
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

impdp truncate date imported in timestamp

Tom Kyte - 1 hour 54 min ago
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

SQL Profiles Long Term Benefits and Issues

Tom Kyte - Tue, 2020-09-22 18:26
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.
Categories: DBA Blogs

Partition existing non-partitioned tables in 12.1.0.2.0

Tom Kyte - Tue, 2020-09-22 18:26
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
Categories: DBA Blogs

Grant privileges to add constraints on a table to other users

Tom Kyte - 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

Procedures Output Parameters vs Debug Compile

Tom Kyte - Tue, 2020-09-22 18:26
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.
Categories: DBA Blogs

How to move the Jobs(Present in user_scheduler_jobs table) from one User to another User ,Present in the same DB server

Tom Kyte - Tue, 2020-09-22 18:26
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 !!
Categories: DBA Blogs

ANSI SQL is slower than Oracle SQL notation

Tom Kyte - Tue, 2020-09-22 18:26
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>
Categories: DBA Blogs

Outer join inside decode syntax

Tom Kyte - 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

Oracle 19c Automatic Indexing: CBO Incorrectly Using Auto Indexes Part II ( Sleepwalk)

Richard Foote - Mon, 2020-09-21 02:39
As I discussed in Part I of this series, problems and inconsistencies can appear between what the Automatic Indexing processing thinks will happen with newly created Automatic Indexing and what actually happens in other database sessions. This is because the Automatic Indexing process session uses a much higher degree of Dynamic Sampling (Level=11) than other […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: CBO Incorrectly Using Auto Indexes Part I (Neighborhood Threat)

Richard Foote - Fri, 2020-09-18 03:19
Following on from my previous few posts on “data skew”, I’m now going to look at it from a slightly different perspective, where there is an inherent relationship between columns. The CBO has difficulties in recognising (by default) that some combinations of column values are far more common than other combinations, resulting in incorrect cardinality […]
Categories: DBA Blogs

Need to calculate Age as part of select

Tom Kyte - Thu, 2020-09-17 10:06
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>
Categories: DBA Blogs

Database Wallet

Tom Kyte - Thu, 2020-09-17 10:06
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.
Categories: DBA Blogs

Process in order to estimate how many DBAs are needed to support a project

Tom Kyte - Thu, 2020-09-17 10:06
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
Categories: DBA Blogs

Choice State in AWS Step Functions

Pakistan's First Oracle Blog - Thu, 2020-09-17 02:47

Richly asynchronous server-less applications can be built by using AWS step functions. Choice State in AWS Step Functions is the newest feature which was long awaited.

In simply words, we define steps and their transitions and call it State Machine as a whole. In order to define this state machine, we use Amazon States Language (ASL). ASL is a JSON-based structured language that defines state machines and collections of states that can perform work (Task states), determines which state to transition to next (Choice state), and stops execution on error (Fail state). 

So if the requirement is to add a branching logic like if-then-else or case statement in our state transition, then Choice state comes handy. The choice state introduces various new operators into the ASL and the sky is now limit with the possibilities. Operators for choice state include comparison operators like Is Null, IsString etc, Existence operators like Ispresent, glob wildcards where you match some string and also variable string comparison.

Choice State enables developers to simplify existing definitions or add dynamic behavior within state machine definitions. This makes it easier to orchestrate multiple AWS services to accomplish tasks. Modelling complex workflows with extended logic is now possible with this new feature.

Now one hopes that AWS introduces doing it all graphically instead of dabbling into ASL.

Categories: DBA Blogs

Table vs Index Fragmentation

Tom Kyte - Wed, 2020-09-16 15:46
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. ?
Categories: DBA Blogs

PARALLEL HINT and DML ERROR logging

Tom Kyte - Wed, 2020-09-16 15:46
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.
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs