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

4
votes
4answers
9k 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 ...
0
votes
1answer
210 views

Object-oriented programming CRUD design pattern

At first I have posted this question at http://cstheory.stackexchange.com/questions/17170/object-oriented-programming-crud-design-pattern - that's because I think it's a subect question, and it can ...
5
votes
2answers
349 views

Defining constraints in `CREATE TABLE` statements

Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the ...
2
votes
1answer
134 views

Oracle Backup and Recovery Issue

I have backed up database located on Host1 and restored/recovered it on Host2. After a while, I backed up DB on Host1 again. Now I want DBs on Host1 and Host2 be the same using last backup. How can ...
1
vote
3answers
1k views

Instance Startup Modes & Shutdown Modes

So, I know that there are 3 modes of startup modes available for the instance which is NOMOUNT, MOUNT, and OPEN. What I want to know is in NOMOUNT mode it is stated that the instance started but still ...
0
votes
0answers
249 views

Oracle Alternative to EXISTS EXCEPT

I wonder if there is an equally elegant solution for Oracle for the problem posted and answered here: Comparing Columns that can contain NULLS - is there a more elegant way? I can't get this ...
-1
votes
2answers
89 views

Combine two SQL statements to a single statement [closed]

My question is related to SQL. I need to pull values from two different tables. I am not successful in doing that. I am able to join the two tables with only a single condition but not many values ...
4
votes
1answer
2k views

Update oracle sql database from CSV

I tried google at first but no luck. Is it possible to update tables from csv file? I am using SQL developer and I am using a script to export edited rows to a csv file. I would like to update the ...
0
votes
1answer
142 views

List partitioning or filtering a non-partitioned table, which incurs lower cost?

Consider a list partitioned table PARTITIONED__SOURCE_TBL on column period, and non-partitioned table SOURCE_TBL also having the same columns. Assume all tables are adequately indexed. Set 1: ...
0
votes
2answers
96 views

Multiple instances in Oracle

I'm really confuse with the term instance and database. So, my first question is how database and Instance are related in Oracle, I mean, whether an instance is associated with one/multiple ...
3
votes
1answer
389 views

Convert SQL Server UPDATE Statement for Use in Oracle

I can not get this UPDATE statement to work in an Oracle environment. It was written for SQL server. I am looking for some guidance on how to convert it. UPDATE SOB SET COA = CASE ...
0
votes
1answer
307 views

PL/SQL for Updating 1 Column in Table A Where PK = a row in table B

Using an oracle DB, 11g r2, I need to write a pl/sql which updates one column in table A where the PK exists in table B, with commits ever ~50,00 rows. 3200000 rows are involved in the update, and ...
0
votes
1answer
145 views

Oracle equiv of T-SQL UNION ALL query

In T-SQL I can accumulate the results of two unioned queries using the following syntax: SELECT Q.a, SUM(Q.b) FROM ( SELECT f1 as a, f2 as b FROM TBL1 UNION ALL SELECT f1 as a, f2 as b FROM TBL2 ) ...
0
votes
0answers
267 views

DBA group in Linux

When we need to install Oracle database in Linux then the user should be in dba group. What is this group and why the user should be in this group for installing Oracle database. What happens if the ...
3
votes
1answer
2k views

Oracle insert through dblink performance issue

I'm trying to do an insert as select to a table via dblink. The query looks like this: Insert into MARS_SUPERVISORS_PUB@EDHPUB select {...} from edhper a, edhper b where ...
1
vote
1answer
294 views

Using Oracle hierarchical queries for transposing field value into rows

