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

-2
votes
1answer
101 views

Display top 5 sales and bottom 5 sales in the same query

I want to display the top 5 sales and the bottom 5 sales in the same query in Oracle, not in PL/SQL.
0
votes
1answer
44 views

Changing own user permissions with Oracle SQL not being the DBA

I have been making changes to a couple of schemas of different environments (they belong to the same application). Sometimes I'll want to copy data from one environment to the other, and sometimes ...
0
votes
1answer
79 views

Compare two databases and get the differences

I want to between two databases and get the differences: columns: in the first table the column name is varchar2(12), in the second table the column name is varchar2(20). Or I have an additional ...
0
votes
1answer
65 views

Migrating database from oracle 11.2.0.03 to 11.2.0.04. [closed]

I have been assigned task of migrating our oracle database from 11.2.0.03 to 11.2.0.04 version. Being new to oracle, I have list down all activities I need to carry out to make it successful. I will ...
0
votes
1answer
78 views

How do I kill a non-running impdp job?

I am trying to run an import operation into an Oracle 11G R2 database from a .dmp file using SQL Developer 4.1's Data Pump Import Wizard. I set everything up with IMMEDIATE start, but then the job ...
0
votes
0answers
78 views

How do I open Data Pump Import Wizard in SQL Developer 4.1?

How do I find and open the Data Pump Import Wizard in SQL Developer 4.1? I opened the Help search tool in SQL Developer 4.1 and searched for Data Pump, and found directions that include: 1.) ...
1
vote
1answer
71 views

Compare Strings ignoring accents in SQL

I would like to know if there is an easy way to compare two text values ignoring the accents and upper case. Im working with an Oracle database. Thank You!
1
vote
3answers
198 views

ORA-02270: no matching unique or primary key for this column-list

I getting an error report of: Error report - SQL Error: ORA-02270: no matching unique or primary key for this column-list 02270. 00000 - "no matching unique or primary key for this column-list" ...
3
votes
1answer
92 views

Global command to destroy all disconnected data files in Oracle 11g

Is there a global command to delete/purge all disconnected .bdf files in an Oracle 11g R2 database? Here is the scenario: Every user-defined user and user-defined tablespace was deleted from an ...
0
votes
1answer
79 views

How can I launch SQL Developer?

I recently installed Oracle 11g R2 on a Windows 8.1 machine, but for some reason SQL Developer does not seem to launch easily. What specific steps do I need to take in order to launch a working SQL ...
2
votes
2answers
81 views

How to find Oracle's installed version and release from a program?

I need to determine from a program what version of Oracle is installed in each of the Oracle Homes on a server. As there may not be any databases created in the Home yet, I need to be able to do this ...
2
votes
1answer
66 views

Can I use an RMAN Backup of DB1 to replace DB2

I have a backup of an Oracle 11g database on Linux, that is a version that is 'prescrub' needed by other users. This is for an ACTIVE database that is now scrubbed, so I do not want to use it as the ...
-1
votes
2answers
70 views

Finding the number of columns a table has

How can I find out the number of columns a table has? I use oracle 10g and I want to develop a J2EE application to count the number of columns of different tables.
1
vote
3answers
160 views

How to select multiple columns between columns in Oracle SQL?

A question that was asked among a few developers, now deferred to DBA experts: Is there a way to select multiple columns within a table instead of SELECT'ing columns piecemeal? For example, let's ...
0
votes
1answer
40 views

Oracle Database 11g and TFS (2010) integration

I am currently in a team of 4 database developers, working with Oracle Database 11g Enterprise Edition. We use quite a bit of SQL/PLSQL and create many scripts. My colleagues send all their work to a ...
4
votes
1answer
46 views

What is non-CDB @ Oracle ?

I'm reading about Oracles PDB and CDB, and their test performances http://www.oracle.com/technetwork/database/multitenant/learn-more/oraclemultitenantt5-8-final-2185108.pdf They compare PDB with ...
1
vote
1answer
200 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
617 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
90 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
53 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
112 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
61 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
69 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
35 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
79 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
88 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
113 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
129 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
56 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
36 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
77 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
56 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
68 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
83 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
51 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
94 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 ...