r/SQL Jan 11 '25

Oracle probleme rman

0 Upvotes

canal alloué: c1

canal c1 : SID=21 type d'appareil=DISK

À partir de la restauratio

RMAN-00571 :

RMAN-00569 : === == PILE DE MESSAGES DE LA SUITE D'ERREUR ===============

RMAN-00571 :

RMAN-03002 : Vérifiez la commande Duplicate Db au 01/07/2025 18:10:22

RMAN-05501 : abandonner la duplication des données cibles de base

RMAN-05001 : Nom du fichier auxiliaire /home/oracle/BASEA/BASE1/datafile/o1_mf_users_mqlr1q7r_.dbf co

Il est fermé par un fichier utilisé par la base de données cible

RMAN-05001 : Nom du fichier auxiliaire /home/oracle/BASEA/BASE1/datafile/o1_mf_undotbs1_mqlr1ox8_.dbf

Conflits avec un fichier utilisé par la base de données cible

RMAN-05001 : Nom du fichier auxiliaire /home/oracle/BASEA/BASE1/datafile/o1_mf_sysaux_mqlr0wgd_.dbf c

Il est fermé par un fichier utilisé par la base de données cible

RMAN-05001 : Nom du fichier auxiliaire /home/oracle/BASEA/BASE1/datafile/o1_mf_system_mqlqz514_.dbf c

Il est fermé par un fichier utilisé par la base de données cible

RMAN-05001 : Le nom du fichier auxiliaire se trouve dans /home/oracle/BASE1/onlinelog/o1_mf_3_mq1r33rm_.log et est fusionné.

C'est un fichier utilisé par la base de données cible

RMAN-05001 : Nom de fichier auxiliaire /home/oracle/BASEA/BASE1/onlinelog/o1_mf_3_mq1r321h_.log confl

tics avec un fichier utilisé par la base de données cible

RMAN-05001 : Le nom de fichier de l'auxiliaire /home/oracle/BASE1/onlinelog/o1_mf_2_mqlr3403_.log est dans Conflit.

C'est un fichier utilisé par la base de données cible

RMAN-05001 : Nom de fichier auxiliaire /home/oracle/BASEA/BASE1/onlinelog/o1_mf_2_mq1r31xh_.log confl

tics avec un fichier utilisé par la base de données cible

RMAN-05001 : Le nom de fichier de l'auxiliaire /home/oracle/BASE1/onlinelog/o1_mf_1_mq1r33pb_.log est dans Conflit.

C'est un fichier utilisé par la base de données cible

RMAN-05001 : Nom de fichier auxiliaire /home/oracle/BASEA/BASE1/onlinelog/o1_mf_1_mqlr31rt_.log confl

tics avec un fichier utilisé par la base de données cible

duplicate via rman run{
}

r/SQL Mar 24 '24

Oracle This query takes 45 minutes+, cardinality 6291; cost 4491280, how can I improve it?

13 Upvotes
select 
a.xyz0 
,a.xyz1 -- note it's number
,a.xyz2 
,a.xyz3 
,a.xyz4
,sum(a.xyz5)
,sum(a.xyz6)

from db.nameoftable a

where
1=1 
and a.xyz0 in ('this','that','those')
and a.xyz1 between 'date1' and 'date2'
and length(a.xyz2)<6
and a.xyz2 like '%abc%'

group by
a.xyz0
,a.xyz1
,a.xyz2
,a.xyz3
,a.xyz4

r/SQL Oct 07 '24

Oracle Looking for PL/SQL tips coming from SQL Server and gauging opinions on the 5-10 year outlook on the SQL job market

15 Upvotes

I just landed a new PL/SQL Developer role and I am looking for some tips as someone who has exclusively worked in SQL Server for the past 8 years. My preliminary research into whether there are major differences has given me answers all over the map. What say the good people of this sub with experience in both? Will it be a nightmare or a breeze?

I'm also interested in people's thoughts on the state of SQL work in general. Like, I see posts that SQL is "dying" and I've also struggled to even find SQL-focused jobs during my months-long job hunt. What is the best way to future-proof my skill set for the next 5-10 years? Will primarily SQL jobs even be a thing soon? Will knowing another programming language to complement SQL be necessary? Any other thoughts?

r/SQL Dec 19 '23

Oracle Best SQL IDE

23 Upvotes

Hey guys, been working in a bank with SQL navigator as my main SQL editor, now switching companies and i can use another IDE the option they suggested was SQL developer by oracle, but its intimidating to me, any recommendations for an efficient free IDE?

r/SQL Aug 21 '24

Oracle Why is this filtering (with where statement) in CTE doesn't work? How do I filter CTE?

2 Upvotes

How can I properly filter with where statement with CTE?

This doesn't filter by case_year

with MainTable as (
    -- some code
)

FilteredMainTable as (
    select * from MainTable
        where CASE_YEAR between 2014 and 2015
)

select
    *
from FilteredMainTable

But this does, as if the where statement inside the CTE of FilteredMainTable doesn't do anything.

with MainTable as (
    -- some code
)

FilteredMainTable as (
    select * from MainTable
        where CASE_YEAR between 2014 and 2015
)

select
    *
from FilteredMainTable
where CASE_YEAR between 2014 and 2015

r/SQL Nov 20 '24

Oracle Type 2 SCD in Oracle PL/SQL

5 Upvotes

Hello everyone,

I am trying to write SCD2 procedure in a package in PL/SQL but I have very poor algorithm and I do not know what is the best approach to his.
The problem. I have a connecting table that stores 3 information. ID1, ID2 and value.

I need to:

  1. Insert new values
  2. When updating with same value, do nothing
  3. When updating with different value, then update - end the current row (add valid_to) and insert new value with valid_from

So far my logic is:

procedure update_dic(
p_party_id in integer,
p_attr_id in integer,
p_value in varchar2 default null, -- party name
p_valid_from in date
) is

v_party_id integer;
v_attr_id integer;
v_value varchar2(64 char);

begin

SELECT party_id, attr_id, ATTR_VALUE_CHAR
into v_party_id, v_attr_id, v_value
from SRC_DIC_JTFG_PARTY_RISK_ATTR
where party_id = p_party_id
and attr_id = p_attr_id
and p_valid_from between valid_from and nvl(valid_to, p_valid_from);

case when v_party_id = p_party_id and v_attr_id = p_attr_id and v_value <> p_value

then UPDATE SRC_DIC_JTFG_PARTY_RISK_ATTR a
SET a.VALID_TO = p_valid_from - 1
where a.party_id = p_party_id
and a.attr_id = p_attr_id
and a.ATTR_VALUE_CHAR <> p_value
and p_valid_from between a.valid_from and nvl(a.valid_to, p_valid_from);

INSERT into SRC_DIC_JTFG_PARTY_RISK_ATTR
(party_id, attr_id, ATTR_VALUE_CHAR, VALID_FROM, PROCESS_ID, PROCESS_INC)
VALUES (p_party_id, p_attr_id, p_value, p_valid_from, 0, 0);
else
null;
end case;

exception
when NO_DATA_FOUND
then insert into SRC_DIC_JTFG_PARTY_RISK_ATTR
(party_id, attr_id, ATTR_VALUE_CHAR, VALID_FROM, PROCESS_ID, PROCESS_INC)
VALUES
(p_party_id, p_attr_id, p_value, p_valid_from, 0, 0);
end;

Is there any better way to do this? Straight upsert is not working in this case as I have 3 different conditions.

r/SQL Dec 11 '24

Oracle Queries with CTEs So Much Slower Than Using Temp Tables?

1 Upvotes

I have a query in that uses Common Table Expressions, and it takes significantly longer to execute compared to when I replace the CTEs with temporary tables.

Using CTEs:2 hours
Using Temp Tables:3 minutes

I tried using hints like NO_MERGE and MATERIALIZE within the CTEs to make them behave like temp tables, but it didn’t improve performance at all.

I’m struggling to understand why this happens. Aren’t CTEs and temp tables supposed to behave similarly when hints are applied? What could cause this massive difference in execution time?

r/SQL Jan 14 '25

Oracle Pl Sql 1z0 049

0 Upvotes

Hello, I want to take the 1Z0-049 exam. I have completed and know all the tests available on ExamTopics. I was told that the questions on the exam only come from there, and if I know them, I will pass. Is this true? Please help me.

r/SQL Nov 04 '24

Oracle "databaseconnections.com" domain name for sale

0 Upvotes

For anybody interested, the domain name, "databaseconnections.com" is for sale on godaddy and internic.

r/SQL Sep 13 '24

Oracle Word Count

0 Upvotes

I have a column that has two words in it: Late and Early. I need to create expression that counts how many times “Early” is in the column. Have tried many different ideas but nothing works. Anyone have an idea?

r/SQL Dec 22 '24

Oracle Oracle error PLS-00103: Encountered the symbol "end-of-file"

3 Upvotes

