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
35 views

Database export taking 12 hours and still running

I'm running an export for a company I haven't worked with before. Started export last night at 7:00pm and it's still running 12 hours later. It's an agile 9.3.3 database, oracle 12c. So far the dmp ...
0
votes
1answer
11 views

Oracle restore to a differnet drive

I have the following script for rman to backup the main database and archive logs: connect target sysdba/password@localhost/db run { allocate channel d1 type disk; crosscheck archivelog all; delete ...
0
votes
0answers
18 views

A dbms_scheduler job is not running

I need help with debugging why a certain job is not running in Oracle with the dbms_scheduler. I have many other jobs running under the same user/schema and they work. So, how do I start finding the ...
-1
votes
0answers
17 views

Generate Unique_ID based on few records [closed]

I have rows like below 99 --row1 1 HOST 02 0 14 19 IBN STDLN 6X17AA N --row 2 3 9605944 OTWA1G148Y 0 10 613 SCA Y Y 0 --row 3 99 --row 4 1 HOST 02 1 00 11 IBN STDLN 6X17AA N --row 5 2 9572311 ...
0
votes
0answers
18 views

Oracle: Select values and join result from multiple rows in one with seperat cells

I have to create a select Statement, which bring me a multiple row result in one. For better understanding here my table Id | Name | Typ | Date --------------------------------- 1 | John | ...
0
votes
1answer
23 views

What is the default quota on a tablespace for a given user in oracle if you don't set one?

My environment I'm using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Explanation of the situation I was creating a user with the following command create user ...
0
votes
0answers
28 views

Oracle - partition table by 3 columns

I have, in Oracle database, fact table based upon which reports are generated. Table contains sales data - large volume. Users generating reports are usually using grouping by (sale_day, customer, ...)...
-1
votes
0answers
18 views

Rollback date specific changes for user [closed]

I have updated a few changes some time ago using the commit statement. I now need to roll back the changes. These changes were done using my Oracle user account.
0
votes
1answer
49 views

Script to install Oracle client 12c2 on Redhat linux 7.5

Can someone help me to provide a script which I can use to install the Oracle client as part of the server build or AMI. Below script I was able to pull out from the guide in the Oracle site. But ...
-2
votes
1answer
42 views

ORA-01427: single-row subquery returns more than one row [closed]

Below query is throwing this issue but when i add the condition rownum <=1 it is updating single row , but i want to update the entire table not the single row UPDATE footnote p SET p.tac_id = ...
0
votes
1answer
20 views

Can Oracle SE2 RAC works with only a single instance? Is it recommended?

I've got a client with an Oracle DB 12 SE2 RAC with two instances. One of the instances is giving too much problems. He wants just to remove it, and therefore leaving Oracle RAC running in only with ...
0
votes
1answer
19 views

RMAN - 01009 Error in Oracle 11gR2 [closed]

When I want to copy-paste this script: RUN { SET NEWNAME FOR DATAFILE 1 TO '/u05/NEWDB/app/oracle/oradata/FBDB/system01.dbf'; SET NEWNAME FOR DATAFILE 2 TO '/u05/NEWDB/app/oracle/oradata/FBDB/...
0
votes
1answer
34 views

What could possibly cause a numeric overflow when enqueuing an Oracle AQ message?

We create and enqueue messages with various properties, like this: message := sys.aq$_jms_message.construct(0); message.set_string_property('a', v_a)); ...
1
vote
1answer
23 views

Where does Oracle store message properties for AQ messages?

We create messages with various properties, like this: message := sys.aq$_jms_message.construct(0); message.set_string_property('a', v_a)); message....
-2
votes
1answer
19 views

SUM inside a CASE, retrieving “NOT A UNIQUE GROUP FUNCTION ERROR” error [closed]

I got this query that works but when I put the SUM i get a "Unique group function error": SELECT A.qty, A.order, B.order, CASE WHEN SUM(A.qty) = 0 THEN 0 ...
-1
votes
0answers
43 views

Automating SQL queries in Oracle

I want to automate a SQL query, this query would run weekly and one field needs to change per run. So, for example, the query would be a total count for cars in stock but changing the country each ...
0
votes
0answers
43 views

