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
82
votes
8answers
203k views

Difference between database vs user vs schema

I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)? If they are same then, what are the similarities between them? ...
81
votes
4answers
725k 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 conenct to that database local_SID = (DESCRIPTION = (ADDRESS =...
58
votes
4answers
36k 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 ...
57
votes
5answers
308k 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 ...
53
votes
7answers
64k 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 ...
47
votes
3answers
460k 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 ...
44
votes
14answers
154k 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 ...
41
votes
9answers
536k views

Connect to sql plus from command line using connection string

Let's say I have a Oracle database. I have a username = x, password = y, database = z. Also I know the port = a, SID = b, Hostname = c. So how do I need to connect correctly? I used many options like:...
39
votes
4answers
72k views

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

There are two ways to connect to Oracle as a administrator using sqlplus: sqlplus sys as sysdba sqlplus system/manager These accounts should be uses for different purposes, I suppose. Which tasks ...
37
votes
3answers
5k 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 ...
37
votes
7answers
70k 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 ...
33
votes
7answers
245k 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 ...
32
votes
2answers
5k 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 ...
32
votes
2answers
190k 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 ...
31
votes
9answers
55k views

How does the Oracle DUAL table work?

SQL> desc dual Name Null? Type ----------------------------------------- -------- ---------------------------- DUMMY ...
31
votes
9answers
34k 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 ...
31
votes
3answers
29k 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 ...
30
votes
2answers
8k 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 ...
29
votes
7answers
220k 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 ...
29
votes
7answers
179k 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 ...
29
votes
2answers
165k 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 ...
29
votes
4answers
16k 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 ...
28
votes
4answers
333k 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. ...
28
votes
2answers
259k 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 ...
27
votes
2answers
171k 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 "...
26
votes
2answers
227k 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.
24
votes
7answers
36k 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 ...
24
votes
4answers
114k 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.
22
votes
1answer
236k 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 ...
22
votes
5answers
240k 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 ...
21
votes
14answers
6k views

Comfortable sqlplus interface?

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 ...
21
votes
3answers
76k 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: ╔══════════╦══════════╗ ║ ...
20
votes
2answers
5k 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 ...
20
votes
2answers
58k 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 '...
20
votes
2answers
42k 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 ...
20
votes
2answers
5k 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 ...
19
votes
3answers
24k 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 ...
19
votes
6answers
16k 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?
19
votes
9answers
35k views

Tools to monitor Oracle Database Performance on Standard Edition [closed]

Here is what I am looking for: Works on Standard Edition i.e. No Diagnostic Pack. Provides both current and historical performance data. Good visuals for easy spotting of trends and presentation to ...
19
votes
4answers
3k views

SSDs with Oracle

We've been looking into using SSDs with Oracle to speed up our test migration runs. It currently takes 12-18 hours to complete a migration run, depnding on the volume of data (we're obviously doing ...
18
votes
6answers
81k 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 ...
18
votes
3answers
69k 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 ...
18
votes
6answers
71k views

Best way to delete very large recordset in Oracle

I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end. The database doesn't really do anything (no SProcs, no ...
18
votes
4answers
61k 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 ...
18
votes
3answers
4k views

Is there any point in undersizing VARCHAR columns?

Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not. I would like to give the question of VARCHAR size a little twist and hope ...
18
votes
2answers
166k 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 ...
17
votes
3answers
17k 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).
17
votes
1answer
36k 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 ...
17
votes
3answers
6k 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(*)...
17
votes
1answer
84k views

Oracle 11g listener fails with ORA-12514 and ORA-12505 errors

I run an instance of Oracle 11g locally on my development machine and can connect to the local instance directly via SqlPlus: c:\>sqlplus ace SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar ...