Optimize update in oracle – Stack Overflow in Spanish

How could I further optimize this update query, since I have to update 200,000 records and the oracle session ends and I cannot update anything, not even ROWNUM

UPDATE TMP_IOP_AJUSTE_TRF_RCBD_PSTVS P
SET P.PLAZA = (SELECT T.NOMCAS FROM TMP_IOP_AJUSTE_TRF_RCBD2_TEST T WHERE T.SEQ=P.SEQ)
WHERE P.NUM_DEC IN ('E422') AND P.PLAZA IS NULL AND  ROWNUM <=10000

oracle: odi tracking of the package that uses a certain allocation

I am trying to find the complete lineage and the process of loading a certain table column. For starters, I'm locating the mapping used to fill the column. To do this I use:

select
    m.name mapping_name,
    mr.qualified_name,
    mc.name datastore_alias,
    t.table_name target_table,
    mdl.cod_mod model_code
from snp_mapping m inner join snp_map_comp mc on m.i_mapping = mc.i_owner_mapping
    inner join snp_map_cp cp on mc.i_map_comp = cp.i_owner_map_comp
    inner join snp_map_ref mr on mc.i_map_ref = mr.i_map_ref
    inner join snp_table t on mr.i_ref_id = t.i_table
    inner join snp_model mdl on t.i_mod = mdl.i_mod
where cp.direction = 'O' and --output connection point
    cp.i_map_cp not in
        (select i_start_map_cp from snp_map_conn) --not a starting connection point;

