All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

2
votes
1answer
19k views

Expanding system tablespace

I need to add new data file into the system tablespace because its running out of space. Are there any precautionary measures I should consider since this is a production environment?
-1
votes
2answers
508 views

Oracle SQL Query/SubQuery Single column with mutilple data [closed]

Id File-Name Descrption 1 yyyy Started 2 yyyy incompleted 3 rrrr Started 4 tttt started 5 uuuu started 6 rrrr completed 7 tttt completed 8 uuuu ...
6
votes
1answer
19k views

Difference between database dump (export) and database backup?

What is the drawback using only EXP or EXPDP command for a database dump (and considering it as a backup) instead of taking backups using BACKUP DATABASE command (via RMAN)? What is a difference ...
12
votes
1answer
88k 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 ...
2
votes
1answer
878 views

How to Recover My Oracle 9i Database From Just The Datafiles

The state of the system when I started was that there was a single, working instance of Oracle Database 9i. I proceeded to install Oracle 11g choosing to Install Software Only. Upon completion, ...
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 ...
0
votes
1answer
2k views

Where to put login.sql in Windows environment to configure SQL Plus?

I installed Oracle 10g XE on my Windows Server 2008 R2. Now I would like to configure SQL Plus to use login.sql for login settings. I created a login.sql file and put it into a C:\users\myname ...
1
vote
1answer
745 views

Force chain to start immediately after completion instead of on a timer

Is it possible to force an Oracle chain to restart immediately after it finishes executing, instead of on a timer? I have only found ways to do a temporal restart, which doesn't really work for me as ...
5
votes
3answers
3k views

Partition pruning with multiple date columns

I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are ...
11
votes
1answer
17k 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 ...
4
votes
2answers
1k views

Materialized view for the last 24 hour period

I am collecting spatial point data that has an associated timestamp with it. I want to create a view of this data for the last 24 hour period. Because I am piping this spatial data through ArcGIS ...
1
vote
1answer
662 views

Select Count(*) FROM with-clause-named table order by a, b not working

I have a series of tables defined in a stored procedure to eventually return to the application like this, then culminating to a simple table which contains distinct values from a previously defined ...
6
votes
2answers
5k views

SQL developer: Setup debugger for plsql

I'm trying to debug remotely pl/sql. But I can't - database returns me an error. What should I do to fix this and start debugging ? ORA-30683: failure establishing connection to debugger ORA-...
0
votes
1answer
330 views

How can I use “select distinct” and have a range for my rows? [closed]

I wrote this code in PL/SQL developer and was wondering how I could have one row per range of t.ttt in the select distinct columns. Here's the SQL: select count (t.aaa), sum (t.bbb) from xxx t where ...
14
votes
3answers
39k 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: ╔══════════╦══════════╗ ║ ...
2
votes
2answers
5k views

Trouble setting up Nagios to monitor Oracle services

I've got an install of Nagios XI that doesn't seem to want to talk to any of my Oracle services here. I've pulled out the monitoring command and am running it manually, after setting ORACLE_HOME and ...
5
votes
1answer
4k 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 ...
2
votes
1answer
642 views

Help with SELECT queries

I need help with some queries. I struggled do find out how to do it, but I think I finally caught on or at least got the results I was looking for in the database. These are the tables (given by my ...
0
votes
1answer
330 views

What structure is used for storing CONTEXT, CTXCAT and CTXRULE Oracle text indexes?

AFAIK there are three major structure types used for storing Oracle index data: B-tree, R-tree and Bitmap. All indexes use one of these structures. However, not sure about CONTEXT, CTXCAT and CTXRULE ...
1
vote
2answers
245 views

What are the alternatives for prefixed indexes in Oracle?

You can create an index and define prefix length, so the index will store only first starting symbols from each of column value. It looks like this in MySQL: CREATE INDEX table_idx ON emp(last_name(8)...
0
votes
1answer
3k views

When hash indexes should be used in Oracle?

Hash indexes are usefull when you have a large table with URLs and you need to query by them. So, a solution would be to have an additional column "url_crc32" that will be filled with hashed URL value ...
2
votes
1answer
1k views

oracle streams - how to get last successfully applied scn for crashed apply?

I have a downstream Streams replication set up between Oracle 9i (source) and Oracle 11g (destination). The process is still in developlment stage, so the whole setup is not stable yet. Sometimes my ...
-2
votes
1answer
624 views

Missing quotation - openquery to oracle [closed]

I am not able to successfully run a code with openquery to a oracle server. Please do not take into account field names/data types, as I had to present only a part of the whole procedure. It's quite ...
3
votes
1answer
2k views

halt on error in a sqlplus script

I am deploying pl/sql code using several sql files that are called with @@file. If a package got a compilation error the script continues to the end. Is there a way to stop on every compilation error?...
3
votes
2answers
8k views

SQL set allowed values for a column

I want to make an ALTER TABLE expression which adds a new column and sets a default value and additionaly defines the allowed values for that column. It's a text column, and allowed should be only '...
2
votes
2answers
8k views

Oracle 11g: “With Clause” very slow

I have written a query utilizing Oracle's with clause. I have identified one particular with clause in a larger query to be really, really slow. Here are three versions of the same query, they all ...
3
votes
3answers
2k views

Increase Oracle buffer cache gets slower

So we're contemplating increasing our Oracle 11gR2 buffer cache. They said they tried it in the past and things appeared to get worse - so they undid the change. They, however, have no idea why ...
0
votes
1answer
3k views

How shared nothing and shared everything architectures look like in Oracle?

Oracle has partitioning and clustering features. Partitioning enables to split table or indexes into multiple tablespaces and store on various servers. So it's done manually. Clustering enables ...
2
votes
2answers
478 views

Support ticket system - when/how to move old tickets?

I don't have access to the partitioning feature, but consider a support ticket system with tens of thousands of tickets being opened everyday and taking about a week to a couple of months in getting ...
0
votes
1answer
3k views

The four properties of a primary key

In our dbms class, these were the four properties of a primary key discussed for Oracle. Unique Not NULL Fully functional dependency Indexed I understand all the properties except the 3rd one (...
2
votes
1answer
73 views

Transaction stays in capture queue

I set up simple bidirectional db replication using Oracle Streams. I used enterprise manager for this. Streams are active and their status is ok. The problem is that the replication is very slow. What ...
0
votes
0answers
50 views

Oracle User Profile for REST API

What sort of profile would you recommend for an Oracle user that is manifested through a RESTful API; particularly where there will be a lot of asynchronous requests? So we're talking about a ...
-1
votes
1answer
815 views

How to get how many rows are inserted in ORACLE?

How do I get how many rows are inserted after executing an INSERT statement? I'm using Oracle 11r2 with MyBatis in Java.
1
vote
1answer
1k views

Oracle not trusting statistics after an import?

We are seing some strange behavior in the way Oracle is handling statistics of an imported schema. We build a schema on the dev system, analyze if and import into the production with the stats. For ...
0
votes
1answer
205 views

Oracle TRANSLATE Not Returning Expected Result

We are trying to execute TRANSLATE on a couple hundred rows of data. We have a temporary table containing ID's to rows that need to be updated using TRANSLATE. The odd part is if we attempt to select ...
4
votes
1answer
5k views

Load Numerical Column with Variable Comma

I'm using sqlldr to load a file which has a numerical value as one of its fields. The problem is that in some of the records the number has commas whereas in other, it doesn't. So doing something ...
3
votes
0answers
2k views

Oracle high Paging Faults

What are the reasons that lead to terrible high Paging faults. The following is the topas information and background about the Environment. OS: AIX 6.1 Softwares: Oracle 11gR2 Paging Space:30720 ...
0
votes
2answers
2k views

How to merge Oracle data

There are two schemas in Oracle11g. For example db1 and db2. They have same structure of tables and different data to each other. Now I want to merge data on db1 with data on db2 with no duplication....
0
votes
0answers
2k views

Oracle GoldenGate - adding trandata

ADD TRANDATA <table name> will add the supplemental logging to all columns of table if the table does not have either primary key or a unique key. Once we applied the ADD TRANDATA how do we ...
2
votes
1answer
7k views

Installing Oracle 11g XE on Ubuntu 12.04

I have spent the last 6 hours trying to install Oracle 11g on Ubuntu. Obviously, I'm not the only one who has struggled with this, as there is a lot of (often misleading) "documentation" online on how ...
2
votes
1answer
2k views

Database Link from Oracle 10g on Windows over to MySQL database

Didn't see any answers on this subject so hoping for a miracle! :-) I've got an Oracle 10g database running on Windows Server 2003 and I'd like to create a database link to an external MySQL database ...
0
votes
1answer
6k views

Select fields from multiple tables

I want to make a query a little complicated, and I'm not able to do it alone. I have the following tables: Table color: id_uo P id_cor PF id_area F text_cor Table area: id_area P ...
1
vote
0answers
1k views

Oracle 10g - Sudden change in execution time for specific query

After the DBAs fiddled with some parameters in our Oracle 10g Production Environment, most queries became a little faster. But a single query (a quite complex one) became dramaticaly slower - it went ...
2
votes
2answers
2k views

Is it possible to monitor role and privilege grant/revoke using triggers?

Is it possible to monitor role and privilege grant/revoke using triggers? I' aware of doing that using Oracle audit tools however, it's interesting is it possible to do that using triggers.
3
votes
1answer
5k views

What is the difference between ON SCHEMA and ON DATABASE triggers?

We can create the database trigger on concrete schema event (ON SCOTT.SCHEMA) or on all schemas (ON SCHEMA). However, we can also use ON DATABASE when creating database trigger. What is the difference ...
5
votes
1answer
11k 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 ...
9
votes
1answer
4k views

Why am I NOT getting a mutating table error in trigger?

It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation: A mutating table is a table that is being modified ...
1
vote
2answers
108 views

Special Oracle Order

I have a table (emp) with two columns in ORACLE: COMPANY OFFICE EMP ------------------------ 9999 00001 emp1 9999 00001 emp2 9999 00002 emp3 9999 00002 emp4 ...
0
votes
1answer
2k views

Force number precision in a view with Union

I am using Entity Framework and Oracle. Oracle's ODAC seems to have a known bug where data of type NUMBER is cast to Int64 by Entity Framework. I thought a view would assist me but CREATE OR REPLACE ...
1
vote
2answers
2k views

Achieve log transfer to standby (Data Guard)

I am not able to transfer my archive logs from primary to standby. I executed the following query: archive log list Database log mode Archive ...