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) ...
5
votes
1answer
112 views
What must be in place to validate a XMLTYPE against a schema?
I have a procedure that generates an XMLTYPE and I want to validate it against a schema. The problem is that there seems to be a permissions issue running createSchemaBasedXML because when I run the ...
0
votes
0answers
74 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 ...
1
vote
1answer
58 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 ...
0
votes
1answer
49 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:
...
1
vote
4answers
111 views
Copying my Oracle 10g Express database to another PC
I have Oracle 10g Express. How can I make a copy of my database and application?
I don't want to make a back up, I want to move my DB and application to another PC.
3
votes
2answers
260 views
Shrinking the SYSTEM tablespace in Oracle
Our SYSTEM tablespace grew out of control because of the SYS.AUD$ table.
We have truncated SYS.AUD$ but the datafile is still very big (~30G).
Resize doesn't work because the file contains used data ...
1
vote
0answers
187 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
85 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
0answers
38 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 ...
3
votes
1answer
85 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 ...
3
votes
1answer
230 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 ...
0
votes
1answer
58 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 - ...
0
votes
2answers
42 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
1answer
108 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
59 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 ...
5
votes
1answer
290 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
1answer
103 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
50 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 ...
6
votes
2answers
286 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
29 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
1answer
60 views
Expanding parent-child tree with additional tables
I have readonly access to a table (Oracle database) which contains a hierarchical structure, represented via parent-child.
I need to add another table, which will expand the existing hierarchical ...
2
votes
2answers
180 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
70 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
39 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 ...
4
votes
3answers
188 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
1answer
67 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
110 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 ...
0
votes
2answers
114 views
A stored procedure to bulk load in Postgres or Oracle given a URL
Is there a stored procedure available in Postgres or Oracle that does a bulk load
given a URL, as in:
url_bulkload csvFile.ctl
Where csvFile.ctl is
#
# csvFile.ctl -- Control file to load CSV ...
2
votes
0answers
75 views
Oracle: How to gather stats in a logical standby database?
I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced.
Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On ...
0
votes
0answers
21 views
import .dmp file created in OS W2K to Win 2003
I have .dmp file exported using oracle 8 and OS Windows 2000. Can it be imported in Oracle 8 and OS Windows 2003?
4
votes
1answer
252 views
Difference between database vs user vs schema
I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)?
If they are same then, what are the similarities between them? ...
0
votes
2answers
55 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
79 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.
5
votes
2answers
160 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 ...
0
votes
1answer
90 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 ...
4
votes
1answer
120 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 ...
1
vote
1answer
59 views
Finding why an Oracle listener got automatically restarted
I got the following message in the <listnername>.log file. I want to find out the reason why it got automatically shutdown and restarted again with different PID.
03-MAR-2013 03:00:57 * ...
2
votes
1answer
109 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
58 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 ...
2
votes
1answer
87 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
0answers
106 views
Create Oracle ASM Disks Without Installing Oracle
I have been asked to setup workstations (VMs) for an instructor's Oracle 11g R2 Workshop. I am finding the documentation that was downloaded from the Oracle Academy site extremely underwhelming. I am ...
3
votes
1answer
114 views
Why is truncate DDL?
I have an interview question, which being asked during my interview. I answered the question, but interviewer was not convinced with my answer. So, please let me know the what would be the correct ...
3
votes
1answer
85 views
Where's my Network Alias list in Oracle?
I can't select any Network Alias from the Network Alias Drop-down.
My TNSNames.ora file is in C:\Oracle
My other machine that is a fresh install doesn't have this problem.
I get an error when I ...
1
vote
1answer
342 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 ...
0
votes
2answers
56 views
oracle null value in quotes - yet another newbie question
I have a where clause like
"and uc.completion_status in ('[P....]') "
here ('[P....]') is the user input from taken a dropdown list in the user interface. and dropdown list consists three options ...
3
votes
1answer
379 views
How to find the schema name of a data pump dmp file?
I have been given a DMP data pump export file to import into my local Oracle instance. I've tried running this command line:
impdp full=Y directory=DATA_PUMP_DIR dumpfile=MyDumpFile.dmp ...
2
votes
1answer
342 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 ...
1
vote
1answer
91 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 ...
0
votes
0answers
57 views
Are there any good open source tools for DB objects end user manipulation?
I've been recently tasked to provide some object adjustment features for our end users, simple thing like changing the value of two or three fields in some specific business know tables without the ...
3
votes
3answers
72 views
Sum Up values in a query based on other information
I am trying to grab the sum of 2 columns if 1 column is the same. I currently have a record-set that looks like this:
I get these results by running this statement:
select distinct a.eventnum, ...
