All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

1
vote
0answers
89 views

Unique constraint violated even though the table is empty

The item_type table is empty, yet on this: INSERT INTO item_type (id, name) VALUES (1, 'PHYSICAL') I get error: Error: ORA-00001: unique constraint (MYDB.SYS_C0013475) violated SQLState: ...
1
vote
0answers
65 views

Can't run procedure on Oracle SQL Developer [closed]

Since yesterday, I have not been unable to run procedure on Oracle SQL Developer. When I click on Run button, progress bar with "Launching" text appears on the right bottom corner of the SQL Developer,...
1
vote
1answer
43 views

How to select all attributes of object type

I am running select query with a function in it. That function returns an object type with 4 attributes and I'd like to select all those attributes. When I try it int this way. It returns just the ...
1
vote
2answers
87 views

How to create database/tablespace/schema and users for quickstart developers on Oracle 12?

Background I need to create a contained development space for a new application. This requires a user account to connect with all permissions, such as create/drop tables and stored procedures. ...
0
votes
0answers
40 views

Install Oracle Instant Client components from Custom option

Installing the Oracle DataBase Instant Client 12c, 4 options are displayed: Instant Client - This installation type installs only the shared libraries required by Oracle Call Interface applications ...
0
votes
2answers
38 views

CASE statement multiple conditions

I've got a simple table below, Select * from message_log where Message = 'BUILD' order by 1; +------------+-------------+ | **Message | Log** | +------------+-------------+ | Build | ...
0
votes
0answers
47 views

Oracle Instance Shutting Down randomly :- ORA-01034 Oracle not Available ORA-27101 Shared Memory Realm does not exist

We are facing a peculiar issue in our development enviornment. Oracle Starts properly and then again gets down due to issue and not able to connect to the Oracle Instance. Here is a Trace from alert ...
0
votes
3answers
90 views

Where is my ORACLE_HOME Windows directory?

I'm trying to find my listener.ora file to edit it, but the docs say it's in the ORACLE_HOME directory, but where is that? I'm running 12.1.0 on Windows Server 2008
1
vote
0answers
53 views

Conditional flow in sqlplus

I have script for SQL plus, which I use to create user and get arguments for username and password. I want the script to do more things when it runs on oracle 12c, but I'm trying to avoid writing ...
1
vote
1answer
63 views

Oracle - execute DML query as different user

I would like to be able to run DML queries (INSERT,UPDATE,DELETE) by executing a procedure on a given schema. Those queries need to change data on different schemas on the same instance. So, basically,...
0
votes
1answer
38 views

SQL Developer auto trace does not show cascading deletes

I'm trying to understand the performance characteristics of SQL statement that does DELETE cascade on 4 linked tables. When I do the auto tracing of the statement it only shows me the DELETE ...
1
vote
1answer
23 views

When would one want to use Oracle transparent data encryption

Oracle TDE features comes with various limitations such as not being able to encrypt columns which are used in foreign key constraints. And probably is available only with enterprise edition which ...
1
vote
2answers
56 views

How to find out why this query is in waiting state?

I have a query that has been several minutes in waiting and is not running at all. In the SQL Developer monitor session view it shows that query has been waiting for 667 seconds? How can I find ...
0
votes
1answer
21 views

Query on Asmlib

I have Oracle Linux running on vmware hosting an Oracle database with ASM (single instance). There isn't any operating system device persistence and therefore whenever my OS reboots, the disk number ...
0
votes
3answers
41 views

Simple PL/SQL code to throw NO_DATA_FOUND exception

In a recent interview I was asked to write a simple PL/SQL code to throw NO_DATA_FOUND exception without actually raising it from the code. I am wondering what could be a simple pl/sql snippet to do ...
1
vote
1answer
27 views

Flat similar rows on few columns - Oracle

I wonder is there any way to flat data like this using sql query maybe you can help me The data looks like this: id name customer_code column1 column2 ----------------------------------------- 1 ...
0
votes
0answers
45 views

Oracle 11g Auditing and Collecting the SQL Text with AUDIT_TRAIL=OS

I'm certainly no Oracle DBA, so please forgive any ignorance on my part. I've done a fair bit of research, but I'm a bit stuck with something at the moment. Hopefully someone can help. I'm currently ...
3
votes
0answers
39 views

