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.
7,810
questions
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
107
votes
4
answers
997k
views
How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora
I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to connect to that database
local_SID =
(DESCRIPTION =
(ADDRESS = ...
- 1,265
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
71
votes
7
answers
864k
views
Connect with sqlplus from the command line using a connection string
Let's say I have an Oracle database. I have a username = x, password = y, database = z, port = a, SID = b, and Hostname = c.
So, how do I connect correctly? I used many options like:
sqlplus x/y@'(...
- 711
64
votes
6
answers
90k
views
Why not use a table instead of a materialized view?
I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some ...
- 5,353
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
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
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
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
45
votes
9
answers
421k
views
How to see list of databases in Oracle?
Is there an equivalent to MySQL SHOW DATABASES statement?
Is it possible to find databases in a cluster? i.e. databases present on the network on some other system?
Could I analyze the files present ...
- 3,317
41
votes
2
answers
356k
views
Oracle: Quick way to list all database links
The title says it all, is there a way to quickly list all the current database links in oracle?
Something along the same lines as this, which lists the current user's tables:
select * from ...
- 1,093
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
35
votes
3
answers
6k
views
Why does this query work?
I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c.
Why does this query not return an exception?
select * from table_a where name in (select name from table_b);
From ...
- 523
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
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
34
votes
2
answers
321k
views
oracle - list users with access to certain tables
I'm sure this has been asked before but I can't seem to find the relevant details for the following.
Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
- 359
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
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
32
votes
9
answers
62k
views
How does the Oracle DUAL table work?
SQL> desc dual
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY ...
- 3,361
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
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
31
votes
5
answers
339k
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
30
votes
7
answers
237k
views
How do I Create Tablespace in Oracle 11g?
I am not too savvy in Oracle, and I have been trying to create a TABLESPACE, which keep on giving me an error message of invalid file name. Below is the syntax that I have used:
SQL> create ...
user74139
29
votes
4
answers
362k
views
How to update a table from a another table
I have one table A has column (id, field_1,field_2),
and another table B has column (id,field_2)
Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. ...
- 399
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
29
votes
4
answers
17k
views
Why is OS authentication considered poor security for Oracle databases?
Oracle is deprecating OS authentication according to the Oracle Database Security Guide, which says
Be aware that the REMOTE_OS_AUTHENT
parameter was deprecated in Oracle
Database 11g Release 1 ...
- 23.9k
27
votes
2
answers
185k
views
How is the default tablespace determined when creating a table?
The Tablespace parameter is optional when creating tables.
Upon execution of a CREATE TABLE statement, Oracle assigns the default one if it was not defined.
In the default database there is a "...
- 895
27
votes
4
answers
131k
views
How to find the latest SQL statements within the database?
I like to get the latest executed statements within my database, along with performance indicators.
As such, I like to know, which SQL statements were most CPU/DISK intensive.
- 1,356
26
votes
2
answers
7k
views
Index vs. partition
Why is it not possible to achieve performance improvements with indexes only, so that other techniques like table partitioning becomes necessary? The question relates only to performance, of course ...
- 1,099
26
votes
3
answers
285k
views
How do I declare and use variables in Oracle?
My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL:
declare @startDate int
select @startDate = 20110501
And I get this ...
- 525
26
votes
7
answers
15k
views
Composite indexes: Most selective column first?
I’ve been reading about composite indexes and I’m slightly confused about ordering. This documentation (little less than half way down) says
In general, you should put the column expected to be ...
- 373
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 ...
- 411
25
votes
2
answers
78k
views
How to catch and handle only specific Oracle exceptions?
From this and this i guess, that there is no predefined Named System Exceptions for ORA-00955.
How can I rewrite the following to catch only the error ORA-00955?
begin
EXECUTE IMMEDIATE '...
- 12.3k
24
votes
7
answers
105k
views
How to get rid of ORA-28002 message the password will expire within 6 days?
I have a user getting an ORA-28002 indicating that the password will expire within six days. I ran the following:
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
But when I try to log in ...
- 12.3k
23
votes
7
answers
464k
views
How check that the Oracle Database is up?
For our application we have a configuration file for connecting to the Oracle database (URL, username, password).
The application takes 2-5 minutes to start. If there is something wrong with ...
- 231
22
votes
4
answers
219k
views
What is the default username and password to the oracle pre-built virtual machine?
I downloaded DeveloperDaysVM2016-06-02_13.ova from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and successfully loaded it into Virtual Box.
I logged ...
- 423
22
votes
3
answers
242k
views
How to get a list of locked accounts / check that account is locked?
I can use following statement to unlock an account:
ALTER USER username ACCOUNT UNLOCK
But which statement can I use to verify that account is currently locked out?
- 747
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
21
votes
14
answers
7k
views
Comfortable sqlplus interface? [closed]
I found sqlplus'interface is rather outdated. It's quite nice to have some commands or keywords at disposal, but for example no "arrow-up" key for the previous history entry is available.
What is a ...
- 1,356
21
votes
2
answers
9k
views
Difference between GROUP BY B,A and GROUP BY COALESCE(B,A)
I have been getting myself very confused.
Could somebody kindly explain under what circumstances I would want to use a GROUP BY COALESCE?
My guess is that I would use it if I wanted to conditionally ...
- 323
21
votes
4
answers
94k
views
Can I change the SID of an Oracle database?
The Server is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit
Is there an easy & quick way to change the SIDs of the test databases on the server?
Dropping & recreating of ...
- 12.3k
21
votes
4
answers
23k
views
What's the most efficient UUID column type
For storing a 128 bits UUID there are multiple storage options:
a byte[16] column
two bigint/long(64 bits) columns
a CHAR(36) column - 32 hex digits + 4 dashes.
a UUID database specific column, if db ...
- 907
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
20
votes
4
answers
26k
views
What is the difference between Oracle's VARCHAR and VARCHAR2 datatypes?
When migrating tables coming from other DBMSs to Oracle, one of the standard tasks is to replace all VARCHAR(n) fields with VARCHAR2(n) fields (provided n <= 4000).
Why does Oracle call this ...
- 12.3k
20
votes
3
answers
20k
views
What does NVL stand for?
What does NVL stand for? I'm talking about the Oracle and Informix (perhaps some others too) function used to filter out non NULL values from query results (similar to COALESCE in other databases).
- 1,065
20
votes
2
answers
48k
views
Is commit necessary after DML operation in Function/Procedure?
I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure?
Example:
create or replace function test_fun
return number is
begin
delete from a;
return ...
- 1,717
19
votes
6
answers
17k
views
Oracle Creating ER Diagrams and Data Dictionaries
I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?
- 657
19
votes
4
answers
252k
views
Where is my ORACLE_HOME Windows directory?
I'm trying to find my listener.ora file to edit it, but the docs say it's in the ORACLE_HOME directory, but where is that?
I'm running 12.1.0 on Windows Server 2008
- 589
19
votes
6
answers
40k
views
What is the recommended RAID configuration for an Oracle database?
RAID (Redundant Arrays of Inexpensive Disks) comes with different configurations (RAID-0, RAID-1...). What is the recommended RAID configuration that I should set up and use when installing an Oracle ...
- 778