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
38 votes
3 answers
45k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
孔夫子's user avatar
  • 4,330
22 votes
3 answers
83k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
NotMe's user avatar
  • 887
62 votes
4 answers
37k views

What is the difference between select count(*) and select count(any_non_null_column)?

I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table. What are the differences between these ...
Martin's user avatar
  • 2,420
18 votes
3 answers
8k views

Is there an execution difference between a JOIN condition and a WHERE condition?

Is there a performance difference between these two example queries? Query 1: select count(*) from table1 a join table2 b on b.key_col=a.key_col where b.tag = 'Y' Query 2; select count(*)...
BellevueBob's user avatar
83 votes
6 answers
434k views

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the ...
BrunoLM's user avatar
  • 3,463
18 votes
4 answers
62k views

How can I unload Oracle database tables into a csv files?

I need to retrieve certain data from an Oracle 11g release 1 database and provide it as one or more CSV files. The data resides in multiple tables and/or views. All this should work via the command ...
aPhilRa's user avatar
  • 183
18 votes
3 answers
125k views

"ORA-01950: no privileges on tablespace 'SYSTEM' " : What error is it?

Earlier I created a user : SQL>create user suhail identified by password SQL>User created. SQL>Grant create session,create table to suhail; SQL>Grant Succeeded. but when I login as ...
Suhail Gupta's user avatar
11 votes
2 answers
1k views

Where to start with Oracle?

This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL. You may find your question has been closed as a ...
108 votes
6 answers
287k views

Difference between database vs user vs schema

What is the difference (if any) between a database, a user, and a schema?
Ravi's user avatar
  • 1,545
57 votes
7 answers
153k views

EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other?

Whenever I need to check for the existence of some row in a table, I tend to write always a condition like: SELECT a, b, c FROM a_table WHERE EXISTS (SELECT * -- This is what I normally ...
joanolo's user avatar
  • 13.4k
21 votes
2 answers
45k views

Duplicate an Oracle database user

We'll be having external auditors coming over to perform a review on our Oracle database. They will be running a tool to perform the review and for that, they require a user ID that can connect to the ...
Gommel's user avatar
  • 213
7 votes
4 answers
2k views

Does an IOT guarantee order in a select?

We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1). The priority does not matter ...
Ben's user avatar
  • 462
6 votes
3 answers
44k views

Use column alias in GROUP BY

I have a query where I chain two columns. Why does the alias not work in the GROUP BY clause but in ORDER BY clause, it does work? How can I write the select correctly? SELECT KOS_VER_ID AS "...
Konrad's user avatar
  • 61
5 votes
3 answers
6k views

Move Complete ORACLE from one system to another

I have 2 identical server with exact disk partitions (OS CENTOS 6.4 both ) [My college central oracle server]. Entire ORACLE HOME ( bin , control files, data files everything on oracle) is on 2nd ...
Ashish's user avatar
  • 197
3 votes
1 answer
7k views

ORA-12528 and ORA-12505 error on Oracle database 11g

It's been one week since I last tried to run my final project that I worked on in my last internship at some banking company. Suddenly, my webapps (Java project) in Eclipse can't start. In the log it ...
Nico's user avatar
  • 133
1 vote
1 answer
9k views

Restrict Oracle DB Login based on SQL Client being used

