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) ...

learn more… | top users | synonyms

5
votes
1answer
8k views

Why I don't need to COMMIT in database trigger?

We can't COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. For example, suppose there's a database ...
5
votes
3answers
579 views

What are the top 5 design decisions which need to be made when implementing a database?

I am implementing an Oracle System to store event alerts from an application so I guess all the schema, database design will be handled by the application through a sript. I am new to Database ...
5
votes
4answers
12k 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 ...
5
votes
4answers
174 views

Replicating only changes made by a specific database user

I am performing parallel testing. We have upgraded the legacy batch jobs to a new framework which we need to test against the existing batch jobs. In the existing setup, I have web application which ...
5
votes
3answers
6k 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).
5
votes
4answers
241 views

What's the best way to list groups of values that are contained in other groups

For example, starting with a table like this: create table t as select 'A' as x, level as y from dual connect by level<=5 union all select 'B' as x, level+2 as y from dual connect by level<=5 ...
5
votes
5answers
15k views

Simplified/automated datafile free disk space reclaim

On Oracle version 11g: After Googling, I can't find a simple way to reclaim free space after deleting a table. I've found a lot of explanations, telling how the datafile becomes fragmented, the big ...
5
votes
3answers
898 views

Alternative for OPTIMIZER_FEATURES_ENABLE

I've installed a PHP driven application in a client's 9i server (Oracle9i Release 9.2.0.1.0 - 64bit Production). Certain queries are having an awful performance (it can use from 15 minutes to hours ...
5
votes
3answers
823 views

Database replication and consistency check

I got a question about 2 databases (oracle 10g) that I have, let's call them A and B. A have some information(in various tables) and I want B to get a partial copy of some tables from A, and ...
5
votes
2answers
23k 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 ...
5
votes
1answer
5k views

Export table without BLOB column

I have a big table with BLOB objects and some references from other tables to this one. I prepare dev and test schema for my app by making expdp (data pump) from production, then anonymizing data on ...
5
votes
3answers
68k 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 ...
5
votes
3answers
7k views

Trouble killing sessions then immediately dropping users in Oracle 11g XE

I would like to be able to kill active sessions by a given user, and then drop that user, and do so within a single database call. Here is what I have so far: DECLARE cursor c is select * ...
5
votes
1answer
566 views

If an 'after' DDL trigger causes an error, is the DDL rolled back?

It has been suggested that DDL is logically performed something like this: begin COMMIT; perform any appropriate pre-DDL trigger code; do the ddl; perform any appropriate post-DDL ...
5
votes
2answers
1k views

Oracle clean up

I have an Oracle database on a VMWare image that seems to be slowly eating all my available disk space. I would like to know if there is a command or script that would perform some kind of clean up of ...
5
votes
3answers
1k views

What could be the reason for disallowing a sub query in the values clause?

For example SQL> create table dates(d date); Table created. SQL> insert into dates select sysdate from dual; 1 row created. SQL> select * from dates; D --------- 28-MAY-11 SQL> ...
5
votes
1answer
58k 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 ...
5
votes
4answers
998 views

optimizing a compare query

Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
5
votes
1answer
2k views

Comments in EXPDB parameter file

Is there a way to put comments in a data pump export parameter file?
5
votes
2answers
20k views

Grant create permission on a specific schema in Oracle 11g

I have two users A and B. I want to grant B the permission to create, drop, etc. all tables in A's schema. As far as I can see, I can grant B full access to all schemas not a specific one. Is this ...
5
votes
1answer
1k 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 ...
5
votes
3answers
22k 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 ...
5
votes
2answers
17k views

How do I allow users to see grants, view definitions, PL/SQL, etc in a database without granting them access to change those objects

As a developer, I need to be able to access the design of the database/database objects from production so I can ensure I'm working with the latest and greatest version of the objects I'm developing ...
5
votes
1answer
192 views

Why does the CBO not chose a lower cost skip scan without stats?

In the script below, the CBO chooses an INDEX FAST FULL SCAN over an INDEX SKIP SCAN (this is illustrated by the queries with the UNION ALL but you can demonstrate it is true with just explain plan ...
5
votes
3answers
8k views

Bulk database update/insert from CSV file

I am implementing application specific data import feature from one database to another. I have a CSV file containing say 10000 rows. These rows need to be inserted/updated into database. There ...
5
votes
2answers
2k views

Cardinality rule for bitmap indexes

The Oracle documentation includes the following advice: A bitmap index should be built on each of the foreign key columns of the fact table or tables In that reference, there is even a bitmap ...
5
votes
2answers
14k views

sqlnet.ora or tnsnames.ora changes require reboot?

Anytime you change Oracle's sqlnet.ora or tnsnames.ora files, does the system require a reboot? In my instance, I only have the Oracle client installed on the machine I'm referring to, but out of ...
5
votes
1answer
156 views

Retrieve Indices of Associative Array as Collection

In PL/SQL, suppose I have some associative array defined as follows: declare type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER; a_arr a_arr_t; I then, sparsely, populate the ...
5
votes
2answers
3k views

Session in “Sql*net message from client”, but is active

I've Googled around, and my impression is that Sql*net message from client suggests the Oracle DBMS is waiting for the client to send new commands to the DBMS, and therefore any time spent in ...
5
votes
2answers
4k views

Restricting database access per IP addresses

I have filtered IP addresses to an access DB server like this: Set the sqlnet.ora tcp.validnode_checking = YES tcp.invited_nodes = (localhost, 192.168.100.130, 192.168.100.186) But in my ...
5
votes
2answers
9k 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 ...
5
votes
2answers
643 views

Oracle Advanced Queuing LIFO

With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
5
votes
2answers
1k views

Oracle partition by interval using persian calendar

Oracle supports Persian (Jalali) calendar in DDL queries, I can easily say: select to_char(register_date, 'YYYY-MM-DD', 'nls_calendar=persian') from my_table; I created a table as: create ...
5
votes
4answers
22k views

Oracle startup error (could not open parameter file)

I installed the Oracle 11g server on Ubuntu 12.10. But I can't start the server. When I start up the server, the following error occurs. SQL> startup ORA-01078: failure in processing system ...
5
votes
1answer
270 views

Oracle: DB *partial* constraint?

I am pretty new to Oracle and the database world. I am not sure if the title makes any sense at all, so I'll just go ahead and explain my situation. I currently have a table at hand that records ...
5
votes
2answers
177 views

Product of n Rows

From this data (assuming the number of rows is not known in advance): with q1 as (select mod(ora_hash(level),5) c1 from dual connect by level <=4) select * from q1; /* C1 -- 2 1 4 1 */ I ...
5
votes
2answers
3k 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 ...
5
votes
1answer
918 views

Are there performance benefits to turning off archivelogging? Is it wise?

The first part of the question is pretty simple; has anyone run a DB in both archivelog and noarchivelog mode? Were there any measurable performance benefits to turning off logging that you could ...
5
votes
1answer
5k views

Oracle RAC failover not working correctly

I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both ...
5
votes
1answer
1k views

Do I need to enumerate the columns used in a materialized view when creating the MV log?

I'm currently creating materialized view logs on a whole bunch of tables in order to support materialized views with FAST REFRESH. The materialized views we're creating contain approx 30 columns from ...
5
votes
3answers
616 views

Bad use of Oracle Tablespaces

I have a doubt about the use of oracle tablespaces. I've decided to define for each schema of the datababase two different tablespaces (one for data, and another one for indexes). The problem is that ...
5
votes
3answers
5k 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 ...
5
votes
2answers
6k views

Clean trace files on Oracle 11g

The partition containing my Oracle 11g installation began to fill up with trace files and when I went looking for what are these I've found this disturbing post about how to disable the trace file ...
5
votes
2answers
7k views

Database logon trigger

To block a particular user and IP combination on an Oracle Database , I created the following trigger, and compiled without errors. Create or replace trigger you_may_not_login after logon on database ...
5
votes
2answers
2k 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?
5
votes
1answer
2k views

Oracle 10g express database link to Oracle 8i

I upgraded a 9i install to 11g with only one problem. Database links between 11g and our legacy 8i database are no longer supported. I have to transfer data from 11g to 8i two or three times a day ...
5
votes
3answers
840 views

Gathering information to tune oracle row prefetching

I'm trying to determine a value for the number of rows an oracle client will prefetch (i.e. OCI_ATTR_PREFETCH_ROWS, exposed as setFetchSize in JDBC) that will minimize the number of server round trips ...
5
votes
1answer
304 views

DBA's Scope in datawarehousing Projects

As a DBA trainee, what needs to be known in order to be part of datawarehousing projects? I mean what resources are suggested to have an overview knowledge of how PL/SQL and other concepts are used ...
5
votes
1answer
11k 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 ...
5
votes
1answer
645 views

Case Behavior Changed Between 11.2.0.1 and 11.2.0.2

I have a case open with Oracle and they say they can't reproduce this. Running the following in 11.2.0.1 or lower produces two rows from both of these queries, but in 11.2.0.2 the second query ...