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

How do I import data into oracle 11g r2 using a `.dmp` file and a `.par` file?

How do I import data into a new installation of oracle 11g r2 using a .dmp file and a .par file? Here is what I have done so far, on a Windows 8.1 devbox: 1.) Open command line as administrator ...
1
vote
3answers
86 views

How do I use the result of a CASE statement in a WHERE clause?

When I try to run this SELECT statement, I get this error: ORA-00904: "GPASCORE": invalid identifier. How do I use the result of the gpaScore CASE statement in the WHERE clause? SELECT (CASE grade ...
1
vote
0answers
78 views

Sync oracle DBs using archive log when not connected to each other

I'm trying to synchronize 2 dbs which are not connected to each other, and located in a separated networks. The 'copy db' (aka: standby) can be read only, but still should be active. My intuition ...
5
votes
4answers
572 views

Instance and database (Oracle vs SQL Server)

I have been using Oracle up to now, where: instance -> process + memory database -> physical datafiles Normally one instance maps to one database. Inside a database, there can be many tablespaces ...
1
vote
1answer
87 views

Spool query to file without output on screen

Using spool on Oracle sqlplus correctly sends the results of my queries to a file. But I also see output on the screen. Is it possible to see only the query without output on screen, and result on ...
0
votes
2answers
29 views

How to delete multiple values?

I have 3 identical values: 2814|Venerdì 13 |1980 |Orrore |1:30 |Dvd |Italiano 2815|Venerdì 13 |1980 |Orrore |1:30 |Dvd |Italiano 2816|Venerdì 13 |1980 |Orrore ...
1
vote
1answer
52 views

Normalizing data for joins, dedicated columns, a view or.. something else? [closed]

As an example, i have the following tables USERS, USER_JOBS and USER_HOUSES USER_JOBS keeps a list of jobs for a given user_id USER_HOUSES keeps a list of houses for a given user_id the common use ...
0
votes
1answer
52 views

Oracle hierarchical update (reparent)

I have been reading on Oracle hierarchical queries and all of the documentation/examples seem to be focused on SELECTing. I have a table with parent-child relations and would like to change parents ...
1
vote
2answers
110 views

How to reformat Oracle SQL quadratic-equation solver script?

I was wondering how I might be able to tweak this SQL script: ALTER SESSION SET NLS_NUMERIC_CHARACTERS='. '; SELECT 'Not a quadratic equation.' ans FROM dual WHERE &&A = 0 UNION SELECT 'x = ...
1
vote
1answer
60 views

DBMS_REDEFINITION with WHERE clause

I wanted to know if DBMA_REDEFINITON package allows a WHERE clause to filter contents before migration. I have a partitioned table and wants to copy data and constraint to another table using ...
0
votes
0answers
46 views

Fetching user info from many databases at one go

I have a requirement to fetch all the schema available in 100+ Oracle instances running in RHEL environment. All the schema information such as username, roles assigned and is to be dumped in one ...
0
votes
0answers
66 views

From RAC to Standby RAC With Active Dataguard

I have oracle database 11.2.0.3 with RAC feature , with 2 node on oracle linux 6.4 : ol6-112-rac1 and ol6-112-rac2 I want to create active data guard with RAC feature from this database , these ...
0
votes
0answers
56 views

Can't create a view for other user's table despite having select privilege

I already executed the following commands: grant create view to scott; conn hr/hr; grant select on employees to scott; but: create or replace view company_phone_book as select employee_id emp_id, ...
1
vote
0answers
53 views

Can two transactions share the same SCN?

Do all transactions have a unique SCN? Can two transactions share an SCN? I'm confused by this statement in the Oracle Golden Gate manual: The CSN is cross-checked with the transaction ID ...
-1
votes
1answer
34 views

Can you view the Oracle listener status from OEM?

Is it possible to view/monitor the listener status from Oracle Enterprise Manager (OEM)? If so, how is this done?
1
vote
1answer
76 views

After Update/Insert trigger sometimes fails to do its job

I've got this trigger: CREATE OR REPLACE TRIGGER "DOKCENTRA"."INTDOC_AFTER" AFTER UPDATE OR INSERT ON "DOKCENTRA"."INT_DOC" FOR EACH ROW BEGIN IF UPDATING THEN IF ( :NEW.DOCNUMBER IS ...
0
votes
3answers
58 views

Executing generated script from output

I created a script that generates and outputs the required ALTER SYSTEM KILL SESSION commands that I need to execute. main script: (without all the conditions, for better use here in SE) SELECT ...
1
vote
2answers
87 views

Regarding difference in execution time of queries

These are two explain plans of two different queries that return the same results but at a drastically different speed (280 second vs 1-7 seconds). I want some help knowing what the fast query has so ...
4
votes
2answers
112 views

Selecting from random amount of tables with identical structure

I am programming along the lines of an unfavorable design. I have no influence on the schema because it belongs to a readymade standard software. For simplicity let's say this software stores address ...
0
votes
1answer
124 views

Cannot startup Oracle due to ORA-15041

I'm troubleshooting an Oracle instance that won't startup due to ORA-15041. The trc file shows this error: Failed to create file '+FRA_DATA' (file not accessible?) ORA-19816: WARNING: Files may ...
0
votes
1answer
54 views

how to write code for logon/logoff triggers in oracle?

I have written the code for a logon/logoff triggers. Logon is working but the logoff trigger is created with an error. Please give some suggestions. Logging table: create table logon_off_audit( ...
0
votes
1answer
35 views

Using exception with raise_application_error

I'm developing with Oracle 11g. How can I use a named exception in RAISE_APPLICATION_ERROR like this: DECLARE my_fault EXCEPTION; PRAGMA EXCEPTION_INIT (my_fault, -20000); BEGIN ...
-5
votes
1answer
33 views

what is the query to display below output in oracle. [closed]

select * from (select year,sum(sales) from yqs group by year);
0
votes
1answer
53 views

How to return “NaN” using SQRT function in Oracle SQL?

How to return "NaN" using SQRT function in Oracle SQL? Here is my unsuccessful attempt: SELECT SQRT(4) AS "SQRT of 4", SQRT(0) AS "SQRT of 0", SQRT(-1) AS "SQRT of -1" FROM DUAL; As ...
2
votes
1answer
76 views

aggregate column of nvarchar2 and obtain result in nvarchar2 too

I need to create a view with an aggregate column of nvarchar2(4000) in Oracle 11g. What I have: Table1 with every column of type nvarchar2(4000): Col_Name txt_value X a X ...
0
votes
0answers
104 views

Not so Simple SQL question

I have two tables: create table product ( product_id number primary key, item_name varchar2(128 byte) not null, pricenumber not null, available_from date not null ); create table orders ( order_id ...
0
votes
1answer
55 views

Oracle DB 11g Restore Points

I am posting here on the forum for the first time... Please feel free to point me to the correct section of the website, if "DBA" is the wrong one. So I work as a Business Intelligence Consultant, ...
0
votes
2answers
67 views

Oracle: how to query a table if I know only its (non-default) tablespace?

So. SQLPlus simply gives for my first try (after a little bit of postgresql experience): SQL> SELECT * FROM MYTABLESPACE.MYTABLE; SELECT * FROM MYTABLESPACE.MYTABLE * ERROR at line ...
0
votes
0answers
39 views

Import view definition from Oracle to SQL Server using SSMS data import wizard

As the title says, I'm looking to import the create script from an Oracle view in order to recreate it in SQL Server. I can only access the Oracle database through the SSMS data import wizard. These ...
1
vote
1answer
80 views

How to exclude rows containing null values within columns?

This is probably not the ideal query to retrieve rows containing specified values within a column, but the solution below effectively returns the values specified in a new column-alias but includes ...
1
vote
1answer
85 views

How to retrieve specific characters within rows of database column? [closed]

Which Oracle SQL query is preferred or ideal to return the second, third and fourth positioned characters within rows of a particular column? A SQL statement using the SUBSTR method or some type of ...
1
vote
1answer
50 views

Should we consider “sqlplus” as an Oracle client?

I am a newbie of SQL*Plus and Oracle, and I have 2 questions about them. (1) Executing "sqlplus / as sysdba" command: $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 18 ...
0
votes
1answer
92 views

ORA-00904 error while querying the Oracle database table

I am newbie with oracle DB and I need to List the Number of departments per region in the region table. This is my SQL statement: select r.region_id, count(d.department_id) "Number of ...
1
vote
0answers
78 views

How to automatically fill in column values based on another difference column value

I have function takes two parameters and I want to make this function takes the parameters automatically from table B and add it to specific column in this table. For example, if I have a function ...
1
vote
1answer
42 views

How do I track down wait events for a specific transaction in Oracle Enterprise Manager?

I have an application that makes tens of thousands of calls per day to a stored procedure. Normally this executes in about 50 ms. However, periodically, it takes 2-3 seconds to execute. I have an ...
1
vote
1answer
47 views

Need help to construct query/pl-sql block to get DB table column name and query result as key-value pairs

I have a DB table has more than 50 columns. I am querying this table, it should return only one row any time. as below image from sqldeveloper. here I need to construct query/pl-sql block, to find ...
2
votes
1answer
59 views

Oracle - Connect Via SqlPlus

I have a local installation of Oracle. I want to add the XMLDB package to it. As step 1, I need to connect to it via sqlplus. And I am not able to. The installation has a database called Pm. Here ...
0
votes
0answers
51 views

Oracle session leaks

We use an API made by IBM that appears to not properly close Oracle sessions in certain cases. IBM suggested we file a bug report but it is unlikely that this will be resolved soon, and so we are ...
1
vote
2answers
65 views

Varchar2 datatypes are longer than expected

When I run the following script : create table tbl1 ( col1 varchar2(30), col2 varchar2(50), col3 number(4,2) ); desc tbl1; alter table tbl1 modify (col1 varchar2(35)); desc tbl1; drop table tbl1; ...
1
vote
1answer
55 views

What is surface area in SQL Server [closed]

What is the surface area in SQL Server? or what is surface area in SQL Server consists of? Does Oracle has similar concepts? If yes what is it called?
2
votes
2answers
328 views

How to create Clustered and non clustered index in Oracle?

I dug couple of hours regarding my question and didn't get satisfactory answer. Still I have doubt. I have found the following about Clustered Index: Data is stored in the order of the clustered ...
0
votes
1answer
53 views

How to display the number of days elapsed between two dates denoted in columns-specifically those exceeding 30 days?

Perhaps I'm overthinking this Oracle SQL query, but I'm attempting to display the number of days elapsed between two column data-sets attributed as "order-date" and "ship-date", which will be ...
3
votes
1answer
77 views

date type implicit conversion

When does oracle implicitly converts date type? on what parameters does it depend? I am running the following: SELECT SYSDATE - '01-01-01' FROM DUAL and got : SELECT SYSDATE - '01-01-01' FROM ...
0
votes
1answer
71 views

Help using regular expressions

I have two ORACLE tables. First table: ID l_name f_name belong_to 123 Smith John A Club 456 Jones Mary The Club, Your Club, My Club 789 Moore Sue The Club, A ...
3
votes
3answers
118 views

Restore data from an old rman backup - ORA-01152

I have some trouble restoring some data from an old backup. Original machine was lost and my mission is to restore data. This is not a critical machine, but some data is still useful. Oracle on ...
1
vote
1answer
52 views

Increase logging/tracing on Oracle 12c database

I need to connect TLS-secured-ly to an Oracle 12c (from some random Java program, using JDBC connection) I set up my listener to use TCPS, I made my wallet and everything and still my program is ...
0
votes
2answers
46 views

Ignore white spaces for comparison in unique constraints of Oracle

How can I tell to Oracle DBMS to ignore white spaces while comparing varchar values for Unique Constraints. It means that NAME 1 is exists in a table column with Unique Key, inserting NAME1, causes ...
1
vote
2answers
82 views

Order by taking less time than max() in oracle

I am using oracle db and I am running 2 queries to find the maximum id from an indexed column. 1st query - select max(id) from tableName Explain Plan: Plan hash value: 3791258597 ...
1
vote
2answers
104 views

TNS errors using Oracle instant client

I'm struggling to get my Oracle application working. I'm using the Oracle 11.2.0.4.0 instant client with a C++ application that is compiled using Visual Studio 2010. The machine is running Windows ...
2
votes
2answers
44 views

why have to write c## for user creation in oracle 12c

I have tried to create user accounts in oracle 12c. But it has shown the error: ORA-65096: invalid common user or role name. I have tried the following two user name: BANNER ...