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
1answer
32 views

Oracle database - get two rows (A,B) for every (COD) row

I have a Oracle database and a variable that can have a value for A and a value for B like in this example: cod cat v1 v2 v3 123 A 1 10 11 123 B 0 0 0 111 A 2 3 ...
2
votes
3answers
87 views

How to identify how many instances of Oracle are installed on Linux environment

Last day, I was at one training for Oracle installation and service start & shutdown. There I came to know about some Linux commands to start and shutdown an Oracle instance on a Linux machine. ...
0
votes
0answers
34 views

Possible cause of ORA-00600 on dbms_xplan.display?

I'm trying to see the execution plan information for queries. I can see some statistics after I set autotrace on. Also, I can check from plan_table. However, most tutorials or blogposts suggest ...
2
votes
1answer
81 views

How to refactor this subquery to be written only once?

procedure foo(p1 IN INTEGER, p2 IN INTEGER, ooo out sys_refcursor) is begin open ooo for select T1.* from T1 where T1.x=p1 and ( p2 in (SELECT z from T2 where ...
0
votes
2answers
58 views

Check if a primary key value is referred in any other table

I am using oracle database. Is there a way I can check if the primary key value in one table is being used as a foreign key value (have any associations) in any other table? Example: if I have the ...
5
votes
2answers
192 views

Oracle 12c Express Enterprise Manager webpage does not load

Problem summary When browsing to the Oracle 12c EM Express webinterface, the page times-out when using a URL like: http(s)://hostname:5500/em http(s)://hostname.domain:5500/em When using a url ...
0
votes
1answer
29 views

Can I use the cost of a query to prove its efficiency when there is only a small number of rows?

Can I run EXPLAIN (Oracle) for a query when there is only a few dozen rows in the table and use the cost to prove whether the query is efficient or not ? Can you measure the efficiency of a query ...
0
votes
3answers
121 views

lsnrctl is not working on Ubuntu 14.04

I have managed to install Oracle 11g on Ubuntu 14.04 through lots of pain. I managed to install it in the end but listener does not seem to be working and even responding any command. My ...
0
votes
2answers
35 views

PL/SQL block running without printing any output

I'm trying to exexcute a pl/sql block using dbms_output to obtain a console output but without reason the unique answer by SQL developer is 'Anonymous block finished'. declare v_count integer; ...
0
votes
0answers
33 views

One Listener for two Oracle Versions

I've got an dev machine with Oracle 10 and 11 running (on separate users) So I want to use only one Listener (the new from 11). I deleted the Listener of Oracle10 and created one on Oracle11. After ...
0
votes
0answers
23 views

How to pull sales for every single month based on my query below using ORACLE

So I am working on a report right now that pulls customer information, and sales for a particular date range, who used a PROMOTION CODE. My current query pulls all the data I need and gives me a sum ...
0
votes
1answer
35 views

Package DBMS_REDEFINITION missing from Oracle 11g?

I'm trying to partition an existing table in Oracle and the research I've done seems to say that using the DBMS_REDEFINITION package seems to be the best way to do this. The problem is I doesn't seem ...
0
votes
2answers
46 views

More Efficient Use of Union

Looking for a more efficient way to achieve what my view is doing. This view joins tables to themselves to show where each part in the database is used. In other words if I am a part of a part list ...
0
votes
1answer
30 views

Catching Errors in procedure

I have created procedure for orders table. The procedure checks if the order has shipped, then the customer can returns all/part of the shipped items. Once verified by the procedure and the order has ...
0
votes
0answers
41 views

Oracle BI Administration tool - problem with connection

I would like to create a new repository with OBI Admin Tool but I am stucked at login to DTB with this error: The connection has failed Connection_Type: OCI 10g/11g Data Source Name: ** this is ...
0
votes
1answer
39 views

A weak entity with many to many relationship with its owner SQL

I have this patient table which is an owner entity: CREATE TABLE PATIENT (FILE_NO NUMBER(4) PRIMARY KEY, NAME VARCHAR2(25), SEX VARCHAR2 (7), BLOOD_GROUP VARCHAR2(3), ALLERGIES VARCHAR (30), ...
0
votes
2answers
104 views

How to tune a SQL query?

I have a question about SQL query tuning. How can rewrite that SQL query for better performance? Query: select * from ( select ( select max(rn) from ( select ...
0
votes
1answer
29 views

How to start Oracle enterprise manager on Windows 64 bits [closed]

I have installed Oracle 11g on my pc. It has installed fine and everything went fine. After restating pc, https://localhost:1158/em is not connecting. The listener is ok and running fine. How can ...
0
votes
1answer
66 views

Oracle migration unable to create indexes

I want to move the contents of a database instance to another using the expdp and impdp utilities. The source instance is version 12.1.0.1.0 and the destination is 11.2.0.1.0 so I modified the expdp ...
0
votes
1answer
145 views

Why does Oracle Database Configuration failed on ubuntu?

I'm trying to install Oracle Database 11g Express Edition on my ubuntu server. After running command: sudo service oracle-xe start I get following result after configuration: Starting Oracle Net ...
-1
votes
1answer
176 views

How To Declare Fixed Length String Value In PL/SQL

In PL/SQL strings are declared in two different ways. These ways are fixed-length strings declaration, variable-length strings declaration. If we need to declare a variable-length string, we must ...
1
vote
1answer
232 views

Database gives “ORA-01109: Database not open” error when it IS open

Running Oracle Database 12c on Windows Server 2012 Whenever I attempt to do a log switch (alter system archive log current), I am given the error in the title. Searching online shows the simple ...
0
votes
2answers
48 views

Oracle Materialized View Overloads Database

Summary: Materialized View across DB link eating up too many system resources Question: Is there a way to throttle a materialized view refresh? We have some fairly large tables that we copy from one ...
0
votes
0answers
23 views

Killed wrong session and cannot connect to oracle anymore

I guess I killed the wrong session. Trying to connect with SQL Developer, it says ORA-12505. Search results say you may set wrong SID, but that's not for my case. How can I get my oracle back? My ...
0
votes
2answers
41 views

IF condition in connect command in SQL*Plus

I want to fire SQL queries only when the user is connected to SQL*Plus. In my case even if the connection is not there, the rest of the commands are running in my SQL script. if "connect ...
0
votes
1answer
18 views

SQL*Net message from client Inactive sessions delete

I am trying to kill a session using alter system kill session . Previously the same command used to return ora28 status. However, these days it stopped giving the status. When V$session table is ...
0
votes
2answers
54 views

Best way to update list of rows in oracle database

I need to update a variable list of database rows. The list of the affected ids will be generated by an application, the statement will be executed about 100 times a day (with an always different ...
0
votes
2answers
61 views

UPDATE with Oracle MERGE statement

I have following MERGE statement. Table table_A contains list of ID (cli_id) and column fb_flag. The query inside update the column fb_flag in table_A when cli_id is matching. Which works. However ...
3
votes
3answers
375 views

How to purge an already dropped table?

I dropped a table but didn't purge it. How can I purge it, so I can release the tablespace that the table uses?
0
votes
1answer
15 views

cannot modify a column which maps to a non key-preserved table

Hello everyone am trying to insert data into base tables via a view that joins more than two tables. I have an instead of trigger that should insert the data into the tables, not sure where have gone ...
0
votes
1answer
43 views

ora-04091 table is mutating trigger/function may not see it

I have two tables say tab1, tab2 here tab1 is the parent table of tab2, Both have triggers enabled say tab1_trg, tab2_trg. Also in tab2_trg there is a SELECT on tab1. Now I try to delete some records ...
0
votes
0answers
28 views

Oracle Listener stoped as soon as it started

I started oracle listener. it seemed working well. but I didn't actually. I hope someone could help me with blow ========================================================================= ...
0
votes
0answers
23 views

If a table is partitioned by a single column should that column be included in index definitions?

Elaborate: CREATE TABLE TEST( school_year DATE, B VARCHAR2(12), C VARCHAR2(15), D NUMBER ) And the table is already partitioned on column school_year. Should column school_year be part of ...
0
votes
1answer
48 views

Cannot fix ERROR:ORA-01031: insufficient privileges

I have tried almost every solution on the internet however I still cannot fix this problem. I cannot login as sysdba every time I try SQL>connect /as sysdba I get the error ERROR:ORA-01031: ...
0
votes
2answers
171 views

Can't login ORA-28031: maximum of 148 enabled roles exceeded

I can't login - and receive the error code above. How do I fix this?
1
vote
3answers
77 views

Whether Cold backup method can be used to “migrate oracle DB”?

I have to migrate one database. I have exact server replica. (Same directory structure on target as well as source database) Previously I have performed DB migration using export/import datapump (As ...
1
vote
1answer
23 views

Whether Cold backup method can be used to “migrate oracle DB”?

I have to migrate one database. I have exact server replica. (Same directory structure on target as well as source database) Previously i have performed DB migration using export/import datapump (As ...
2
votes
1answer
158 views

ORA-12546: TNS:permission denied, on Windows through Task Scheduler

I am trying to export all objects of specific oracle user by executing a batch file through Windows Task Scheduler: exp.exe myuser/pass@dbname owner=myuser log=mylog.txt The Oracle 11.2.0.4 x64 and ...
1
vote
2answers
44 views

Sync data between 2 databases Oracle 10g

I have 2 databases DB_LIVE and DB_NEW, both have different credentials. I have a Data Read access on DB_LIVE and I am owner of DB_NEW which is a completely blank and raw db provided to me. I want to ...
-1
votes
1answer
40 views

Oracle - Service Account vs Database Account?

I'm a developer and not a DBA, specifically not an Oracle DBA. I have a Java application that is connecting to a Oracle 11g R2 database. I have an account and schema called "OBR". I am being told the ...
0
votes
0answers
41 views

Need to find DDLs that were executed in a week in Oracle 11g

Is there any way to find the list of DDLs that were executed in a period of time in a particular schema? It should contain all types of DDLs that were executed on any of the tables of the schema. ...
1
vote
2answers
63 views

Testing on Logical Standby Database

Primary DB: 11.2.0.3 Enterprise Standby DB: 12.1.0.2 Enterprise I've found several articles on making changes on a Physical Standby DB, but I've been having difficulty locating how to enable ...
1
vote
2answers
35 views

Fetching recent/historical queries run on a database from an application

I'm getting some database errors from an application related to unique constraints however none of the records have any duplicate values so I want to check what query was run that produced the error. ...
2
votes
1answer
22 views

Could Not Be Accessed Value Of The For Loop Counter In PL/SQL

I try to learn PL/SQL for loops in tutorials. I learn flow of control in a for loops. (And i saw that it is same with most of the other programming languages) Here is the flow of control in a for ...
0
votes
1answer
46 views

ORA-12154 when PLSQL Developer 11 is trying to connect to database

Hi i have troubles when i'm trying to connect my PLSQL Developer to Oracle 11g R2 Express Edition. I created a new folder in c:\Oraclex86, in that folder I installed the oracle 11g R2 Client for x86 ...
2
votes
2answers
88 views

Does any SQL engine have navigation support in CHECK clauses?

Imagine a simple database to track personal spending. The most basic form of the model should let you create any number of accounts and any number of transactions (specifying the spending amount, date ...
1
vote
0answers
36 views

Oracle Table - game like script - need solution to find out the values from current column

I have a little "game". I have the following Oracle Table (a view more exactly), where I have the values for my columns id, cod, val, t1, tall and I need to calculate v1,v2,v3,v1a,v2a,v3a. ID Cod ...
0
votes
1answer
2 views

Creating pluggable database using dbca

I have created a PDB using Database Configuration Assistant.Is there a way I can look at where the data files got created? I did not get the option in DBCA to specify my data file. Thanks in advance
0
votes
2answers
220 views

Oracle database patch update doubts

Oracle Recommended Patches -- Oracle Database (Doc ID 756671.1) says the following 4 patches are recommended to be applied to 11.2.0.4 database. Note:21744343.8 Combo of 11.2.0.4.5 OJVM PSU and ...
1
vote
2answers
84 views

What reasons can lock an Oracle account

There could be a lot of reasons why an Oracle account can be locked. When an account is locked and the users is asking for our help, following could be the possible cause of account lock: A user ...