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, ...
1
vote
1answer
97 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
87 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
233 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
61 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?
1
vote
2answers
810 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
201 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
443 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
51 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
67 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
231 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
72 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
352 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
266 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
75 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
64 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
86 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
35 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
0answers
109 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
163 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
99 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
261 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
74 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
241 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
618 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
96 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
149 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
74 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
219 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
219 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
189 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
181 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
260 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
111 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.
...
4
votes
1answer
149 views
Is there a way to export an Oracle database to a CREATE DATABASE command?
I have an existing Oracle 11 instance, I want to export the database (which was created via DBCA) to an equivalent "CREATE DATABASE" command, handling the character set, etc. I'd also like to get a ...
1
vote
2answers
752 views
Oracle RAC with single node Data Guard physical standby
Best practices for Oracle 11gR2 Data Guard implies that the primary and standby databases should have the same layout, eg. RAC to RAC.
What special considerations would one need to take into account ...
1
vote
1answer
94 views
Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index
One of the largest tables in our system has its primary key setup as a globally ranged partitioned (in huge, useless, multi-billion value ranges) non-unique index. Its values are just a sequence ...
3
votes
2answers
80 views
Is there a bug with PL/Scope in combination with associative arrays?
I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something.
I have the following package
create or replace package tq84_pkg_c as
...
-1
votes
1answer
157 views
how to connect remotely to oracle database [closed]
I've installed oracle 11g express edition and sql developer. I don't know ho to connect to a database on remote server. Can anyone help please. Note that I didn't installed any other software like ...
0
votes
0answers
42 views
Strange symbols in Database
My web application is storing the currency as strange symbols. I have the same web application running in two different machines connecting to same database. One is working fine, accepts currency ...
2
votes
1answer
383 views
Changing a materialized view column's length after a change in the base table?
We changed the underlying table column size (length). So the materialized view which is built on that table throws an error now since the MV column still has the old size.
This is an Oracle 11gR2 ...
1
vote
0answers
73 views
Deleting folders after Oracle upgrade
I upgraded from Oracle 10g to Oracle 11g in Windows Server 2003.
New Oracle home : C:\app\oracle\product\11.2.0\db_1
Old Oracle 10g was installed on: C:\oracle\product\10.2.0\
Questions
Is the ...
0
votes
0answers
783 views
Oracle XE TNS Listener fails to start
I freshly installed Oracle XE 11.2.0.2.0 and found out that the TNS Listener service fails to start. At first I tried to start the Windows service by hand. After the failure this is the entry in the ...
2
votes
1answer
698 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?
5
votes
3answers
268 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 ...
5
votes
2answers
320 views
What would be the correct size of redo logs?
I installed and put in production a 11g database. I was adviced to size redo logs to 200MB each one. Now I see that my archived redo logs are each one 200 MB and all of them occupy a lot os disk ...
4
votes
1answer
339 views
How many control files should I have?
On the Oracle Database Appliance, the default deployment only gives you a single control file.
I find this a little puzzling. The single control file causes a policy violation in the automatically ...
1
vote
0answers
55 views
How to find an text in entire Oracle database?
I have a Oracle database (11g2). But i don't know how to find a text in all of table on a database. Because there are so many tables, can't find down by one column, one table... it takes me a lot of ...
1
vote
1answer
161 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 ...
3
votes
2answers
229 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 ...
-1
votes
1answer
80 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.