Dataguard: preventing logical standby to loose track after flashback database on primary

We have 2 Oracle databases; a primary and a logical standby, configured with Dataguard. Whenever we have an application rollout on the primary, we create a restore point in case anything goes wrong. ...
0
votes
2answers
52 views

Why can I execute this query but not see its execution plan?

Consider the following query: SELECT table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' ||owner ||'.' ||table_name)),'/ROWSET/ROW/C')) AS COUNT FROM ...
0
votes
1answer
25 views

How monitor TEMP space allocation for a particular object type like Clob\Blob

I am new to Oracle and i wanted to know if there is a way to monitor Clob\Blob objects allocation in Oracle Temp Space. With JDBC 4.0, we have clob.free() and blob.free() methods and i want a way to ...
1
vote
1answer
67 views

How can I fix these broken synonyms?

When I run the following query: SELECT S.OWNER AS SYN_OWNER, S.SYNONYM_NAME AS SYN_NAME, S.TABLE_OWNER AS OBJ_OWNER, S.TABLE_NAME AS OBJ_NAME, CASE WHEN O.OWNER IS NULL THEN '...
0
votes
0answers
41 views

Archive and Logging issues in oracle 12c

When I want to enable archive log on oracle till 11g i used alter database add supplemental log data(Primary key,unique) columns In 12c I can run the same on the PDB or on the core. I need the ...
0
votes
0answers
40 views

DBMS_Scheduler is running fine at regular intervals but procedure within scheduled program not getting executed

DBMS_Scheduler is running fine at regular intervals but the procedure within scheduled program is not getting executed. We have created one scheduled program which has PL-SQL block contains stored ...
0
votes
1answer
23 views

Insert data into oracle table with loop?

How to use sql to insert line detail data into header with the output like below. line detail MO_ID SEQ_NO QTY LEVEL 121M 2 3 121M 3 9 125M 2 6 125M 3 7 125M ...
0
votes
2answers
78 views

Oracle EM 11g - query to find out CPU utilization

in OEM 11g, if you see performance, there are graph about cpu utilization, memory utilization, and disk I/O utilization. my question is: what is the script to see CPU-Utilization history ? Thank ...
3
votes
2answers
178 views

Copy Oracle database 10g to 12c

I need to copy an Oracle 10g (10.2.0.4.0) database (150GB) to an Oracle 12c (12.1.0.2.0) database. Both are Standard Editions. What method should I use? Target is on a different server - empty ...
0
votes
1answer
61 views

Oracle equivalent of sp_rename in SQL Server

I am trying to rename a column of multiple tables in Oracle using a cursor over a table that has all the information of other table. In SQL Server I was able to do it correctly using sp_rename. I am ...
0
votes
0answers
31 views

updating entire row with values from other table [duplicate]

I have two tables, namely product and product_bkp. Due to faulty mappings, some of the rows from table product got updated wrongly. Now I want to update them using the backup table product_bkp. I ...
0
votes
1answer
121 views

How to connect to oracle 12c from sqlplus without password in command line

This may seem as weird question: If i have user called demo with password Pass1234 When connecting to oracle 11g I can run sqlplus like this with password in command line: on DB host: C:\> ...
-2
votes
1answer
49 views

Oracle Restoring Database on Existing Database or rename database

I want to change my DB name in Oracle. I am using this command: nid TARGET=SYS/welc0me12$@CSPROD DBNAME=CSGOLD SETNAME=Y After that I am getting an error: Datafile E:\APP\ADMINISTRATOR\ORADATA\...
3
votes
1answer
74 views

Drop 15 days records in monthly partition