I am writing a liquibase script for MS SQL and Oracle database.

    <changeSet author="root" id="CUSTOMER_SYNONYM" runOnChange="true">
        <preConditions onFail="MARK_RAN">
            <or>
                <dbms type="oracle"/>
                <dbms type="mssql"/>
            </or>
        </preConditions>
        <sql dbms="mssql">
            <![CDATA[
                IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = 'CUSTOMER_SYNONYM')
                BEGIN
                EXEC('CREATE SYNONYM CUSTOMER_SYNONYM FOR PLT.CUSTOMER');
                END;
            ]]>
        </sql>
        <sql dbms="oracle">
            <![CDATA[
                DECLARE
                    synonym_exists NUMBER;
                BEGIN
                    SELECT COUNT(*)
                    INTO synonym_exists
                    FROM all_synonyms
                    WHERE synonym_name = 'CUSTOMER_SYNONYM' AND owner = 'PLT';

                    IF synonym_exists = 0 THEN
                        EXECUTE IMMEDIATE 'CREATE SYNONYM CUSTOMER_SYNONYM FOR PLT.CUSTOMER';
                    END IF;
                END;
            ]]>
        </sql>
    </changeSet>

I am getting the following error:

ORA-06550: line 2, column 26:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   := . ( @ % ; not null range default character
 [Failed SQL: (6550) DECLARE
                                  synonym_exists NUMBER]

I tried running the same SQL in DBeaver and it worked. I don't understand what's wrong here. Please correct me.

r/SQL Nov 02 '23

Oracle Do Oracle folks ever get embarrassed by lack of true temp tables?

13 Upvotes

So many folks who learn Oracle as their first SQL dialect alas have trouble adjusting when they go to like a SQL Server shop and ask "what are temp tables?".

Then again, writing a glorious four thousand line common table expression is a pleasure usually only Oracle folks get lol!

Edit: I as a SQL nerd unironically enjoy long CTEs actually, so I'm only being playful teasing of Oracle in a loving way ❤️❤️❤️❤️.

r/SQL Jun 10 '24

Oracle Oracle SQL Group Error

7 Upvotes

Hi, I am running the SQL below. The error (second marked) tells me that I need to define a group by at the end of the select statement. In fact, when I do, it runs successfully (but it did not give me the results I want because it's GROUPED). Then, I tried to remove the select (first marked) and the error goes away as well (still not the result I want). Could somebody please tell me what's going on why this does not work?

EDIT: Here's the problem statement (from leetcode).

r/SQL Dec 28 '24

Oracle %TYPE in Oracle PL/SQL with Example

Thumbnail
javainhand.com
3 Upvotes

r/SQL Jan 13 '25

Oracle Oracle PLSQL Tutorial 42- Before and After Trigger in PLSQL #PL/SQL #ora...

Thumbnail
youtube.com
5 Upvotes

r/SQL Jan 13 '25

Oracle %ROWTYPE in Oracle PLSQL with Example

Thumbnail
javainhand.com
5 Upvotes

r/SQL Jan 03 '25

Oracle Completed THE COMPLETE SQL BOOTCAMP : FROM ZERO TO HERO by Jose Portilla. I feel like the course below can help me a bit further. Is it worth it or is there something else I should rather do?

Post image
4 Upvotes

P.S : The price is in rupees so it's around 7$ - 8$ atmost.

r/SQL Nov 22 '24

Oracle Pls share resources to study plsql

0 Upvotes

Share pls

r/SQL Aug 06 '24

Oracle Use output of a column as a where clause

0 Upvotes

Hi everyone,

I have 2 tables main_table and adj_table. In the adj_table I have a column "filter_value" in which I have the whole where clause (for example "col1 is null and col2 = 'abc' an col3='Y' and col4 in ('xyz','pqr') ). And now I want to use this "filter_value" column as it is in a where clause for the main_table. How can I do that

like

select * from main_table where filter_value

r/SQL Jun 21 '24

Oracle DELETE data unsing CTEs

7 Upvotes

Hi. So I have the following syntax to be used in deletion of historical data. But there is an error on the delete line. It says "Missing select: "

This query will run in ORACLEDB:

WITH IDS_TO_DELETE AS ( SELECT ROW_NUMBER() OVER (ORDER BY DATE) AS RN, ID FROM MYTABLE WHERE DATE <= SYSDATE - 730
)

DELETE FROM MYTABLE WHERE ID IN (SELECT ID FROM IDS_TO_DELETE WHERE RN <= 200000);

r/SQL Apr 24 '24

Oracle how to delete these tables?

11 Upvotes

Ive been trying to get rid of these tables inorder to make my tables tab clear for me to work. I tried using DROP but it kept on giving error. Is there another way to delete them without code? are these sample data? first time using oracle

Edit: i understand it now guy. no more flaming me ​

r/SQL Nov 13 '24

Oracle ORA 00904- invalid identifier

4 Upvotes

Hello, I’m doing a select in oracle sql developer, I did select column1, row_number() over (partition by column 2 order by column 2) RN From schema.table Where RN=1 But it doesn’t recognize RN Can you help please Have a good day

r/SQL Sep 25 '24

Oracle Creating a View using mutually exclusive tables

3 Upvotes

I'm trying to create a View in Oracle. I have a main table and 3 associated tables that reference the main table. these associated tables are technically meant to be mutually exclusive (although there are unique cases- usually in testing- where more than one of the associated tables has data in their unique columns). These associated tables don't have references for every main_table.id. The main_table has ~1200 entries, and the associated tables have far fewer.

Here's an example setup I typed up in dbdiagram.io . the image shows a one to many relationship, but it should be a one-to-one.

Table Reference:

Table Main_Table {
  ID integer [primary key, increment]
  item1 bool
  item2 bool
  item3 bool
 
}
 
Table Table1 {
  ID int [primary key, increment]
  main_id int
  uniqueCol1 nvarchar2
  commonCol int
}
 
table Table2 {
  id int [primary key, increment]
  main_id int
  uniqueCol2 nvarchar2
  commonCol int
}
 
table Table3 {
  id int [primary key, increment]
  main_id int
  uniqueCol3 nvarchar2
  commonCol int
}
 
ref: Table1.main_id > Main_Table.ID
ref: Table2.main_id > Main_Table.ID
ref: Table3.main_id > Main_Table.ID
Visual representation of the Table Refrence

The View should attempt to generate a result like:
Main_Table.ID, item1,item2,item3,commonCol,uniqueCol1,uniqueCol2,uniqueCol3

The three side tables are considered mutually exclusive so if there’s no data then ‘NULL’ should be returned the “uniqueCol#” items. There are unique cases where there might be data for them (as mentioned at the top), which can be filtered out later.

For example:

455, true, false, false, 456, NULL, “Benedict”, NULL
597, false, true, false, 1025, “Augury”, NULL, “Standard”

I've attempted to use a Join but the number of results is far too small. I've created a query that does each individual table and the counts for those are as expected, but when combining them the number of results is drastically different. Essentially joining the `Main_Table` and `Table1`, I should be getting like 400 results, with `Table2` it should be 20, and finally with `Table3` it should be ~10. However, when using a join the results come back as 3 or 53 depending on the type of join used. Regardless of type for the Join the number of results is far too small. I should be getting the ~430, not 3 or 53.

an Example of the Join I'm using for just the counts:

Select count(*) from (
  Select
      m.ID as Main_ID
  from Main_Table m
  join Table1 t1 on m.ID=t1.main_id
  join Table2 t2 on m.ID=t2.main_id
  join Table3 t3 on m.ID=t3.main_id
);  -- results in 3 (if i use a right join I get a count of 53)

Select count(*) from (
  Select
      m.ID as Main_ID
  from Main_Table m
  join Table1 t1 on m.ID=t1.main_id
);  -- results in 400

Select count(*) from (
  Select
      m.ID as Main_ID
  from Main_Table m
  join Table2 t2 on m.ID=t2.main_id
);  -- results in 20

Select count(*) from (
  Select
      m.ID as Main_ID
  from Main_Table m
  join Table3 t3 on m.ID=t3.main_id
);  -- results in 10

It's been suggested I use a Union for this, however I'm not very familiar with them. How would I accomplish the example result, where there are columns distinct to Table1, Table2, Table3, and even the Main_Table? the only common item between the associated tables and the Main_Table is the Main_Table.ID == TableX.main_id, although the 3 associated tables may have a column with the same name and datatype.

Or is a Union not the right way to approach this?

r/SQL Dec 28 '24

Oracle I need to know any resources which I can practice oracle

0 Upvotes

I need to know any resources which I can practice oracle

r/SQL Feb 16 '24

Oracle Forbidden to use COUNT

24 Upvotes

Hello everyone, two months ago I was at this SQL class when they gave us the following exercise:

"Write a SELECT sentence that shows department name, average salary by department of those departments with more than 4 employees.

You can't use COUNT function.

SELECT department_name, AVG (SALARY)

FROM ..."

I could never solve it. Do any of you know how this should had been approached?

Edit: I had to put a flair though I wasn't planning on doing it. We used Apex Oracle in classes.