We have an application in our organization that is based on Oracle Forms 11g (on Oracle Fusion Middleware/WLS). Users have access to the application through accounts created at the database (i.e. they ...
Anjan Biswas's user avatar
0 votes
3 answers
454 views

Do database engines update when the updated value is the same? [closed]

Suppose I am running multiple column update on one row. Does the database engine physically write new data to the database if some of the columns contain the same values as existed previously in the ...
kyooryu's user avatar
  • 256
0 votes
3 answers
7k views

Update values in one table based on contents of another

Software Name: Oracle SQL Developer Version 17.2.0.188 I have three tables: orders, order_details, and products. I wanted to run a query to update units_in_stock in the products table based on ...
Lynn's user avatar
  • 19
34 votes
2 answers
340k views

How do you show SQL executing on an Oracle database?

How can you show the SQL that is currently executing on an oracle db? Extra information that would be useful would include user, session id etc.
Clarkey's user avatar
  • 1,063
32 votes
9 answers
39k views

How do you version your Oracle database changes?

I am interested to know what methods other people are using to keep track of changes made to the database including table definition changes, new objects, packages changes, etc. Do you use flat files ...
Leigh Riffel's user avatar
  • 23.8k
31 votes
5 answers
331k views

How do I generate a tnsnames.ora file to connect to an Oracle Database?

I have installed Oracle 11g, and I can connect as sysman to the Oracle database, but there is no tnsnames.ora file that I can find. Do I need to generate the tnsnames.ora file myself? If so, where do ...
user897237's user avatar
29 votes
2 answers
176k views

How to transfer data using expdp and impdp commands?

I'm an Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump's expdp and impdp commands. I have ...
jrara's user avatar
  • 5,333
13 votes
4 answers
5k views

Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

I stumbled upon this question on a Twitter conversation with Lukas Eder. Although the correct behavior would be to apply the ORDER BY clause on the outermost query, because, here, we are not using ...
Vlad Mihalcea's user avatar
10 votes
3 answers
21k views

Resolving port 5500 not free issue during Oracle 12c Release 2 Database Configuration Assistant installation on CentOS

Background I am attempting to install Oracle 12c on CentOS 7 (deployed as a Hyper-V virtual machine) as outlined by this installation guide. Problem Everything with my installation goes well except ...
Dodzi Dzakuma's user avatar
8 votes
3 answers
3k views

How to store many smallish time-series in a relational DB?

Input Data Multiple Tests Beds generate measurement data of various complexity. In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
Martin's user avatar
  • 2,420
8 votes
3 answers
12k views

ORA-04031: unable to allocate bytes of shared memory ("","","","")

I am receiving this error in a development database. It happens on some specific queries (simple inserts and deletes via .NET application). The database is used by one user, and error happens ...
spm's user avatar
  • 91
7 votes
2 answers
29k views

What is the difference between Schema and Database in Oracle?

In Oracle, datafile are system files where actual data is stored. The collection of datafiles make tablespace and, at last, database is collection of tablespaces. Correct me if I am wrong on the ...
nitesh.kodle123's user avatar
6 votes
4 answers
23k views

How can I select rows from a hierarchical query with the lowest level?

I have a hierarchical query in Oracle 11gR2 that returns something like this: Parent (Level 1) Child (Level 2) Grandchild (Level 3) Child (Level 2) Grandchild (Level 3) Grandchild (Level 3) ...
Allan Lewis's user avatar
5 votes
3 answers
20k views

Using OPENQUERY to execute a script

I have a SQL Server 2008 instance which I want to use to import data from an Oracle server. I have set up a linked server that works correctly when running simple queries like SELECT * FROM table. ...
user802599's user avatar
5 votes
1 answer
14k views

Why isn't oracle using an index for distinct query ?

This is the scenario SQL> exec dbms_stats.gather_table_stats(user,'TM', cascade=>true) PL/SQL procedure successfully completed. SQL> SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / ...
haki's user avatar
  • 376
5 votes
1 answer
14k views

Update oracle sql database from CSV

I tried google at first but no luck. Is it possible to update tables from csv file? I am using SQL developer and I am using a script to export edited rows to a csv file. I would like to update the ...
xiltepin's user avatar
4 votes
1 answer
8k views

Unable to open/connect to Oracle 12c Enterprise Manager Express [duplicate]

I have a just installed a new single instance installation of Oracle 12c (12.1.0.2.0 on Windows x64) with ASM. The database itself is working OK (can be accessed locally and remotely via port 1521), ...
cbailiss's user avatar
  • 296
4 votes
3 answers
23k views

ORA-01180 when restoring the database

I get the following error message when trying to restore the database from backup using RMAN. rman target / RMAN> set dbid=130474031 RMAN> configure device type 'sbt_tape' clear; RMAN> ...
Dumitru Gutu's user avatar
3 votes
1 answer
15k views

How to reference a table from another schema inside a package?

I am using TOAD to create a package in a Schema that I have am owner. My package uses tables of another schema. The package has a procedure that uses a cursor. When I create the package I get ...
Dzyann's user avatar
  • 345
2 votes
2 answers
2k views

Trigger to get next prefixed ID: Is there a better way?

I have a projects table: +------------+-----+ | PROJECT_ID | ... | +------------+-----+ | LC000001 | | | LC000002 | | | LC000003 | | | LC000004 | | | LC000005 | | +------...
User1974's user avatar
  • 1,457
2 votes
2 answers
10k views

"Unable to display page" with Oracle 12c Enterprise Manager Database Express

i'm using redhat 6, installed 12.1.0.2 of oracle and created a database. database (dev12ee1) is a non-container database, there is also an 11g database on the server (dev11ee1) lsnrctl status shows ...
davegreen100's user avatar
2 votes
1 answer
5k views

Step by step establish a connection between SQL Server 2014 and Oracle 12c

I have two servers (both servers are on Windows OS): Server_A running Oracle 12c Server_B running SQL Server 2014 I want these servers to be able to see each other, so I can SELECT anything I want ...
SajjaD's user avatar
  • 187
1 vote
1 answer
36k views

Resolving ORA-01033 "initialization or shutdown in progress"

I followed the instructions at http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php to create a database export. I used: expdp scott/tiger@db10g full=Y directory=TEST_DIR dumpfile=...
babu's user avatar
  • 43
1 vote
0 answers
6k views

Start Oracle 12c enterprise manager in Windows 7

I have installed Oracle 12c in Windows 7. The installation was succesful and the RDBMS and the database that I created are working properly. Then I tried to access to the enterprise manager via web ...
Esteban S's user avatar
  • 131
1 vote
3 answers
9k views

Oracle 12c Express Enterprise Manager on Centos 6.5 is not reachable

I have Centos 6.5, and installed Oracle 12c. As dbca utility says after configuring new db, EM Express should be available on https://hostname:5500/em In fact, it is not. Neither on localhost nor by ...
Battle_Slug's user avatar
1 vote
0 answers
8k views

How to run ORACLE 12C EM?

I have installed and created a database in oracle 12c in ubuntu 14.04 . Now I can login to the CLI using sqlplus \ as sysdba and run sqlcommands , but I can't login to the EM from the web. The name ...
Tamim Addari's user avatar
0 votes
3 answers
22k views

Comparing values between two tables

How to compare values between two different tables? I have two tables as shown below: Table 'A' sql_id OPERATION OPTIONS 63kf3an2j0pk SELECT STATEMENT 63kf3an2j0pkc TABLE ACCESS FULL ...
user41960's user avatar
61 votes
3 answers
606k views

How to make sqlplus output appear in one line?

I have a table with 100 columns. When selecting data in SQL Plus the output wraps, making it difficult to read. What I'd rather like is either a horizontal scroll bar to appear or somehow send the ...
Kshitiz Sharma's user avatar
50 votes
4 answers
97k views

What is the difference between sys and system accounts in Oracle databases?

There are two ways to connect to Oracle as an administrator using SQL Plus: sqlplus sys as sysdba sqlplus system/manager These accounts should be used for different purposes, I suppose. Which tasks ...
Lazer's user avatar
  • 3,351
48 votes
15 answers
187k views

Eliminate duplicates in ListAgg (Oracle)

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to ...
Leigh Riffel's user avatar
  • 23.8k
34 votes
2 answers
6k views

As a DBA, how would I go about transitioning from Oracle to SQL Server?

I'm an Oracle DBA that also has Sybase experience. What are the major architectural and conceptual differences between the two RDBMS platforms? An answer similar to the SQL Server->Oracle question ...
Philᵀᴹ's user avatar
  • 31.7k
33 votes
7 answers
208k views

Import a Oracle DMP file into a Fresh install of oracle

A client sent us an Oracle database we need to test against. We don't use Oracle or have any in-house Oracle expertise. We need to setup the database so we can connect to it and debug a problem. I ...
user avatar
32 votes
2 answers
9k views

How to make a transition from SQL Server DBA to Oracle?

As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do? I would assume the concepts are the same and the ...
darwindeeds's user avatar
32 votes
1 answer
319k views

"ORA-03113: end-of-file on communication channel" on startup

I have been reading posts here, on Oracle support, and anywhere else I can find for the last three days and I've given up on this problem... An Oracle database hung. Shutdown of the database sat for ...
kainaw's user avatar
  • 1,362
25 votes
7 answers
41k views

Oracle - Any way to view uncommited changes to a particular table?

I'm debugging through a batch process currently that does a lot of DML statements, but doesn't do a commit right away. It would be nice to be able to view the "pending" changes from another session ...
contactmatt's user avatar

1
2 3 4 5
9