which works great (Topic found here: https://stackoverflow.com/questions/60566000/odi-column-lineage-query/60566807#60566807).

Now I need to find the package that this mapping uses, to be able to investigate the whole loading process. So the question is: are there any queries that can return packages that use defined mapping? I found an SNP_PACKAGES table but I'm not sure if I can get something from there.

How to export data from big tables in different files in Oracle?

I have a table that contains 10 Lakh lines and I need to export to a different file that contains 1 Lakh lines each.

sample_01.sql, sample_02.sql and so on

How can we take export this way in Oracle?

ssis: installation of Oracle 32 or 64 bit on a Windows Server 2019 to import data on a SQL server and use with Power BI

Good morning everyone,

First time posting, so I apologize if I make mistakes.

I'm an Oracle newbie and I'm really struggling with this 32 and 64 bit business concept with Oracle.

All I really want to do is import data daily from an Oracle 12c DW to a local SQL Server 2019 and then plug Power BI on top.

The reason is that we are looking to exit Oracle and enter SQL server and I need PoC.

So far I've looked high and low and can't seem to figure out WHAT TO INSTALL to access WHAT in Oracle.

All I really need, if I understand correctly, is to install an Oracle CLIENT to be able to use SSIS to build my ETL routines. BUT when I install a 32 bit client it says it needs a 64 bit client and when I install a 64 bit client it says it wants an ODAC. I also installed it and still had no success.

It is also difficult for me to understand what folder level enters the PATH in the system environment variables.
It is very complicated compared to SQL server, so you could really use your help in following questions.

  1. Since SSMS is 32-bit, can I install 32-bit oracle client and ODAC? If so, what software do I download and install? I can't tell the difference between xcopy and other versions (and can you tell me how to update the path?)

  2. Do I need to install x64 bit since I am on a 64 bit workstation? If so, again, what software do I download?

My ultimate goal is to be able
– Connect to ORACLE using SQL SERVER SSIS and import data daily through ETL routines
– As a temporary backup, also connect to ORACLE using Power BI.

Any help and advice greatly appreciated.

Thank you

query performance: querying 500 million records in Oracle view takes forever

I really need professional help now after wasting a week of time to solve my problem and search Stack Overflow. I have a view created on top of two different tables. This view has 500 million records.

watch:-

CREATE VIEW VZ.SERVICE_UTILIZATION
AS (
select T0base.SNO as SNO,
    T0base.SERVICE_CALL_DATE as SERVICE_CALL_DATE,
    T1.LOOKUP_VALUE as ORIG_SYS,
    T2.LOOKUP_VALUE as DEST_SYS,
    T0base.MSG_ID as MSG_ID,
    T7.LOOKUP_VALUE as SERVICE_NAMESPACE,
    T4.LOOKUP_VALUE as OPERATION_ACCESSED,
    T0base.RESPONSE_TIME as SERVICE_PROVIDER_TIME,
    T5.LOOKUP_VALUE as SERVICE_END_POINT,
    T0base.SLA_TIME as SLA_TIME,
    T0base.SUCCESS as SUCCESS,
    T8.LOOKUP_VALUE as PERSPECTIVE,
    T0base.CREATION_DTM as CREATION_DTM,
    T3.LOOKUP_VALUE as SERVICE_TYPE,
    T0base.PROCESS_ID as PROCESS_ID,
    T0base.ACTOR as ACTOR,
    T6.LOOKUP_VALUE as CLIENT_END_POINT,
    T0base.SERVICE_PROVIDER_TIME_TYPE as SERVICE_PROVIDER_TIME_TYPE,
    T0base.PARENT_PROCESS_ID as PARENT_PROCESS_ID,
    T0base.ORIG_SYS_NO as ORIG_SYS_NO,
    T0base.DEST_SYS_NO as DEST_SYS_NO,
    T0base.SERVICE_TYPE_NO as SERVICE_TYPE_NO,
    T0base.OPERATION_NO as OPERATION_ACCESSED_NO,
    T0base.SERVICE_END_POINT_NO as SERVICE_END_POINT_NO,
    T0base.CLIENT_END_POINT_NO as CLIENT_END_POINT_NO,
    T0base.NAMESPACE_NO as NAMESPACE_NO,
    T0base.SERVICEBUSPROCESSID as SERVICEBUSPROCESSID,
    T0base.TRANSACTIONID as TRANSACTIONID,
    T0base.PARTNERMACHINE as PARTNERMACHINE,
    T0base.PARTNERCONNECTIVITYTIME as PARTNERCONNECTIVITYTIME,
    T0base.DATASIZE as DATASIZE
from t_vz_service_utilization T0base
  left join t_vz_lookup T1
    on (T0base.ORIG_SYS_NO = T1.SNO )
  left join t_vz_lookup T2
    on (T0base.DEST_SYS_NO = T2.SNO )
  left join t_vz_lookup T3
    on (T0base.SERVICE_TYPE_NO = T3.SNO )
  left join t_vz_lookup T4
    on (T0base.OPERATION_NO = T4.SNO )
  left join t_vz_lookup T5
    on (T0base.SERVICE_END_POINT_NO = T5.SNO )
  left join t_vz_lookup T6
    on (T0base.CLIENT_END_POINT_NO = T6.SNO )
  left join t_vz_lookup T7
    on (T0base.NAMESPACE_NO = T7.SNO )
  left join t_vz_lookup T8
    on (T0base.PERSPECTIVE_NO = T8.SNO )
)

Now, I'm looking at the different values ​​of this view

SELECT /*+ FULL(su1) PARALLEL(a, 8) */ 
               DISTINCT su1.orig_sys AS orig_sys, 
                        su1.dest_sys AS dest_sys, 
                        su1.operation_accessed AS operation, 
                        su1.service_namespace AS namespace 
          FROM service_utilization su1;

This query operation takes forever to execute. Also, I'm assuming the indexes on the underlying tables don't work when querying the views. I am looking for another alternative to speed up the performance of my query when dealing with views that have over 500 million records.

Note: – Materialized view is not an option for me.

oracle: what license should i use for SQL Server as a standalone developer?

I have seen that for SQL Server, there is a developer license that is free.
Apparently it can be used for all environments that are for development or testing, but not for production environments or for use with production data.

What I want to know is what license would I need as a freelance developer to be able to use Sql Server Management Studio to connect to a production server (as well as development / test servers of course) from my client. My client would be the one who needs the standard or enterprise license for their production server, but what about me as a developer?
What license do I need and for what price?
Or, if the license is expensive, what software could you use that is free and performs the same functionality (globally) as Sql Server Management Studio?

Also, comparatively, what is the software used for Oracle databases and what are the required licenses (and their prices)?

oracle: when I do a tnsping this does not respond or takes a long time to respond

I use an application to connect with tnsnames to my oracle, so when I try to connect this takes a long time or does not connect, when I make a connection it is the same as it takes a long time to display fine with 1009274646 ms, I tried to configure my listener again using NET CONFIGURATION WIZARD, but every time I try to test the connection it says I DO NOT HAVE ANY LISTENER, even if I am ready to configure it, sometimes I don't even respond from NETWORK CONFIGURATION WIZARD. I even try to install the Oracle client, but it is the same behavior. I don't know what happened two days ago, everything was working fine. Any useful advice please?

enter the image description here
enter the image description here

oracle – Recover database until cancel?

I am trying to use the command restore database until cancel, but I get the following error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "cancel": expecting one of: "scn, sequence, time"
RMAN-01007: at line 1 column 24 file: standard input

Is this command deprecated?

My version of Oracle database is 11g R2

node.js – Run oracle package / procedure with nodejs

Good afternoon, I have a procedure configured to run on the node backend (LOOPBAKC framework)

I am defining execution as follows:

         let sql = `
            VARIABLE PSEQUENCECONFLITO NUMBER;
            EXEC PGPOBDOFALHASAIDA.SPGRAVARFALHASAIDA('${filters.a}',${filters.b},${filters.c},'${moment(minDate).format('DD/MM/YYYY')}','${moment(maxDate).format('DD/MM/YYYY')}','${filters.d}',${filters.e},'${moment(minDateEvent).format('DD/MM/YYYY')}','${moment(maxDateEvent).format('DD/MM/YYYY')}',:PSEQUENCECONFLITO);
                 `
        let ds = Ftbdofalhaoper.dataSource;

        ds.connector.execute(sql, function (err, data) {
            if (err) {
                console.log("Error:", err);
            } else {
                console.log("data:", data);
            }
        });

But I always get the following error:

(Error: ORA-00900: invalid SQL statement) { errorNum: 900, offset: 9 }

I have tried other ways, returning the same error:

let sql = `
        VARIABLE PSEQUENCECONFLITO NUMBER;
        CALL PGPOBDOFALHASAIDA.SPGRAVARFALHASAIDA('${filters.a}',${filters.b},${filters.c},'${moment(minDate).format('DD/MM/YYYY')}','${moment(maxDate).format('DD/MM/YYYY')}','${filters.d}',${filters.e},'${moment(minDateEvent).format('DD/MM/YYYY')}','${moment(maxDateEvent).format('DD/MM/YYYY')}',:PSEQUENCECONFLITO);
        `

Anyone with a solution to help me?

P.S:
If I run the same command in SqlDeveloper, the procedure works perfectly.

Procedimento PL/SQL concluído com sucesso.

oracle – Define block device for an ASM disk

New versions of ASMLib support for oracleasm querydisk -p DATA01 which shows the underlying physical disk.

oracleasm-querydisk

Example:

# oracleasm querydisk -p SSDDATA1
Disk "SSDDATA1" is a valid ASM disk
/dev/sdl1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/sdar1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/sdbn1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/sddf1: LABEL="SSDDATA1" TYPE="oracleasm"
/dev/mapper/mpathqp1: LABEL="SSDDATA1" TYPE="oracleasm"
#

Earlier versions may not have the -p option and show only the largest and smallest numbers you can use to find the physical disk:

Assignment of ASM disks to physical devices

Example:

(root@orcldb2 ~)# /etc/init.d/oracleasm querydisk VOL1
Disk "VOL1" is a valid ASM disk on device (8, 97)

(root@orcldb2 ~)# ls -l /dev | grep 8, | grep 97
brw-rw----   1 root disk     8,      81 Nov  4 13:02 sdg1

This example that I copied from the previous blog post seems to be not entirely accurate (grep 97 returns the minor number 81?), however the concept works.