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.
405
questions
38
votes
3
answers
46k
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 ...
- 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:
╔══════════╦══════════╗
║ ...
- 887
62
votes
4
answers
38k
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 ...
- 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(*)...
- 283
84
votes
6
answers
441k
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 ...
- 3,513
18
votes
4
answers
63k
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 ...
- 183
18
votes
3
answers
128k
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 ...
- 421
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 ...
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 ...
- 462
109
votes
6
answers
293k
views
Difference between database vs user vs schema
What is the difference (if any) between a database, a user, and a schema?
- 1,555
58
votes
7
answers
161k
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 ...
- 13.5k
21
votes
2
answers
46k
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 ...
- 213
6
votes
3
answers
45k
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 "...
- 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 ...
- 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 ...
- 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 ...
- 125
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 ...
- 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 ...
- 19
34
votes
2
answers
344k
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.
- 1,093
33
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 ...
- 23.9k
31
votes
5
answers
338k
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 ...
- 413
29
votes
2
answers
178k
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 ...
- 5,353
13
votes
4
answers
6k
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 ...
- 907
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 ...
- 275
8
votes
3
answers
13k
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 ...
- 91
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 ...
- 2,420
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 ...
- 295
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)
...
- 322
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 / ...
- 386
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 ...
- 51
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. ...
- 443
4
votes
3
answers
24k
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> ...
- 141
4
votes
1
answer
9k
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), ...
- 296
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 ...
- 355
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 | |
+------...
- 1,497
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 ...
- 562
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 ...
- 187
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 ...
- 203
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 ...
- 131
1
vote
1
answer
37k
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=...
- 43
1
vote
1
answer
2k
views
Does oracle database suffer from parameter sniffing issue
Does oracle database suffer from parameter sniffing issue in stored procedures?
Considering there are millions of pages written on parameter sniffing issue, I think that this is more of a bug than a ...
- 93
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 ...
- 159
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
...
- 29
63
votes
3
answers
615k
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 ...
- 3,317
51
votes
4
answers
99k
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 ...
- 3,361
49
votes
15
answers
189k
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 ...
- 23.9k
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 ...
- 31.9k
33
votes
7
answers
209k
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 ...
James
32
votes
1
answer
326k
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 ...
- 1,372
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 ...
- 560