How to run SQL statement on all databases using OEM?

I have access to Oracle Enterprise Manager Cloud Control 13c and I would like to know how much Memory (RAM) does each instance have allocated. This is achievable by running this simple SQL : SELECT ...
1
vote
2answers
46 views

Index on a partitioned key

we have a list partitioned table. Let partitioned column be c1. We have an index on that column(c1). We are planning to drop the index. Our doubt is that if we drop the index, does it affect the ...
-2
votes
1answer
69 views

Creating new table with multiple values in a column [closed]

I have created a table with only one column Create Table RLID_MARK ( RLID Int ) I want to Add multiple values in Column RLID using insert into RLID_MARK (RLID) values( '43230335' , '20936450' , ...
0
votes
0answers
30 views

Range vs List partitioning for YYYYMM year month field Oracle 12c

I have a table which holds monthly data differentiated by a column year month (YYYYMM, eg 201911). We have been planning to partition the table by month however we are unable to decide between Range ...
0
votes
1answer
40 views

Oracle Can a Dropped View Be Recovered?

I dropped a view 20 minutes ago. Can I recover it? If yes, how?
1
vote
1answer
20 views

Setting NLS_LENGTH_SEMANTICS for session via trigger

I have the following problem with my Oracle 12c database. I have to install a third party application, which needs an Oracle database instance. The supplier demands to set the NLS_LENGTH_SEMANTICS ...
-2
votes
0answers
30 views

Joining Multiple Tables causes Duplication and total price is not calculated properly [closed]

These are the created tables: CREATE TABLE hosins ( insurance_company_insurance_id NUMBER(4) NOT NULL, hospital_hospital_id NUMBER(4) NOT NULL, disscount_rate NUMBER(5,...
0
votes
0answers
53 views

Creating new table from a query results [migrated]

I want to create a table of output from this query,Table is giving USR_ID Kindly help I'm novice to SQL select USR_ID from USR,company@,employee where SK_USR_ID=USR_ID and Assignedto=employeeID and ...
0
votes
1answer
29 views

select subquery condition in where statement

I am getting this error: ORA-06550: line 43, column 16: ORA-00904: "STOCK_BALANCE": invalid identifier ...for the following query: select PARTY_ID, PARTY_NAME, IT_ID, IT_ID ||...
-1
votes
0answers
54 views

Compare sysdate vs date field

I'd like to know how to compare dates with another alternative to: date_field < other_date_field I have a column called SAPInsertDate where the values are like this '19.11.2019 10:40:14' and the ...
2
votes
0answers
37 views

ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

Trying to recover an Oracle 11g database I get this message: ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified These are the steps I am following: SQL> STARTUP NOMOUNT ...
0
votes
2answers
54 views

CSSCAN for the character set change, database version 11.2.0.3.0

According to Document 745809.1, Installing Csscan. While running the "Csminst.sql" we are getting below error SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql create user csmig identified by csmig ...
1
vote
1answer
28 views

RMAN Restore Validate time equals to actual restore time?

I'd like to know if the time to restore the database is equal the time using restore validate database. If not, How can I know how much time the restore will spend?
0
votes
0answers
3 views

Updating one table with values from another table [migrated]

Sorry for this very basic newbie DB question... I have two tables with the same columns but want to replace the values of one of the columns in the first table with the values of the corresponding ...
0
votes
1answer
61 views

ORACLE DMU (database migration assistant for unicode) Tool

Reading the documentation for Oracle DMU tool, when I try the step create a step for DMU Repository tablespace the document says to use the result of the query for initial size. However; I could not ...
0
votes
1answer
44 views

SQL query not displaying a row when one of the variable gets 0 values

I have this query that is set to return a % but when one of the sender_marks in the list values gets 0%, the mark is not displayed with 0 but disappears. SELECT p.SENDER_MARK,ROUND (Removed/...
0
votes
2answers
50 views

Modify TNSNAMES.ORA file to connect to an Oracle Database

I would like to modify TNSNAMES.ORA file to connect to an ORACLE Database and I noticed that on my PC there are 3 different files with name TNSNAMES.ORA files. Which of these three should I modify in ...
0
votes
1answer
42 views

Automatically purge dropped tables in Oracle

I am using an Oracle 18c database in a build system. One of my builds is for a JPA/Hibernate project, which is configured as "create-drop". This means it will create all tables during startup and ...
0
votes
0answers
26 views

Data replication between Oracle and Postgres

Is there a way to replicate the DML changes that happen in a Postgres table into a table in Oracle database? My Postgres database is RDS in AWS environment. So I am unable to use the extension ...
0
votes
1answer
46 views

Monitoring of Oracle Databases

Can I monitor all databases on different hosts with single cloud control? Do I need to install agent on each server? Do I need any license ?
0
votes
0answers
23 views

Import Schema dump into multiple schemas

I have a production schema and for upgrade they have decided to use 3 schema instead of one. For deploying this into production I have to take dump of schema A and import into 3 different schema with ...
0
votes
0answers
25 views

Is there any way to group only syntax errors from exceptions given by oracle server?

Basically the exceptions thrown by oracle are in the form, say for example 'ORA-01756','ORA-00907' etc. I would to know if there is a way to filter out just syntax errors may be something like a list ...
0
votes
1answer
28 views

percentage of count in Oracle

I'm trying to get percentage of payments according to status every day. I found a MySQL query which throws an error at Oracle when I use '=' inside SUM parentheses: "ORA-00907: missing right ...
0
votes
1answer
52 views

Enable archivelog

I'm trying to enable archivelog mode in Oracle database. For that I shutdown the services and used startup mount. Instead of database getting mounted, I'm getting error ORA- 12528 TNS - All instances ...
2
votes
1answer
50 views

alter database backup controlfile vs cp Linux file

We have the following explanation in the documentation: Back up the control file to a binary file (duplicate of existing control file) using the following statement: ALTER DATABASE BACKUP ...
0
votes
2answers
40 views

Detect if the family record is incomplete to avoid partial deletion

MS SQL SERVER and ORACLE. I have a table Acct table that has acct_id and parent_acct_id attribute. Parent_acct_id on children account points to acct_id on parent account. If no parent, then ...
0
votes
1answer
31 views

RMAN log file with date and name in Oracle 11gR2 in Linux

I have a script and I want to create my log file with datetime and with unique name. It's my script: ....... ....... rman target/ LOG='/u01/backup/logs/rman.log' <<EOF ....... ....... ....... ...
0
votes
2answers
31 views

Convert Text Column Web Service Data to Columns

I have a database table which stores web transactions for a fundraising donation page. One of the columns in the table stores a bunch of concatenated data in the format 'data1=value1 data2=value2 ...
0
votes
2answers
64 views

Variables in Oracle Triggers

Context Every few weeks the database guy at work backs up the production database and then several weeks later imports it into the development database. I don't know why it takes so long between ...
0
votes
1answer
28 views

Using of delete obsolete in Oracle 11gR2 RMAN on Linux

I'm using crontab for automatic backup (full backup). It works 2 times in a day (8 AM and 23 PM). In my script I use delete obsolete but I have a question. I want to delete old backups without last 2 ...
0
votes
1answer
47 views

Oracle 11gR2 can I delete datafiles to reduce tablespace?

I have the following doubt, maybe a little silly, if I have a tablespace for example TBS_MYDATA of 155 GB, this tablespace is composed of 5 datafiles of 31GB each, in turn, each of these datafiles ...
0
votes
1answer
31 views

Oracle SQL query - select or rows without all ids in other tables

I need to delete customers if customer not have any accounts or all of accounts not have any operations (no records in id_customer, id_account_sender, id_account_receiver in operation table). example ...
-1
votes
1answer
39 views

How to know needed UNDO_RETENTION for a specified query of long duration? [closed]

I'd like to know whether there is some way to know the undo_retention needed for a specified query with long duration that need keep the consistence. In other words, I'd like to know how long my ...
1
vote
1answer
80 views

SQLcl: set color in sqlprompt (ANSI/VT100 Control sequences)

This is my login.sql SET sqlprompt "_user'@'_connect_identifier > " SET sqlformat ansiconsole SET serveroutput on SET lines 3000 How to set the sqlprompt to color red for Prod (and green color ...