Questions tagged [oracle]

All versions of Oracle database. Add a version-specific tag like oracle-11g-r2 if that context is important in the question. Do not use for Oracle products such as applications or middleware or other database products owned by Oracle, like MySQL or BerkeleyDB.

Filter by
Sorted by
Tagged with
0
votes
0answers
143 views

Oracle SQL query - not exist taking too long

i am trying to fetch the details for those customers who did not shop last year but shopped in current year, i am getting the results, however it is taking too long to execute. is there a better way ...
0
votes
1answer
23 views

Speeding up linking Oracle tables in Microsoft Access

Many people in my office use Oracle tables by linking them in Access (either 2010 or 2016) databases. They always complain about the fact that, when Access is about to present them the list of tables ...
0
votes
0answers
50 views

Is there a way to optimize the query if we are using ROUND function 100 times in a single query

The below query is to compare the last day of the month and select either zero or a value. I am not pasting the entire query as its huge and the i am just pasting the condition which if possible can ...
0
votes
1answer
46 views

Can someone help me with the SQL Server syntax for the my Oracle query

Oracle version: CREATE OR replace PROCEDURE Item_reco_recordset (p_depositid IN NUMBER, p_b1 IN NUMBER, ...
0
votes
1answer
304 views

Oracle SCN_TO_TIMESTAMP (ORA_ROWSCN) fails

Trying to run the following SQL which is basically an exact copy of an example I have seen on multiple sites, showing how to do this (I just change the tablename) SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) ...
0
votes
2answers
30 views

How to catch all the errors happening during the execution of an SQL file containing multiple INSERT INTO statements to insert multiple rows?

I've got a sql file TW_popul.sql containing multiple INSERT statement: INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID) VALUES (32353357, 8.67, 0, 'com....
0
votes
0answers
16 views

Hierarchial query missing result

i have an Oracle hierarchical query that looks like it will answer what i need. The issue i see with it is that the parent 187091 is not shown in the LEVEL_SAMPLE_NUMBER field. How would I modify ...
0
votes
1answer
24 views

SQL query output to a file is failing with error code 9 when the data contains French and English characters with size > 32k

Database: Oracle 12 C I have a MESSAGE table with BODY VARCHAR2(32000), METADATA VARCHAR2(32000). Initially I had a problem when both columns have 32k+32k chars of data. I had fixed it by applying ...
1
vote
1answer
52 views

Run stored procedures in other schemas from stored procedure [closed]

I have a current process: a large procedure in one schema. There is a plan to have A, B1, B2, B3 schemas (B-schemas are identical, could be B-n schemas) in Oracle 12c database. Schemas B1, B2, B3 have ...
0
votes
1answer
30 views

v$ tables not recognized in a sql statement of a shell script

I have .sh script with this block: sqlplus -S / as sysdba <<EOFILE set feedback off set lines 2000 set pages 20000 col db_size for a15 alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:...
0
votes
2answers
46 views

Adding parameters to ORACLE sql case statement [closed]

I have the following sql query SELECT DISTINCT SAMPLE.SAMPLE_NUMBER, LOT.PRODUCT, SAMPLE.LOGIN_DATE, SAMPLE.STATUS as SAMP_STATUS, CASE WHEN SAMPLE.TEXT_ID LIKE '%TOP%' THEN 'TOP' ...
1
vote
1answer
138 views

How to UPDATE/DELETE an Oracle Foreign Table with Foreign Data Wrapper from PostgreSQL?

I need to transfer lots of row from my PostgreSQL database to a remote Oracle DB through the Oracle Foreign Data Wrapper (FDW). The required operation is so far INSERT to a Foreign Table (FT), but now ...
0
votes
0answers
14 views

tracking in odi the package which uses a given mapping

I am trying to find the whole lineage and loading process of a certain table column. To begin with i am locating the mapping used to populate the column. To do this i use: select m.name ...
0
votes
0answers
49 views

Exporting from Oracle Cloud, importing on local database

I am currently experiemting with oracle databases. I have a free tier at Oracle Cloud, with a free instance of autonomous database. As I like to have my backups locally I did an export, basically ...
1
vote
0answers
32 views

What's a process for determining table links?

I need to make several queries to get information out of our Oracle 12c database. I've used oracle SQL Developer to determine primary and foreign key relationships, but how do I link tables when there ...
0
votes
1answer
47 views

Deleting rows from Oracle table user_scheduler_job_run_details

Oracle Database 12c Red Hat Linux 7 I want to delete the last 1000 records on the Oracle table user_scheduler_job_run_details because it is getting too large. How best to delete rows from the Oracle ...
0
votes
0answers
187 views

EM Express page of Oracle 19c is not opening

I have setup the https port for EM express in the CDB database: exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500) However while opening the EM express page https://hostname:5500/em/ the browser is throwing "...
0
votes
2answers
44 views

How to export large table data into different files in oracle?

I'm having a table which contains 10 Lakh lines and need to export into different file which contails 1 Lakh lines each . sample_01.sql ,sample_02.sql and so on How can we take export like this in ...
0
votes
0answers
155 views

Installing Oracle 32 or 64 bit on a Windows Server 2019 to Import data into SQL server and use with Power BI

I am an Oracle noob and really struggling with this concept of 32 and 64 bit business with Oracle. All I really want to do, is import data on a daily basis from an Oracle 12c DW into a local 2019 ...
0
votes
2answers
126 views

What are v$database, SID, ServiceName, istance in Oracle 18c?

I have set up Oracle 18c (18.4) on Windows 10 on a company laptop (localhost should be the same as my company's domain name). Once I've created user SYSTEM with password at the installation I tried to ...
0
votes
1answer
60 views

Querying 500 million records in Oracle view takes forever

I really need professionals help now after wasting a week of time to solve my problem and searching on Stack Overflow. I have a view created on top of two different tables. This view has 500 million ...
0
votes
1answer
68 views

How does an index speed up insertion in a table

How does index speed up insertion in a table? I have an idea how it happens for retrieving the rows and selection of data, but how does it helps in insertion? CREATE TABLE persons( id NUMBER , ...
0
votes
1answer
28 views

Query another schema on a different sid Oracle

I have 3 db's that should be in sync with each other, but there are some differences that I am trying to rectify. We try to stay out of the main schema and do all of our work on a separate user ...
0
votes
1answer
37 views

Extract only single value from multiple rows based on certain condition for same id in Oracle

I'm an Oracle beginner and I'm having troubleshooting the issue below. My table: +-------+------+-------+------ + | ID | GRP | ITDESC| DEN | +-------+------+-------+------ + | 12345 | MANX |...
0
votes
2answers
93 views

Recover database until cancel?

I'm trying to use the command restore database until cancel, but I'm getting the following erro: RMAN-00571: =========================================================== RMAN-00569: =============== ...
0
votes
1answer
31 views

ignored in stored procedure with clause

Hi i'm currently working with oracle that will give me my requered output. I able to make the query but when i'm putting it in stored procedure it gives me error of Error(11,1): PL/SQL: SQL Statement ...
0
votes
1answer
74 views

DBCA and can't init database

SQL> startup; ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0.1/db_1/dbs/initAWSDB.ora' I created a new Oracle 12c ...
0
votes
1answer
19 views

Define block device of an ASM disk

I have a bunch of asmdisks: /dev/oracleasm/disks/DATA01 /dev/oracleasm/disks/DATA02 .. /dev/oracleasm/disks/DATA07 Probably, they were created as: oracleasm createdisk DATA01 /dev/sda1 ...
3
votes
2answers
129 views

MySQL: Using DISTINCT and GROUP BY together?

I saw the following MySQL query that that uses both DISTINCT and GROUP BY together: SELECT DISTINCT user_id, post_id, post_content FROM some_table GROUP BY post_id, user_id HAVING ...
0
votes
0answers
31 views

Can't remove Agent Software from OEM13c

I have "applied" Agent Software in my Oracle Cloud Control 13c. Agent was installed on previous servers and work fine. But now when I want to install it on new configured server, OEM tell me that "...
0
votes
1answer
38 views

Property column in sys.col$ in Oracle

I would like to ask what are the number presenting in Property column in sys.col$ table? When I count distinct, it returns 39 rows. And base on my own research, I know for sure that the property = 0, ...
0
votes
1answer
44 views

Can I use impdp FULL=Y for an Oracle database charset conversion?

I need create a copy of an Oracle 12.2 instance (Oracle Restart) running on an Linux VM. The new instance needs a charset conversion from WE8ISO8859P1 to UTF8 I started by creating a clone of the VM ...
0
votes
2answers
61 views

RMAN Backup to Disk and to Cloud Parallel

I'm currently working on a backup solution for my company. One part of the solution would be to write the backup to disk and also send it to an oracle cloud archive storage for long term storage. In ...
0
votes
0answers
40 views

Prevent reserved words being used for Column Names in Oracle 12c via a trigger

Is there a way to prevent Oracle from permitting reserved words in column names via a trigger? This solution (after I fix the Declare typo) has compilation errors and doesn't work. I want to ensure ...
-1
votes
2answers
41 views

Where to find Dummy Data For Oracle Database [closed]

I don't know if the question is relevant, but if someone can give me some info, I'll be very grateful. I have found this website - here for generating dummy data. I have found some .dump files full of ...
0
votes
1answer
24 views

How to assign variable as column value

I'm new with Oracle. This is something simple that I'm just not getting. I'm in a situation where I need to split a string with values separated by commas (i.e. 25,27) and "decode" it (example: if 25 ...
1
vote
1answer
37 views

Get one row between two duplicate rows with one different value in a column [closed]

I have a table with data entered manually by the customer, there are some data which is duplicated but just a value in one column is different. For example +------------+-------+--------+----------+ |...
0
votes
1answer
45 views

Add a constraint to a column on an Oracle table so that only positive values are allowed

I'm not allowed to drop the table, or delete the columns. The task is to add a constraint to a column possibly by using an ALTER statement so that it disallows any negative numbers. Is this possible? ...
0
votes
1answer
46 views

What exactly mean refused in Oracle listener? [closed]

What are exactly the reasons of the listener refuse some attempt of connection in database? Other question is, the amount of refused connection is lsnrctl services command is all refused connection ...
-1
votes
1answer
88 views

PL/SQL returning Procedure value and putting it in an insert clause [closed]

I'm doing an assignment and the last question states that I call the procedure from question 2 and function from question 3 and then put them in an insert clause(question 4). Write a PL/SQL Code for ...
0
votes
1answer
25 views

Can I do a standard restore/recover using image file backups?

We have a database backed up with incrementally updated image file backups. I understand the typical use case would be to switch to the copies, but would a point in time recovery using restore/...
0
votes
1answer
47 views

get data each day and show 0 if no data

Hi i'm trying to create query that will give me the employee double break count each day. I need to display each day but what i able to display now is the only days that has value. This is the same ...
0
votes
0answers
66 views

Why privileges on SYSTEM

I'm trying LiquiBase, so for testing I created new schema/user LB_TEST create user LB_TEST identified by <password>; grant create session, connect, resource to LB_TEST; alter user LB_TEST ...
1
vote
2answers
68 views

Oracle newly created DB is filled with datatables

I've just created my first database with oracle database configuration assistant. Basically nothing special, database was created successfully. What I don't understand is why are there so many tables ...
0
votes
1answer
28 views

Creating a database link from Oracle Cloud server to remote access database

I work for a small company that currently use Apex to run reports on our accounts system. Oracle is installed on our local server and part of the data is gained via a database link to an access ...
1
vote
1answer
37 views

How to determine if Oracle Java classes are being used

Oracle EE 12.1.0.2 As part of a general clean up of a legacy application prior to a migration I'm trying to determine what objects are actually in use. There are some 230 JAVA class objects in my ...
1
vote
2answers
60 views

Tracing oracle database

I was working with sql server databases for years and I am used to tools like sql server profiler for tracing all queries on particular database. My question is - how to do this same thing with ...
1
vote
1answer
71 views

What is Oracle CPU Time and CPU Usage and why is it important?

I am trying to understand Oracle's CPU Usage and CPU Time metric for its monitoring (such as in the ASH Viewer) for individual queries From my understanding: CPU Time -- the amount of CPU time (in ...
0
votes
1answer
106 views

Oracle query get schema name, size and creation date

I'm having trouble making a query with the following output, Schema Name, Size and Creation Date. I'm new to Oracle and after more than 4 hours of searching I decided to create a question here. I ...
0
votes
0answers
37 views

How to convert join result of column to row in sql Oracle

I have two tables related, with maximum 7 columns in the second table: Table 1: |ID_TABLE_1(PK) | STATE |ID_CAT |DATE | DESCRIPTION| |270184 |1 |3 |09/03/2020 | 7 test ...

1
3 4
5
6 7
136