I have created a monthly partition on my table like below: PARTITION BY RANGE (PARTITION_KEY) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION MayPartition VALUES LESS THAN (TO_DATE('...
0
votes
1answer
120 views

How to configure sql developer with oracle 12c?

I'm trying to connect sql developer to a fresh installation of oracle: I can connect to sys user via sqlplus I can connect to sys user via sql developer when I select Connection Type "Local\Bequeath"....
0
votes
1answer
53 views

Oracle 12c : Unable to install sample schemata on Windows 10

I've gone through lots of trial and errors in installing the sample schemata for Oracle 12c and still can't get it right. I have a pluggable database named PDB01. I also have an instantclient ...
1
vote
1answer
29 views

Statement specific auditing - Oracle

I have enabled default auditing in one of my database. Oracle 11g. Audit_trail is set to DB, other than that for a particular schema 'test' I have set: audit all on test by access With the above ...
0
votes
1answer
46 views

Mixed mode auditing - 12c

Oracle 12c database comes with default Unified auditing enabled even if we set it to FALSE which has ORA_SECURECONFIG policy enabled by default. Apparently, the audit options for that particular ...
0
votes
2answers
46 views

RMAN Overhead on the database

What is the overhead of RMAN hot backup (full backup including archive logs) being performed during business hours? Does it actually affect the performance of the database? What is the case when ...
1
vote
1answer
46 views

Why doesn't Oracle show my privileges?

I have a user that is able to drop, alter, create tables and indexes. But when I list the privileges for my Oracle user it does not show any CREATE TABLE or other such privileges. How am I able to do ...
0
votes
2answers
79 views

How to export own objects using expdp without special privileges?

As per this post on Oracle forums expdp can be used to export your own schema without any special privileges: It all depends on what you want that user to be able to export/import. If all that ...
1
vote
2answers
91 views

expdp full export not exporting all schemas

On Oracle 12c, fairly new database with APEX installed. I do a expdp \"/ as sysdba\" directory=dp_dir full=y dumpfile=full.dmp logfile=full.log And I am getting just a few tables from SYS and ...
0
votes
1answer
42 views

ORACLE - Trying to return MAX or MIN based on a CASE statement

I'm working on a query to give me either a MAX or a MIN based on whether another column is divisible by 2. SELECT CASE mod(ROLE_TYPE, 2) WHEN 1 THEN MAX(SALARY) ELSE ...
0
votes
0answers
23 views

How to use an external encryption key in an Oracle function?

I am storing People data in an Oracle 11g database. I am implementing symmetric encryption on one sensitive data column in my People table. For this, I created an Encrypt function based on the example ...
0
votes
1answer
45 views

Does Oracle randomly switch execution plans to check if there is a better solution?

I had a talk with an external colleague about oracle "swinging in" its performance. In detail it was about, that oracle optimizes its execution plans for the same query by time. I don't talk about ...
2
votes
2answers
98 views

RMAN DUPLICATE from backup without Target or Catalog: How is the aux instance created?

I am trying to duplicate an Oracle database based on backups to test if they can be used in case of complete system failure. Hence, I want to do this without a connection to the source database. I'm ...
0
votes
0answers
45 views

Using a trigger to check whether a matching record exists

I have a database model set up as in the picture above, modelling a student's enrolment into a unit with an associative entity to facilitate the m:n relationship. I wish to code a trigger that will ...
3
votes
2answers
63 views

Golden Gate realtime replication basic question

I have a task to replicate unidirectional real time data from source database A to target database B. I do not need all the table from A, only few tables and few columns inside those tables. Say 10 ...
0
votes
2answers
31 views

Can multiple Oracle databases share a single parameter file?

I'm a developer working on an application team that supports a system with one production database and 4 test databases (2 Dev, a SIT and UAT environment). The prod database exists on it's own Unix ...
1
vote
1answer
50 views

Oracle index with REGEXP_REPLACE is it a good idea?

I have a table of contacts with 1 million registers and now the user wants to search in it by the telephone. Trying to avoid a fullscan I decided to create a new index in this column, but the trick ...
0
votes
3answers
129 views

Oracle union default sort is weird [closed]

As you know union removes duplicated rows. I thought it removes from the latter ones, but it doesn't. I found out that if there's no order by clause, Oracle sorts the merged dataset by the first ...
1
vote
2answers
61 views

How can I stop Oracle Shutdown command?

I used Shutdown normal, instead of Shutdown immediate. Now I want to stop the process, because its taking too long. How can I do it?
4
votes
2answers
43 views

How to assign a constant to a value on all rows, if that value is adjacent to some value on any row

I have data like this (STATUS, and NAME are the column headers): STATUS NAME ACTIVE A1 INACTIVE A1 INACTIVE B1 INACTIVE B1 I want to include a CUSTOM field that returns a ...