Questions tagged [oracle]
Oracle Database is a Multi-Model Database Management System created by Oracle Corporation. Do NOT use this tag for other products owned by Oracle, such as Java and MySQL.
151,655
questions
1340
votes
16
answers
2.4m
views
How do I limit the number of rows returned by an Oracle query after ordering?
Is there a way to make an Oracle query behave like it contains a MySQL limit clause?
In MySQL, I can do this:
select *
from sometable
order by name
limit 20,10
to get the 21st to the 30th rows (skip ...
- 16.3k
1212
votes
26
answers
2.4m
views
Get list of all tables in Oracle?
How do I query an Oracle database to display the names of all tables in it?
- 15.8k
674
votes
35
answers
693k
views
Fetch the rows which have the Max value for a column for each distinct value of another column
Table:
UserId, Value, Date.
I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date.
How do I do this in SQL? (Preferably Oracle....
- 6,775
524
votes
18
answers
1.4m
views
How to create id with AUTO_INCREMENT on Oracle?
It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g.
How can I create a column that behaves like auto increment in Oracle 11g?
- 7,417
494
votes
34
answers
1.1m
views
How to select the nth row in a SQL database table?
I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native ...
- 20.5k
461
votes
11
answers
641k
views
Can a foreign key be NULL and/or duplicate?
Please clarify two things for me:
Can a Foreign key be NULL?
Can a Foreign key be duplicate?
As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to ...
- 21.8k
455
votes
29
answers
805k
views
Is there a combination of "LIKE" and "IN" in SQL?
In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.
Further, I ...
- 15.7k
443
votes
9
answers
705k
views
How to generate an entity-relationship (ER) diagram using Oracle SQL Developer
I want to use Oracle SQL Developer to generate an ER diagram for my DB tables but I am new to Oracle and this tool.
What is the process for creating an ER diagram in SQL Developer?
- 4,531
433
votes
8
answers
778k
views
What is the difference between Views and Materialized Views in Oracle?
What is the difference between Views and Materialized Views in Oracle?
- 81.1k
418
votes
16
answers
671k
views
Oracle: If Table Exists
I'm writing some migration scripts for an Oracle database, and was hoping Oracle had something similar to MySQL's IF EXISTS construct.
Specifically, whenever I want to drop a table in MySQL, I do ...
- 165k
408
votes
12
answers
381k
views
Liquibase lock - reasons?
I get this when running a lot of liquibase-scripts against a Oracle-server. SomeComputer is me.
Waiting for changelog lock....
Waiting for changelog lock....
Waiting for changelog lock....
Waiting ...
- 5,714
381
votes
9
answers
849k
views
Best way to do multi-row insert in Oracle?
I'm looking for a good way to perform multi-row inserts into an Oracle 9 database. The following works in MySQL but doesn't seem to be supported in Oracle.
INSERT INTO TMP_DIM_EXCH_RT
(EXCH_WH_KEY, ...
- 5,131
377
votes
16
answers
1.1m
views
Update statement with inner join on Oracle
I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ...
- 4,187
360
votes
16
answers
294k
views
Difference between a user and a schema in Oracle?
What is the difference between a user and a schema in Oracle?
- 6,717
341
votes
11
answers
549k
views
Is there any boolean type in Oracle databases?
Is there any Boolean type in Oracle databases, similar to the BIT datatype in Ms SQL Server?
- 3,411
335
votes
32
answers
2.2m
views
ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
We have an application running locally where we're experiencing the following error:
ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor
I've tested the ...
- 7,025
331
votes
11
answers
536k
views
How to UPSERT (update or insert into a table?)
The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data:
if table t has a row exists that has key X:
update t set mystuff......
- 301k
330
votes
8
answers
1.4m
views
Update a table with data from another table
Table 1:
id name desc
-----------------------
1 a abc
2 b def
3 c adf
Table 2:
id name desc
-----------------------
1 x 123
2 y 345
In ...
- 25k
327
votes
13
answers
1.2m
views
How do I list all the columns in a table?
For the various popular database systems, how do you list all the columns in a table?
- 12.2k
316
votes
13
answers
860k
views
How do I find duplicate values in a table in Oracle?
What's the simplest SQL statement that will return the duplicate values for a given column and the count of their occurrences in an Oracle database table?
For example: I have a JOBS table with the ...
- 13.1k
310
votes
6
answers
382k
views
What is the difference between varchar and varchar2 in Oracle?
What is the difference between varchar and varchar2?
- 3,163
309
votes
21
answers
588k
views
Select count(*) from multiple tables
How can I select count(*) from two different tables (call them tab1 and tab2) having as result:
Count_1 Count_2
123 456
I've tried this:
select count(*) Count_1 from schema.tab1 union all ...
- 3,101
307
votes
17
answers
698k
views
How can I create a copy of an Oracle table without copying the data?
I know the statement:
create table xyz_new as select * from xyz;
Which copies the structure and the data, but what if I just want the structure?
- 13.1k
304
votes
19
answers
146k
views
Inner join vs Where
Is there a difference in performance (in oracle) between
Select * from Table1 T1
Inner Join Table2 T2 On T1.ID = T2.ID
And
Select * from Table1 T1, Table2 T2
Where T1.ID = T2.ID
?
- 81.1k
299
votes
7
answers
646k
views
Oracle "Partition By" Keyword
Can someone please explain what the partition by keyword does and give a simple example of it in action, as well as why one would want to use it? I have a SQL query written by someone else and I'm ...
- 21.1k
296
votes
8
answers
790k
views
How to connect to Oracle using Service Name instead of SID
I have a Java application that uses JDBC (via JPA) that was connecting to a development database using hostname, port and Oracle SID, like this:
jdbc:oracle:thin:@oracle.hostserver1.mydomain.ca:1521:...
- 15.1k
293
votes
9
answers
889k
views
How do I do top 1 in Oracle? [duplicate]
How do I do the following?
select top 1 Fname from MyTbl
In Oracle 11g?
- 61.6k
275
votes
3
answers
115k
views
Oracle SQL Developer multiple table views
In Oracle SQL Developer, one can list the data in a table using the Data tab when viewing a table. there where can add, delete, edit or just viewing records of table currently viewd
the problem is ...
- 2,751
270
votes
12
answers
169k
views
Non-Repeatable Read vs Phantom Read?
What is the difference between non-repeatable read and phantom read?
I have read the Isolation (database systems) article from Wikipedia, but I have a few doubts. In the below example, what will ...
- 7,368
270
votes
7
answers
382k
views
Case insensitive searching in Oracle
The default behaviour of LIKE and the other comparison operators, = etc is case-sensitive.
Is it possible make them case-insensitive?
- 13.5k
266
votes
6
answers
683k
views
How to export query result to csv in Oracle SQL Developer?
I'm using Oracle SQL Developer 3.0. Trying to figure out how to export a query result to a text file (preferably CSV). Right clicking on the query results window doesn't give me any export options.
- 22.7k
261
votes
14
answers
288k
views
What is the dual table in Oracle?
I've heard people referring to this table and was not sure what it was about.
- 54.4k
257
votes
16
answers
1.4m
views
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Why am I getting this database error when I update a table?
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
- 2,622
244
votes
8
answers
286k
views
Oracle Differences between NVL and Coalesce
Are there non obvious differences between NVL and Coalesce in Oracle?
The obvious differences are that coalesce will return the first non null item in its parameter list whereas nvl only takes two ...
- 16k
243
votes
9
answers
610k
views
How can I set a custom date time format in Oracle SQL Developer?
By default, Oracle SQL developer displays date values as 15-NOV-11. I would like to see the time part (hour/minute/second) by default.
Is there a way to configure this within Oracle SQL Developer?
- 3,461
242
votes
10
answers
75k
views
Why does Oracle 9i treat an empty string as NULL?
I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case. As I understand the SQL specifications, ' ' is not the same as NULL -- one is a valid datum, and ...
- 17.8k
232
votes
12
answers
396k
views
How can I find which tables reference a given table in Oracle SQL Developer?
In Oracle SQL Developer, if I'm viewing the information on a table, I can view the constraints, which let me see the foreign keys (and thus which tables are referenced by this table), and I can view ...
- 27.2k
230
votes
11
answers
737k
views
What's the difference between RANK() and DENSE_RANK() functions in oracle?
What's the difference between RANK() and DENSE_RANK() functions? How to find out nth salary in the following emptbl table?
DEPTNO EMPNAME SAL
------------------------------
10 rrr 10000....
- 7,368
229
votes
9
answers
171k
views
When do I need to use a semicolon vs a slash in Oracle SQL?
We have been having some debate this week at my company as to how we should write our SQL scripts.
Background:
Our database is Oracle 10g (upgrading to 11 soon). Our DBA team uses SQLPlus in order ...
- 4,830
223
votes
13
answers
382k
views
How to add 'ON DELETE CASCADE' in ALTER TABLE statement
I have a foreign key constraint in my table, I want to add ON DELETE CASCADE to it.
I have tried this:
alter table child_table_name
modify constraint fk_name
foreign key (child_column_name)
...
- 12.8k
222
votes
15
answers
645k
views
error: ORA-65096: invalid common user or role name in Oracle database
I just installed Oracle database, and it was missing the SCOTT schema. So I am trying to generate it myself. I got the sql script of Scott schema, but when I try to run the query
CREATE USER SCOTT ...
- 2,465
219
votes
12
answers
319k
views
What is the maximum length of a table name in Oracle?
What are the maximum length of a table name and column name in Oracle?
- 8,624
219
votes
33
answers
1.1m
views
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
I have installed Oracle 11g Express Edition Release 2 in my windows 7 64 bit OS and tried to execute JDBC program, then I got the following error:
java.sql.SQLException: Listener refused the ...
- 2,191
219
votes
6
answers
525k
views
GROUP BY with MAX(DATE) [duplicate]
I'm trying to list the latest destination (MAX departure time) for each train in a table, for example:
Train Dest Time
1 HK 10:00
1 SH 12:00
1 SZ 14:...
- 2,251
219
votes
26
answers
895k
views
How can I get column names from a table in Oracle?
I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and ...
- 2,806
215
votes
27
answers
16k
views
What resources exist for Database performance-tuning? [closed]
What good resources exist for understanding database tuning on the major engines and advancing your knowledge in that area?
The idea of this question is to collect the shed load of resources that ...
213
votes
10
answers
478k
views
SQL Query to concatenate column values from multiple rows in Oracle
Would it be possible to construct SQL to concatenate column values from
multiple rows?
The following is an example:
Table A
PID
A
B
C
Table B
PID SEQ Desc
A 1 Have
A 2 a ...
- 5,376
205
votes
18
answers
737k
views
How do I reset a sequence in Oracle?
In PostgreSQL, I can do something like this:
ALTER SEQUENCE serial RESTART WITH 0;
Is there an Oracle equivalent?
- 17.1k
205
votes
4
answers
375k
views
Oracle find a constraint
I have a constraint called users.SYS_C00381400. How do I find what that constraint is? Is there a way to query all constraints?
- 12.9k
205
votes
8
answers
697k
views
How to retrieve the current value of an oracle sequence without increment it?
Is there an SQL instruction to retrieve the value of a sequence that does not increment it.
Thanks.
EDIT AND CONCLUSION
As stated by Justin Cave It's not useful to try to "save" sequence number so
...
- 2,741