One column in my table stores list of values separated by comma (I know it's a poor design, but refactoring is not an option at the current moment). I seem to come up with a working solution but I'm ...
4
votes
3answers
2k 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 ...
1
vote
0answers
2k views

Oracle 11g R2 on CentOS 6.3, and Net Configuration Assistant

this is my first time to install Oracle 11g R2 on Linux. I made all instructions in Oracle documentation. But in the installation process, the Net Configuration Manager is failed. I continued the ...
0
votes
1answer
291 views

Oracle trigger to update columns daily

I'm trying to create a trigger that would run twice daily and do updates based on a specific condition. To be run on Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi We have Table A ...
0
votes
1answer
1k views

ORA-01435: user does not exist

I am trying to run the SQL queries coded on my friend's laptop onto mine. I have imported the .sql file into SQL developer and I'm trying to run it.However, I keep getting the error: 01435. 00000 - ...
2
votes
1answer
867 views

Specify Server for DBMS_Scheduler Job in Policy Managed RAC

A unit test requires a dbms_scheduler job to run on the same RAC node as the unit test is being run from. I know that with an Admin managed database this could be done by creating a service that ...
4
votes
1answer
5k 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') ...
3
votes
1answer
2k views

Oracle: Non key-preserved table should be

I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join. I've searched around the site and found a lot of advice on what key-preserved ...
5
votes
1answer
11k views

How to resolve ORA-01031: insufficient priviledges error on Windows installation?

I installed Oracle 11G on my Windows 2008 R2 Server. I also installed Oracle Client Libraries using separate installation media. After the client installation, when I try to log into the database ...
1
vote
2answers
207 views

Expand sparse table with self outer join on distinct values

I have this: g1 | g2 | x ---------+-----------+----- New York | Monday | 210 New York | Tuesday | 258 Chicago | Monday | 30 Chicago | Wednesday | 25 LA | Thursday | 40 ...
0
votes
0answers
143 views

No network ACL, yet my user can successfully connect to external server

This one's had both me and our dba scratching our heads. We have an Oracle 11.1.0.7.0 instance, DEV, with a user who can successfully connect to our ldap server, e.g.: DECLARE l_session ...
0
votes
1answer
4k views

SYSDBA password in Oracle DB

I am connecting to Oracle DB using sysdba: sqlplus / as sysdba What is the default password for sysdba user. I tried in net and found out the default password is change_on_install but it is not ...
4
votes
1answer
144 views

Oracle BLOB store performance

LOBs in Oracle are stored in-row when they are less than 4K in size and out-of-row otherwise. I'm wondering how do SECUREFILE storage options COMPRESS and ENABLE STORAGE IN ROW work? I have a lot of ...
0
votes
1answer
221 views

Invalid rowid error

I'm trying to see, how UPDATE Lock helps to minimize error while dml (delete/update) operations. declare cursor update_lock is select empno from emp where deptno=&no for update of sal; num ...
7
votes
2answers
4k views

Oracle 11g: performance improvements of inserts

I have a table of 500 millions of rows (and growing) I did the following to improve performance of inserts: On database side: dropped all indexes and constraints disabled logging On application ...
1
vote
0answers
107 views

How can I count the total number of document indexed by my oracle.text index?

I know I can use ctx_query.count_hits to count the number of document matching a query, and similarly, I can use count(*) where CONTAINS(...) But these are only options if I actually have a query. ...
2
votes
2answers
4k views

Why can't we write ddl statement directly into the PL/SQL block

Why can't we write ddl statements directly in PL/SQL block, for example when i write CREATE OR REPLACE PROCEDURE test IS BEGIN truncate table table_name; // error END test; / But, CREATE OR ...
1
vote
1answer
460 views

Materialized view log not updating

Under what conditions would a materialized view log not update? I must be missing something. I've got a 10gR2 database. I can create the following MV logs: CREATE MATERIALIZED VIEW LOG ON linked_t1 ...
0
votes
0answers
60 views

Troubleshoot abnormal connections

We experienced extreme slowness of login on 6 Mar in 1 of our Oracle servers (Solaris 10) running Oracle DB. During that period of time, our firewall had detected and dropped many TCP connections ...
9
votes
2answers
1k views

100 TeraBytes Capacity Database - Resources and Time Estimates

I am working on a 'back of the envelope' calculation for a 100TB reporting database setup. I am seeking out thoughts from the experts here. Proposed environment: Storage Capacity ~ 100TB Tables ~ ...
1
vote
1answer
391 views

What are the things need to be considered while creating tablespace?

I am creating temporary/permanent tablespace on Oracle. As I am going to create an application that is transactional there will be traffic. I bumped into concept called tablespace in oracle. What kind ...
1
vote
1answer
1k views

Does 'update global indexes' not work if the index is already unusable?

I have a partitioned table in Oracle 11g with a PK (global index). If I truncate one of the partitions with ALTER TABLE tbl1 TRUNCATE PARTITION p1; the global index has the status UNUSABLE. If I ...
2
votes
1answer
2k views

When is data moved during an Oracle partition split?

I need to split an Oracle partition, and I'm confused about whether or not Oracle is going to physically relocate the data when I execute the split. Here's what I intend to do: alter table ...
1
vote
1answer
382 views

oracle huge hash_area_size

I am wondering what is the maximum setting for hash_area_size parameter? I'm trying to perform a huge join which ends up allocating 10Gb of temp space and completes in an hour. 10Gb is not that much ...
1
vote
2answers
1k views

In Oracle, how can I give one user privileges to alter the schema of another user?

I have an Oracle DB (10.2), and I have a consultant (Dogbert) that I want to do some work on it. In particular they need to alter the table structures of tables in a schema (we'll call it Wally) that ...
0
votes
2answers
145 views

Is there any way to find out when particular view was compiled?

I have an Issue with a view, recently I made changes to the view and committed the code (in SVN). Code was moved to another repository and compiled, but my modifications are not reflected. I searched ...
0
votes
1answer
2k views

Import data from Oracle to SQL Server [duplicate]

I have an Oracle database in a remote server. I want to import data from it to SQL Server on a local machine using a stored procedure or job shedule but I don't know how can I do it.
2
votes
2answers
2k views

Materialized View does not import properly when importing a db for a second time in another schema

When I import a database with materialized view mv_mt in just one schema I get no errors. create materialized view mv_mt refresh complete next trunc( sysdate ) + 1 as SELECT sysdate, media_type.* ...
0
votes
0answers
143 views

Materialized view for a yet-to-be-defined DBlink

In our product we have a main application with an oracle 11g DB and an optional module with another DB. We want the two to communicate through a dblink and I was told to put some code for the ...
0
votes
2answers
144 views

How to influence Oracle backup output size?

I have two environment one Prod one Dev. Dev is created by Prod data and both have a similar database size. However, the backup output size varies greatly. Prod is about 300 M while Dev always above ...
6
votes
2answers
986 views

Making the Oracle optimizer use a virtual column to find out about a partition

I have a large table that has three columns like this: "START_DATE" DATE, "START_VALUE" NUMBER(10,7) "START_DATE_VALUE" NUMBER(18,7) GENERATED ALWAYS AS ( (extract(YEAR FROM START_DATE) * ...
5
votes
1answer
383 views

Enforce data integrity with a recursive table

I have an existing oracle 11g database schema that works with a web application. I am planning an expansion to the application so a web service can do data operations on the database. As part of the ...
2
votes
1answer
766 views

Can a view affect ALTER TABLE commands?

I provide a database system to a number of customers. The database is installed locally on Oracle. We periodically upgrade the database structure (either adding new fields, renaming old fields, ...
2
votes
1answer
222 views

Consistent Database Export of Oracle Database

I'm quite confused and I need some clarification! I do full export of database with Export Pump (for replication/duplication). What I usually do is: SQL>SHUTDOWN IMMEDIATE SQL>STARTUP ...
2
votes
1answer
3k views

Completely uninstall Oracle server on Linux

I have searched lot in Google for how to uninstall Oracle on Linux, but I can't figure it out. I think that I need to reinstall the server because I get a lot of errors when I try to startup the ...