Oracle is a RDBMS (Relational Database Management System) created by Oracle Corporation. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java. Also included is support for C (Pro*C, OCI and EXTPROC), C++ (OCCI) and Java (JDBC) ...
4
votes
2answers
239 views
MAX for each subset
I have a table that looks like this:
+---------------------------------------------+
| EVENT_ID | ITEM_ID | PERSON_ID | EVENT_DATE |
+---------------------------------------------+
| 123 | 1 ...
4
votes
3answers
114 views
Is a process created in Oracle for every SQL statement?
After reading the Oracle docs, it´s still not clear for me if an Oracle process is created for every SQL statement or just for every connection (or not really related to both).
Any comment here would ...
4
votes
2answers
7k 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 ...
4
votes
2answers
341 views
Learning Oracle, does it matter if I install 10g or 11g?
I'm taking a class in Databases, they're using Oracle 10g in class, but I'm having some trouble installing the database here in Windows 8 (auth problems), so I thought I'd attempt 11g.
Is 11g ...
4
votes
4answers
4k views
Building Dynamic Oracle Where Clause
I'm working on application that uses dynamic query to do a select statement based on user input, after discussing security with DBAs they want me to convert my dynamic select statement into Stored ...
4
votes
4answers
127 views
Getting the value of 'foo' for the closest previous row with higher 'bar'
From this initial data:
select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9;
/*
FOO BAR
---------------------- ...
4
votes
2answers
1k views
Rebuild large Oracle 10g index with insufficient TEMP space?
One of my (Oracle 10g) tables has grown over time to about 840 million rows. An index (DATE, NUMBER(38)) on it occupies 38GB at present. I'd like to change the index (I think it could make good use of ...
4
votes
1answer
80 views
Do I need to use NVARCHAR rather than VARCHAR to properly store UTF8 characters in Oracle?
I want my Oracle database to properly store ALL possible UTF8 characters. If NLS_CHARSET is set to AL32UTF8 is that all I need to do? Or do I need to also make sure I use NCHAR and NVARCHAR rather ...
4
votes
2answers
6k views
Oracle Enterprise Manager 11g dbconsole “Internet Explorer cannot display the webpage”
The dbconsole is configured and started, but when I navigate to it, Internet Explorer shows the page stating that "Internet Explorer cannot display the webpage". This is the error that means that IE ...
4
votes
2answers
2k views
SQL Server Express vs. Oracle Express Edition vs DB2 Express-C
I have been trying to compare the features of the SQL Server Express, Oracle XE and DB2 Express-C, but find it difficult to compile all the differences into one place. Especially the current ...
4
votes
1answer
211 views
Dodgy causes of ORA-12549?
I am dealing with a rather massive ETL processing which keeps throwing me the ORA-12549 error.
Have been looking for problems in the database so far and nothing. The DBA says the database is "fine" - ...
4
votes
3answers
160 views
What methods are available to create a snapshot of an oracle database for testing purposes?
I am new to databases and have just been assigned to work with Oracle. We want to create a snapshot of the database for testing purposes that will allow us to revert back once we're done.
I'm a bit ...
4
votes
2answers
3k views
What is Key Preserved Table concept?
I read in the Oracle Documentation about key-preserved table in Updating Join Views Section.
However, I didn't find any simple to way understand it.
I hope to receive some simple conceptual details ...
4
votes
2answers
1k views
What is difference between Oracle Apps and Oracle Application Server?
I am very new to oracle, could anybody tell me what Oracle Apps and Oracle Application Server are.
4
votes
2answers
4k views
Oracle - Automate Export/Unload of Data
Oracle SQL Developer has an option to export the contents of a query result to various formats (CSV/fixed width/Excel/XML). Is there any way to automate it?
If not, what free tools are available ...
4
votes
2answers
1k views
How to handle multiple Oracle homes?
On my desktop I have an Oracle client and and Oracle ODAC installed in different homes. I need ODAC because there ADO components off the Client installation don't work correct with CLOB data.
I think ...
4
votes
2answers
12k views
How to connect to a remote Oracle server from Windows machine using PL/SQL Developer?
I understand that I need to configure database address in the TNSNAMES.ORA file but where should I put this file?
UPDATE
I defined the $TNS_ADMIN variable, created the TNSNAMES.ORA file, added new ...
4
votes
2answers
1k views
What's a free client for Oracle 8 on Linux / Ubuntu?
I need to connect to a legacy Oracle 8 database.
Oracle SQL Developer would be my tool of choice, but the current version doesn't support Oracle 8 anymore.
I tried Squirrel too, but the connection ...
4
votes
1answer
324 views
How will Oracle fill a datafile added to a tablespace?
I have a tablespace with one datafile which is 50% used. If I add a new datafile to the tablespace -
will the first datafile continue to be used until it reaches full capacity and only then will ...
4
votes
1answer
512 views
What is Sql Server Clustered Index in Oracle terminology
A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on.
If you look up with a name Ozgur you start O then z etc.
I would like a simple explaination for this ...
4
votes
2answers
2k views
Multiple copies (cp) with Oracle ASM asmcmd tool?
In order to import a transportable tablespace into an Oracle 11 database with ASM, we use the cp command of the asmcmd tool.
How can we copy more than one file at a time?
Wildcards are not ...
4
votes
1answer
935 views
How do I horizontally partition an oracle database table, and should I?
We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment.
Create ...
4
votes
2answers
586 views
Updating rows conditionally with a gapless ascending value
A user has a table they would like to update. Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less. The rows ...
4
votes
2answers
576 views
Identify which parameters were passed to a procedure (Oracle)
Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values. Is there a way from within the PL/SQL to determine whether a ...
4
votes
1answer
153 views
Is there a way to export an Oracle database to a CREATE DATABASE command?
I have an existing Oracle 11 instance, I want to export the database (which was created via DBCA) to an equivalent "CREATE DATABASE" command, handling the character set, etc. I'd also like to get a ...
4
votes
1answer
139 views
Retrieving data if control file is lost in oracle Database
Suppose if the databases control file is lost permanently (no backup).
Can we retrieve the data from data file in Oracle database?
4
votes
2answers
2k views
Oracle 11G - Index Rebuild VS GATHER_TABLE_STATS
Quick question, after populating my table, I noticed through my explain plan that indexes, in most cases, only seems to take effect after I perform GATHER_TABLE_STATS:
Exec ...
4
votes
1answer
120 views
How to use simple output parameters?
I'm new to Oracle, so I apologize for such a simple question, but I've already wasted several hours on it.
I have the following stored procedure:
create or replace procedure MyTestProc (outparam OUT ...
4
votes
1answer
214 views
Comments in EXPDB parameter file
Is there a way to put comments in a data pump export parameter file?
4
votes
2answers
241 views
Oracle build_plan_xml visualizer
Background: Please review my related question that involves SQL Server : Interpreting SQL Server's Showplan XML
I now have a very nice looking Execution Plan display for SQL Server (as a result of my ...
4
votes
1answer
2k views
Connection to an oracle db from another computer
I have installed Oracle 11g to an ubuntu, I can connect to DB using sqlplus from the localhost, But I cannot connect from another computer, I have configured my tnsnames.ora as followed;
ELIF =
...
4
votes
3answers
9k views
Oracle 11g - Create Database fails?
I've just installed Oracle 11g and linked it to SQL Developer.
I want to create a database for my django install.
CREATE database 'django';
Error starting at line 1 in command:
create database ...
4
votes
1answer
1k views
When is an application using too many sessions?
I'm working on a client-server application that uses OCI to communicate with Oracle. In the past our application often treated sessions as an infinite resource, quite possibly to the detriment of ...
4
votes
1answer
182 views
When refreshing materialised views does the index automatically refresh
When refreshing materialised views does the index automatically refresh
4
votes
1answer
519 views
What is the difference between these two create table statements?
a)
SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE);
Table created.
SQL> desc xml_tab1;
Name Null? Type
----------------------------------------- -------- ...
4
votes
4answers
129 views
Oracle 11g delete by unique key slow
We have been plagued by a deadlock issue for quite some time that relates to deletes and inserts on the same table. It happens sporadically, but had been increasing in frequency. It would manifest ...
4
votes
1answer
177 views
Does UPDATE INDEXES work for local indexes?
I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed):
CREATE TABLE table1 (col1 INT, col2 INT)
PARTITION BY LIST(col1)
(
PARTITION p1 VALUES(1),
PARTITION ...
4
votes
1answer
671 views
Oracle subquery column aliasing syntax?
I have something like:
SELECT a, b
INTO v_a, v_b
FROM (
SELECT x, y
FROM whatever
UNION
SELECT z, w
FROM something
) AS subquery (a, b)
Which of course doesn't work in Oracle ...
4
votes
1answer
334 views
How, if possible, can an .sql file be executed against an oracle database from a bash script
Is it possible to execute an sql file (a text file containing several SQL statements) against an oracle database from a bash shell command line (e.g. no graphical GUI and no interactive clients)
...
4
votes
1answer
193 views
Estimate space left in Oracle
I have a large Oracle table that need to be re-partition. I was wonder there is a way to know that I have enough space to create a copy of this table with new partition and swap the new one with the ...
4
votes
2answers
729 views
Where can I find autoextend events?
In Oracle, where can I see the history of DBFs autoextending? Or get the timestamp that a DBF last autoextended, and by how much?
4
votes
1answer
745 views
Calculate months between two dates
For a University course I've been given the following task
Suppose that we have an employee table like (first_name, last_name, salary, hire_date, ...). There is regulation which states that every ...
4
votes
2answers
6k views
Determine Oracle session client character set?
I know how the database characterset (NLS_CHARACTERSET in select * from v$nls_parameters;) and the client character set (the client environment setting NLS_LANG) interact.
What I can't find out ...
4
votes
1answer
3k views
oracle format specifiers: to_number vs to_char
SQL> select TO_NUMBER(123.56,'999.9') from dual;
select TO_NUMBER(123.56,'999.9') from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select TO_CHAR(123.56,'999.9') ...
4
votes
1answer
4k views
Query execution time in Oracle SQL Developer
I know I can use set timing on to see how long it took to execute a query after the execution is finished.
But is it possible to see current execution duration in Oracle SQL Developer while the query ...
4
votes
1answer
2k views
What does TOAD do, when I terminate script execution?
When it stops the execution of a long running script. The script terminating needs even longer then the time the script was running. I.e. I have to kill Toad.
2nd EDIT:
Just found that this is a ...
4
votes
2answers
79 views
Alternative to sequence and timestamp: uniquely ordering records in time
Oracle 11gR2 Exadata
I'm required to uniquely identify when records are created in time. Sequence caching means I cannot use a sequence-based ID and batching inserts means that all records inserted ...
4
votes
3answers
204 views
Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837)
I decided to reduce the memory allocation of a local developer instance, and was told that the following command did the job;
alter system set memory_max_target = 512M scope = spfile;
Which I guess ...
4
votes
1answer
3k views
How to transfer data using expdp and impdp commands?
I'm 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 expdp and impdp commands. I have ...
4
votes
2answers
1k views
Import Oracle full dump file to fresh Oracle installation
I am importing a Oracle dump fill created on a AIX Oracle 9.2 installation with a Full Export option to a Windows Oracle 10.2.0.4 on Windows 2008 R2 x64.
I want to import the file to a clean ...
