DBA Blogs

TIME OF ENTRY IN FORM IF RECORD ADDED IN MASTER LIST NOT REFRESH

Tom Kyte - 10 hours 13 min ago
1) I created Employee master FORM from emp table 2) In employee form deptno is the select list and I put button for new department add. It will call dept page form which has property chained = false 3) After adding new dept control goes back to employee form but I can find newly added dept in list 4) test case created on apex.oracle.com [redacted] Page no:-2 model page of employee call from page no 1 new. Page no:- 3 model page of dept call from page no 2 new button next to dept list of value Application export : https://drive.google.com/file/d/1IRFpTMuI-b_ndmbwDjahds8DGk4_zEHQ/view?usp=sharing Test-video : https://drive.google.com/file/d/1_QrRcXngwqQ39r5Bp_4aWttIEG3Hpc-o/view?usp=sharing
Categories: DBA Blogs

Degree of Parallelism PARALLEL vs. PARALLEL (AUTO) Definition and Calculation 19c EE

Tom Kyte - 10 hours 13 min ago
I have two questions both referring to the documentation in Oracle 19c SQL Language Reference PARALLEL hint "For a statement-level PARALLEL hint" section. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-D25225CE-2DCE-4D9F-8E82-401839690A6E 1) PARALLEL and PARALLEL (AUTO) have the exact same definition, but the two examples that immediately follow in the documentation describe different behavior. Namely, PARALLEL will always run in parallel while the preceding definition says a statement with PARALLLEL hint may run serially. Please clarify this seeming contradiction. 2) PARALLEL and PARALLEL (AUTO) state "...the computed degree of parallelism,...", but do not specify the computation. I was suspecting it is DOP = PARALLEL_THREADS_PER_CPU * CPU_COUNT (for single instance), but the definition of PARALLEL (DEFAULT) provides that calculation. It would be misleading for all three PARALLEL, PARALLEL (AUTO), and PARALLEL (DEFAULT) to use the same calculation but only to define the calculation for one. Hence, I now suspect the calculation for PARALLEL and PARALLEL (AUTO) is something else. What is the calculation(s) used for DOP for PARALLEL and PARALLEL (AUTO) hint? Thank you in advance.
Categories: DBA Blogs

find duplicates using a group of columns

Tom Kyte - 10 hours 13 min ago
What query can be used to find the duplicates using a group of columns among which one is a varchar type from a million of records. The varchar type column matching must be case insensitive and space insensitive. In the attached data all rows corresponds to the same name but the group by or aggregate treats them as different record. The grouping should be case insensitive and ignore the spaces. I need to perform the operation on a million records. create table duplicate_filter(name varchar2(50), age integer, salary number) ; insert into duplicate_filter(name, age, salary) values ('John Doe', 20, 100) ; insert into duplicate_filter(name, age, salary) values ('JohnDoe', 20, 100) ; insert into duplicate_filter(name, age, salary) values ('john Doe', 20, 100) ; insert into duplicate_filter(name, age, salary) values ('johndoe', 20, 100) ; insert into duplicate_filter(name, age, salary) values ('john doe', 20, 100) ; commit select name, age, salary , count(1) total from duplicate_filter group by name, age ,salary ; select name, age, salary, count(1) over(partition by name, age, salary) total from duplicate_filter ;
Categories: DBA Blogs

LOB compression

Tom Kyte - 10 hours 13 min ago
I have a DB on ExaCC Gen1 platform with a big LOB segment and when I try to compress it I ran into undo problems. Table size 2,68G LOB segment size 14TB UNDOTBS1 size 544G UNDOTBS2 size 128G Sys@DBname1> show parameter undo_retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 3000000 Sys@DBname1> set timing on Sys@DBname1> ALTER TABLE TABLE.NAME MOVE LOB(bfiledata) STORE AS (TABLESPACE TS2 COMPRESS DEDUPLICATE) online; ALTER TABLE TABLE.NAME MOVE LOB(bfiledata) STORE AS (TABLESPACE TS2 COMPRESS DEDUPLICATE) online * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old Elapsed: 75:11:23.77 I am played around with undo_retention parameter, however this seems not help me in this matter. What else could I try in order to overcome this issue? Additional information: CREATE TABLE "UCMMASTER"."FILESTORAGE" ( "DID" NUMBER(*,0) NOT NULL ENABLE, "DRENDITIONID" VARCHAR2(30 CHAR) NOT NULL ENABLE, "DLASTMODIFIED" TIMESTAMP (6), "DFILESIZE" NUMBER(*,0), "DISDELETED" VARCHAR2(1 CHAR), "BFILEDATA" BLOB, CONSTRAINT "PK_FILESTORAGE" PRIMARY KEY ("DID", "DRENDITIONID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "UCMMASTER" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "UCMMASTER" INMEMORY PRIORITY HIGH MEMCOMPRESS FOR QUERY LOW DISTRIBUTE AUTO DUPLICATE ALL LOB ("BFILEDATA") STORE AS SECUREFILE ( TABLESPACE "UCMMASTER" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) CACHE ENABLE ROW MOVEMENT
Categories: DBA Blogs

Count rows from two different tables

Tom Kyte - 10 hours 13 min ago
question:- I want to calculate the total rows of two different table and then calculate the difference of two rows between these tables:--- I have written two queries select (select count(*) from batchhr.tr_time_mar_intrfce_src ) cnt, (select count(*) from batchhr.tr_time_mar_intrfce_tmp ) empl from batchhr.tr_time_mar_intrfce_src, batchhr.tr_time_mar_intrfce_tmp; select count(*) cnt from batchhr.tr_time_mar_intrfce_src a union all select count(*) emp from batchhr.tr_time_mar_intrfce_tmp b; the second query generating the output as below:- cnt 7736 1942 and the excepted output was: cnt emp 7736 1942 the first query generating the output was: cnt emp 7736 1942 7736 1942 7736 1942 and the excepted output is: cnt emp 7736 1942 please help me
Categories: DBA Blogs

Announcement: Registration Links For Upcoming Webinars Now Open (“Join The Gang”)

Richard Foote - 11 hours 41 min ago
The registration links for my upcoming webinars running in August are now open!!! The price of each webinar is $1,600 AUD. There is a special price of $2,750 AUD if you wish to attend both webinars (just use the Special Combo Price button). (Note: Do NOT use the links if you’re an Australian resident. Please […]
Categories: DBA Blogs

Adding a database to MySQL HeatWave using Auto Parallel Load

DBASolved - Sat, 2022-05-21 14:59

If you are reading this post, more than likely you are either looking at Oracle’s MySQL HeatWave for the first […]

The post Adding a database to MySQL HeatWave using Auto Parallel Load appeared first on DBASolved.

Categories: DBA Blogs

Removing a MySQL Heatwave Cluster

DBASolved - Sat, 2022-05-21 11:36

Recently I’ve been playing with Oracle’s MySQL Database Service (MDS) and testing out the Heatwave option.  Heatwave is very impressive […]

The post Removing a MySQL Heatwave Cluster appeared first on DBASolved.

Categories: DBA Blogs

Announcement: Dates Confirmed For Upcoming Webinars (“Here Today, Gone Tomorrow”)

Richard Foote - Thu, 2022-05-19 01:53
As promised last week, I have now finalised the dates for my upcoming webinars. They will be run as follows: “Oracle Indexing Internals“ Webinar: 18-22 July 2022 (between 09:00 GMT and 13:00 GMT daily) “Oracle Performance Diagnostics and Tuning“ Webinar: 8-11 August 2022 (between 09:00 GMT and 13:00 GMT daily) I’ll detail costings and how […]
Categories: DBA Blogs

Maximum number of Autonomous Databases on an ExaCC Full Rack

Tom Kyte - Tue, 2022-05-17 12:26
How many autonomous db we could have for ExaCC full Rac?
Categories: DBA Blogs

Number of cores before requring RAC licencing for BYOL

Tom Kyte - Tue, 2022-05-17 12:26
The maximum number of cores before needing RAC licensing is per Cluster or for the sum of the Clusters?
Categories: DBA Blogs

Automatic Indexes: Automatically Rebuild Unusable Indexes Part III (“Waiting For The Man”)

Richard Foote - Tue, 2022-05-17 01:43
I’ve previously discussed how Automatic Indexing (AI) will not only create missing indexes, but will also rebuild unusable indexes, be it a Global or Local index. However, all my previous examples have been with Automatic Indexes. How does AI handle unusable indexes in which the indexes were manually created? In my first demo, I’ll start […]
Categories: DBA Blogs

Connecting to MySQL Database Service (MDS) via DBeaver

DBASolved - Mon, 2022-05-16 10:37

With every new service on any cloud platform, the need to make connections is essential .This is the case with […]

The post Connecting to MySQL Database Service (MDS) via DBeaver appeared first on DBASolved.

Categories: DBA Blogs

Maximum number of concurrent sessions in multi instance database

Tom Kyte - Sun, 2022-05-15 23:46
Hi, We have Oracle 12C on 2 instances. I know GV$license can give maximum number of concurrent sessions since start of instances. But is there a way to get maximum we had accessing the database from both together ? Syed
Categories: DBA Blogs

Index on XMLTYPE with XPATH Expression including a XPATH Function

Tom Kyte - Sun, 2022-05-15 23:46
Is there a way to create a index for a xpath that is including a xpath function? Please consider that xmltype index creation fails at oracle livesql.
Categories: DBA Blogs

Cannot Upload git-upload-pack error while cloning Azure Git Repository

Tom Kyte - Sun, 2022-05-15 23:46
Hi, <i>Background and Requirement</i> - I am working for a firm that uses <b>Oracle SQL Developer</b> for Data Cleaning and Manipulation of the data residing in the Oracle Database. We use <b>Microsoft Azure</b> for complete lifecycle management and work planning. So, we decided to use an <b>Azure-hosted cloud Git Repository</b> to host our code remotely and leverage its version control capabilities. We have a Git repository on Azure and are trying to clone the same in Oracle SQL Developer. <i>Steps followed to fulfill the requirement</i> - The following steps were followed for cloning the existing remote repository in Oracle SQL Developer. 1. Go to Teams Menu. 2. Hover over Git. 3. Select Clone option. 4. After the Clone from Git wizard opens up, entered the correct Repository URL, Username and password. 5. We work on a VPN so, I have set the corresponding proxy settings too. When testing the proxy, it gives a success message. (So, no issue in the proxy settings) 6. Click next to fetch remote repository branches. An error appears at this stage. <i>Error that occured</i> - A popup with the title <b>Validation failed</b> and the content as https://<remote repo url>/_git/<remote repo name>:cannot open git-upload-pack appears. <i>Troubleshooting Methods Tried</i> - The following troubleshooting methods have been tried. 1. A lot of troubleshooting methods online suggested that the Local git config has sslVerify set to false could help. Did that, no gain. 2. Tried cloning my personal git repository to test the working of the Git integration on Oracle SQL Developer. It was able to successfully fetch the remote branches. Hence, the error is coming up only while cloning an Azure Repository. 3. Looked at almost all the solution links online, but most of them were for Eclipse. Since both Eclipse and SQL Developer are Java-based applications, I tried doing those resolutions but most of them are regarding SSL Verify setting to false. At the end I have raised the issue here. Hoping to find some help here. Thanks in advance.
Categories: DBA Blogs

FORCE_LOGGING in Autonomous Database

Tom Kyte - Fri, 2022-05-13 16:46
Is FORCE_LOGGING enabled at CDB level in ADB-S? I checked that FORCE_LOGGING was not enabled at the PDB level and the Tablespace level.
Categories: DBA Blogs

Find Circular References in UDTs

Tom Kyte - Fri, 2022-05-13 16:46
The latest Oracle docs has the following design tip: 9.13.5.2 Circular Dependencies Among Types Avoid creating circular dependencies among types. In other words, do not create situations in which a method of type T returns a type T1, which has a method that returns a type T. https://docs.oracle.com/en/database/oracle/oracle-database/21/adobj/design-consideration-tips-and-techniques.html Attached is a link to LiveSQL that exhibits a very simple circular dependency that will likely have issues recompiling during a datapump. Assuming we already have a large application that the compiler is having issues with is there a query we can use to find instances where T1 references T2 and T2 references T1? We would also need to find them a few generations apart (T1 references T2, T2 references T3, T3 references T1). The reference may be either in an attribute (REF) or a subprogram (parameter or return type). This would allow us to find what types may need to be changed to be brought in line with the latest documentation. Thanks in advance for your help.
Categories: DBA Blogs

Select XMLQuery XML parsing error with ampersands

Tom Kyte - Fri, 2022-05-13 16:46
Hi Tom and Team, I guess that this issue is related to the namespace, but as I don't know well this, Could you help me to solve the error running this Select, please? <code>with testTable as ( select xmltype ('<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <ns5:MT_Consulta_pedidos_pagamento xmlns:ns2="urn:Cpy.com/Model/ConsultaPedidosDevolucao/v0" xmlns:ns3="urn:Cpy.com/Model/AtualizaStatusPagamento/v0" xmlns:ns4="urn:Cpy.com/Model/AtualizaItensDevolvidosCancelados/v0" xmlns:ns5="urn:Cpy.com/Model/ConsultaPedidosPagamento/v0"> <codigo_empresa>&Empresa</codigo_empresa> <numero_pedido_venda>&Pedido</numero_pedido_venda> <codigo_loja>&Loja</codigo_loja> <numero_componente>&Componente</numero_componente> </ns5:MT_Consulta_pedidos_pagamento> </soap:Body> </soap:Envelope>' ) xml_val from dual ) select xmlquery('/soap' passing xml_val returning content) as dados from testTable;</code>
Categories: DBA Blogs

Patch Oracle GoldenGate Microservices using RESTful APIs

DBASolved - Fri, 2022-05-13 08:10

In 2017, Oracle introduced the world to Oracle GoldenGate Microservices through the release of Oracle GoldenGate 12c (12.3.0.0.1). Upon the […]

The post Patch Oracle GoldenGate Microservices using RESTful APIs appeared first on DBASolved.

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs