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
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
951 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
49k 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
931 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
2answers
19k 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
21k 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
16k 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
168 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
1k 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
1answer
22k views

Importing schema to new or different tablespace

Is there a convenient way to import a schema into Oracle 11gR2 using a single new or different tablespace than where the data originated? As an example, I have exported BLOG_DATA from OLDDB, where ...
5
votes
1answer
154 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
628 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
931 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
19k 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
262 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
165 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
854 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
528 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
5k 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
6k 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
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
803 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
299 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
10k 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
640 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 ...
5
votes
2answers
5k views

How to Install/Configure Oracle Instant Client on Ubuntu

Installing the Oracle Instant Client on Ubuntu/Debian based systems is awkward since Oracle is RPM centric regarding its Linux support. What's the best way to easily install and configure the Instant ...
5
votes
2answers
1k views

Oracle View Ignoring External WHERE Clause

Good day, This one has me stumped. I have a rather nasty developer query that I would like to store in a non-materialized Oracle view. The text for the view itself is a bit long to list here, but ...
5
votes
1answer
2k views

Oracle extract slow function call from a WHERE clause

I have an oracle table in which dates are stored as the number of minutes since a specific date. It looks something like this: CREATE TABLE MY_TABLE ( SOMETHING NUMBER(15,1) NOT NULL, ...
5
votes
1answer
2k views

Oracle aggregate functions slows query down massively

I have a table with ~12M rows of data in it. Here is the table structure: SYSTEM_ID BATCH_ID MEASUREMENT_INDEX, PARAMETER_ONE, PARAMETER_TWO The primary key is made up of the first three columns. I ...
5
votes
2answers
4k views

Help to understand explain plan in Oracle

I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with ...
5
votes
1answer
5k views

How can I get the Oracle TNSListener to start after changing my host name?

When running lsnrctl status I can see that it is using the old host name, but I don't know where it is pulling that value from. My Oracle installation is 11.2.0 on Windows7 x64, if that makes any ...
5
votes
2answers
3k views

Oracle's Merge v. Select->Update/Insert

What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value? I'm trying to optimize a database, but I haven't found any speed tests comparing the two. I found ...
5
votes
1answer
559 views

Silent truncation of last character when using SSIS with Oracle

I am using SSIS to export data from an Oracle database. Sometimes when I export a VARCHAR2 column to NVARCHAR the last character gets truncated (silently). It's completely random as far as I can tell. ...
5
votes
1answer
15k views

How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?

I have an Oracle database, and I would like to create an ERD in Visio 2007 Pro. I have so far looked at DBMS_METADATA.GET_DDL commands to generate DDL. I know that I can import an Access DB into ...
5
votes
2answers
225 views

Implementing uniqueness with Oracle

For example, say I have something like CREATE TABLE COMPANY(id int not null primary key, ...); CREATE TABLE DEPARTMENT(id int not null primary key, company_id int not null, CONSTRAINT ...
5
votes
1answer
72 views

What is Utility_DSN?

I'm not exactly sure when it happened, but I've started noticing that some of the hosts in my Enterprise Manager grid have a target named UTILITY_DSN. Does anyone know what this is? The one I noticed ...
5
votes
1answer
1k views

Oracle concatenation of queries and function index usage

I have a problem with something I can't understand about indexes in Oracle 11g. We can create test data with: create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 ...
5
votes
2answers
5k views

Current isolation level in oracle

How to get the current (default) transaction isolation level in oracle?
5
votes
2answers
2k views

EXP-00085 warnings while doing full backup

During a full export of my Oracle 10.2.0.2 database, I encounter the following warnings [...] . exporting post-schema procedural objects and actions EXP-00008: ORACLE error 6502 encountered ...
5
votes
3answers
2k views

Do roles have an owner in oracle?

Suppose that I have logged in to oracle database using user X, then user X creates some roles using create role command. I want to know that is user X the owner of the roles? Can I select all roles ...
5
votes
1answer
160 views

how do I configure a new Oracle 11g instance to match the configuration of the old Oracle 10g server

How can I compare two Oracle database servers which are on two different machines? We need to ensure that the new server is setup correctly. For example, one machine is running Oracle 10g on ...