The second and terminal release of Oracle Corporations Oracle 11g database product. 11.2 was released for Linux on September 1st, 2009. New features added include RAC One Node, Improved data compression ratios (up to 20x), Edition Based Redefinition, Recursive Subquery Factoring, Instance Caging, ...
0
votes
0answers
32 views
Why can't OUI detect system packages when they are already installed?
I am installing Oracle 11g 32bit on Ubuntu Linux and the OUI shows following error message on Prerequisite Checks screen -
This is a prerequisite condition to test whether the package "make-3.80" is ...
0
votes
0answers
88 views
Can't change Oracle sqlnet.ora runtime in the client program?
I am trying to setup Kerberos authentication for my Oracle database and I have a situation where I need to maintain different versions of sqlnet.ora for each kerberos user connection. My application ...
1
vote
0answers
185 views
Registry error while trying to install Oracle 11g on Win 2008 R2
I am new to Oracle and trying to install Oracle database 11g Enterprise Edition on Windows 2008 Server R2 but getting this error all the time...
It gives me error when updating registry key ...
5
votes
1answer
131 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
1answer
62 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:
...
3
votes
2answers
455 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
308 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
0answers
43 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
2answers
51 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
92 views
can I replace nulls in a PIVOT with zeroes?
I am using the PIVOT function in Oracle and am curious if I can replace the null values with zeroes? I know I can wrap the entire query in another SELECT and then use COALESCE on the values, but I am ...
2
votes
1answer
102 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 ...
5
votes
2answers
168 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 ...
4
votes
1answer
132 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
74 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
131 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
661 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 ...
1
vote
1answer
358 views
Alternative tools to export Oracle database to SQL Server?
I've got an Oracle database that I need to export (schema and data) to SQL Server.
I am trying the Microsoft SQL Server Migration Assistant for Oracle, but it is horribly slow, grossly inefficient ...
1
vote
1answer
163 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 ...
1
vote
1answer
96 views
need to use PLSQL to insert distinct rows
I need to take some data from Table A, use some logic, and then insert one or more rows into Table B. I have a PLSQL block that brings in data from Table A with a cursor, performs all the logic ...
2
votes
1answer
280 views
how to create and insert into a table in an anonymous block
I have PL/SQL anonymous block that creates a table (using execute immediate) and then inserts data into the table.
When the block compiles, it gives me an ORA-00942: table or view does not exist ...
2
votes
1answer
73 views
Blocking incoming database links
Let's say there is an Oracle database A and another Oracle database B. Our database is A.
Is there a way to block database links from B to A?
2
votes
2answers
1k views
“ORA-01950: no privileges on tablespace 'SYSTEM' ” : What error is it?
Earlier I created a user :
SQL>create user suhail identified by password
SQL>User created.
SQL>Grant create session,create table to suhail;
SQL>Grant Succeeded.
but when I login as ...
1
vote
0answers
247 views
Oracle 11g R2 - same query sometimes fast, sometimes slow
I have a simple update query on a table with about 100 rows in it:
UPDATE RESULTS_DATA
SET IMAGE_BLOB=:myBlob
WHERE MEASUREMENT_INDEX=:mIndex
AND BATCH_ID=:batchId
AND SYSTEM_ID=:sysId
...
0
votes
2answers
619 views
“ORA-01017: invalid username/password” : Why is that?
What is the difference between the two :
SQLPLUS / AS SYSDBA
and
SQLPLUS
Enter user-name: SYSDBA
Enter password:
If I use the first command I get connected and see the prompt SQL> but upon ...
1
vote
1answer
56 views
Update field1.table1 based on compound join between table1 and table2 - Oracle
I want to run update statement on Oracle 11g, but it fails with the following error:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery ...
0
votes
1answer
81 views
Logging and saving user activity
Database:Oracle 11gR2
OS: Windows 2008 Server R2
I'm not trained DBA, just have to run things on an Oracle database temporarily, which is used to perform OLAP processes.
I need to automatize user ...
0
votes
1answer
322 views
ORA-00990 error while trying to grant priviliges: Why is that? [closed]
After creating a user named suhail I am trying to grant that user some privileges. But I am getting an error saying missing or invalid privilege . Why is that ?
C:\Windows\system32>SQLPLUS / AS ...
1
vote
1answer
88 views
Unable to start Oracle Database Server : Get an error as I try to start it
I just installed Oracle Database Express Edition 11g Release 2 for windows.It created a short cut icon on the desktop :
but as I click this icon I see this dialog box :
What is it ? How do I ...
5
votes
1answer
387 views
Global locking for multi-master Oracle GoldenGate replication
This is a very complex scenario, but I figured a state-of-the-art challenge might interest some of the many high-end users of dba.se.
Problem
I'm working on an intercontinental data replication ...
0
votes
0answers
315 views
ORA-06512: at “SYS.DBMS_ADVISOR”, line 201 PL/SQL: numeric
We have been using Oracle Database - Enterprise Edition 11.2.0.3 running on the IBM AIX on POWER Systems (64-bit). My question / problem is : When trying to run below command then I'm getting the ...
0
votes
0answers
82 views
can I get a list of all parameters and their values while inside my stored procedure?
I have a number of Oracle stored procedures whose activity I want to be able to audit, so I am having them INSERT some data into an auditing table. It would be swell if I could list the variables and ...
1
vote
1answer
69 views
Strange behaviour of a query with order by and rownum on Oracle's Exadata server
I am executing the following query on two different database servers:
SELECT start_date,
end_date,
resort_id
FROM employee_activity
WHERE employee_id = 27
AND start_date < ...
3
votes
1answer
97 views
best way to create multiple similar tables - CTAS vs INSERT
I am creating a few dozen tables as CREATE TABLE AS SELECT (CTAS). The only difference in the SELECT statements is specifying a different value in the WHERE clause for TEAM_ID. I'm an Oracle newbie ...
0
votes
0answers
36 views
minimum software required to connect to an Oracle server on a lan
I'm an OK dba with MySQL, passable with SQL Server, awful with Oracle.
I have a Windows Server 2008 R2 running Oracle 11g. I'd like to connect to this server with SQL Developer running on Windows XP ...
7
votes
1answer
137 views
Generate XML using Oracle XML schema
I have some XML schema definitions that have been registered using DBMS_XMLSCHEMA. I have created tables using the XMLTypes generated. I can create XML using XMLQuery and if I understand correctly, ...
1
vote
1answer
205 views
Should a table be partitioned before or after an INSERT?
I have a fairly simple question that is proving hard to search for.
I need to create a table that will be partitioned and into which (lots of) data will be loaded.
Is it more efficient to create the ...
0
votes
1answer
124 views
How do I debug an ETL process that is unable to commit any records?
I used a DB link to connect to another DB and delete a couple of records. Later I ran an Informatica Workflow (ETL tool) to load data into that database using a DB connection that connects directly to ...
7
votes
2answers
285 views
are there significant differences with 500+ million row tables in Oracle?
I am in a database designer in a data warehouse environment. I am used to dealing with tables with a maximum of 1 millions rows and am now faced with tables with more than half a billion rows. Are ...
2
votes
1answer
75 views
Oracle Table Design, Storing Small Values
In Oracle if you want to store a small value, such as a one digit code (e.g. 0, 1, 2, 3 4, or Null; or Null, or 1; or 1, or 2), what is the space optimal way to store such a value. Currently I'm ...
1
vote
0answers
263 views
Oracle 11g release 2 32 bit client Installation
I have installed a 32 bit Oracle 11g Release 2 (11.2.0.3) client on my Solaris 64 bit machine. But I am getting:
Generic Status: Library Open failed, Library Name: libclntsh.so, Platform Status: ...
2
votes
2answers
833 views
Add additional disks to FRA diskgroup in ASM
We're running Oracle 11.2.0.3 with a two node RAC and our FRA diskgroup is running low on space. I need to allocate some more disks to it through ASM.
I'm not sure how to go about doing this.
I can ...
1
vote
2answers
108 views
Count events on “user based” sliding window
So, if I wanted to know how many events a certain user bought in certain timeframe windows. I will use 7 for the example, but the query should accept this number of days as a parameter, which will be ...
2
votes
1answer
203 views
Can you have multiple Oracle ACL files?
I have an application which needs to send emails through Oracle triggers. I understand that in Oracle 11g you must have an ACL file to grant certain schemas access to the network. My plan was to ...
0
votes
0answers
77 views
XML errors while copying dependents during a redefinition to an interval partitioning scheme
I am partitioning an existing table. I used the following process:
Checks
DBMS_REDEFINITION.CAN_REDEF_TABLE(...);
Successful
Create duplicate table
CREATE TABLE "USER"."MYTABLE_ONLINE"
( ...
1
vote
1answer
286 views
How to list all users that have grants over a package
I'm looking for some query to list all users that have grants over a package.
For example, user Schema_A has been granted to execute package: B.MyPackage.
I'm querying views or tables like: ...
6
votes
1answer
232 views
Package state not re-intialized on raise_application_error
When a package has state and the header is changed, the first call gets an error stack something like this:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of ...
0
votes
1answer
248 views
How to connect to a Database made by Oracle SQL Database?
So I am fairly new at this, so if you could keep that in mind in your answers, it would be much appreciated. I installed Oracle SQL Database on my Windows PC. It came in two zip files. I installed it ...
1
vote
2answers
224 views
Factoring out subselect (with clause) ends in ORA-00904
I am trying fo factor a repetitive subquery with with but getting ORA-00904 over and over again.
This is my base query which works quite well:
select tka.id, tka.name, txt.text as langname, ...
2
votes
0answers
332 views
Why ORA-00932: Inconsistent Types error?
Sorry for the meaningless title, but I couldn't think up a better one. I'm using Oracle 11g r2.
The following query raise ORA-00932 error:
SELECT BBB.FIELD1 FROM TABLE0 AAA
JOIN (SELECT * FROM ...
1
vote
0answers
128 views
What is the nature of “LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2”?
I'm not sure when this started, but for the time being the primary database instances in a physical standby configuration logs this message on every redo log switch.
...
