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
1answer
159 views
Error AWR Operation failed: CATPROC not valid when creating database with dbca
I have a fresh installation of Oracle 11g enterprise edition on CentOS 6, both 64 bits. I have installed Oracle and so far everything looks fine. I have already added an started a listener. The ...
0
votes
1answer
56 views
How to keep an Oracle 11g instance lean?
As a Mac user I run a local Oracle Enterprise Linux (OEL) Developer Day VM that houses an Oracle 11g instance for development/unit testing. I frequently refresh the data in the schemas (sometimes ...
0
votes
1answer
55 views
Why Oracle SQL Developer does not need Oracle client?
I installed Oracle SQL Developer from Oracle's web page. It seems to work without a Oracle client installed on my computer. How is that possible? I also installed Toad for Oracle and it seems to ...
0
votes
1answer
36 views
Can we create a table in specific datafile - Oracle 11g R2?
I am buying a new server than has two HDs:
300 GB with 15000 rpm.
600 GB with 10000 rpm.
I am thinking to allocate the datafiles in both hard disks. and put the less used tables in the data files ...
2
votes
2answers
1k views
Setting NLS params in logon trigger don't work in oracle 11g
We have a trigger setup so that when a specific user logs in, their NLS session parameters are modified. This used to work on Oracle 10g. We've just migrated to Oracle 11g RAC, and the session ...
4
votes
2answers
521 views
Oracle 11g see transaction or session start time
I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records?
I've got a transaction id "TX-1234-abcd", a ...
3
votes
4answers
971 views
Is it safe to delete dummy tables Oracle 11g XE
i created a database schema in 11g express edition, however there are some APEX tables and some dummy data...
Is it safe to delete them? also i would like to know what does apex means and why they ...
0
votes
0answers
35 views
Is it recommended to run a Oracle schema backup when there are active sessions?
I need to backup two schemas in a Production database (Oracle11gR2) that are being accessed by an application and some other systems. These schemas are quite small and exp will be used for the export.
...
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 ...
0
votes
0answers
43 views
Replicating Excel's CHIDIST function in Oracle 11g
I'm attempting to replicate the functionality of an Excel workbook in Oracle. One of the formulae on the worksheet uses CHIDIST, which "Returns the one-tailed probability of the chi-squared ...
3
votes
1answer
100 views
How to combine `delete` and `insert` operations in one statement?
for the following data, I'd like to be able to delete some rows and insert others giving the result below. Is this possible with a single statement (eg with the merge statement)?
create table ...
1
vote
1answer
29 views
Rebuild an index while it is being referenced in Oracle
I have come across many articles on this site and on Tom Kyte's blog on the how, whys and whens of Re-building indexes. Note that this question is about whether I can rebuild when there are concurrent ...
2
votes
1answer
38 views
BLOBs storage in a dedicated table
I have a database that has several BLOB columns scattered across multiple tables. Since I am a developer and am looking at making application code writing easier, I wanted to turn these columns into ...
1
vote
1answer
925 views
How do I find out the “redo log file max size” in Oracle 11R2
I'm working with Oracle 11R2 and I want to see the max size of my redo log files (I didn't install Oracle so I'm not aware of the configuration).
I have searched the Internet but all I have found ...
1
vote
0answers
24 views
rman 'delete archivelog all’ very slow
On our automated test system we use flashback all the time to reset our Oracle 11g database to a state before a testrun. We noticed some of the scripts involved started to become slower and slower. In ...
3
votes
1answer
73 views
Index on foreign key makes query extremely slow
We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem.
The queried table (table3) has an indexed PK, three FK with indexes ...
1
vote
2answers
517 views
Run Unix command using PL/SQL
Is it possible to run a Unix command using a query in Oracle?
I want to run simple commands (like df -h) using a query.
Is this at all possible or am I wasting my time? I don't want to use a ...
3
votes
2answers
459 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 ...
0
votes
2answers
26 views
Why is the padding on months in Oracle 9 characters?
I ran two simple sql statements
select length(to_char(sysdate, 'fmMonth')) from dual;
select length(to_char(sysdate, 'Month')) from dual;
The length of the first statement was 4 (It's June) and 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 ...
1
vote
1answer
127 views
NLS_TIMESTAMP_FORMAT changing automatically and randomlly
I am using Oracle 11gR2. My nls_timestamp_format is changing randomly from dd-mon-rr to yyyy/mm/dd.
Does anyone have any clue why this is happening?
0
votes
1answer
25 views
USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view)
Does anyone know what triggers the USER_ID field in the log.xml to be populated? The value also exists in the V$DIAG_ALERT_EXT view.
I've found by observing the logs that if a temp tablespace fills ...
1
vote
1answer
89 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 ...
0
votes
1answer
62 views
Different dates Oracle 11g with TOAD
I have the following queries:
SELECT to_date(to_char(to_date('01-FEB-1949'))) FROM DUAL;
/*this returns 2/1/2049. */
SELECT to_date(to_char(to_date('01-FEB-1949'),'dd-MON-yyyy')) FROM DUAL;
...
1
vote
1answer
49 views
where is oracle session timezone set?
I have a logging table
CREATE TABLE EMAIL_LOGGING
(
ID NUMBER(9) NOT NULL,
SEND_DATE TIMESTAMP(6) DEFAULT LOCALTIMESTAMP NOT NULL,
...
4
votes
2answers
336 views
Return a fully dynamic table from an Oracle function
I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
3
votes
1answer
1k views
How to connect multiple users/schemas in Oracle 11g?
I'm almost new user to databases, so I think I'm doing something wrong.
I have the database created.
I created 3 users, 1 it's an Admin, 2 are just users.
I created 2 roles, 1 for the Admin and 2 ...
0
votes
0answers
88 views
Oracle 11gR2 DBCA installation failing with TNS Lost contact
I am getting below errors while running DBCA with silent install using response file
ORA-12547: TNS:lost contact
PRCR-1079 : Failed to start resource ora.ocl.db
It's a fresh installation and not ...
0
votes
0answers
36 views
Migrating form Oracle to Sybase fails due to missing functions
I generated the DDL from Oracle to Sybase, created the tables with no issues even migrated most tables into Sybase using insert into, however when came to the time functions I get errors:
Function ...
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, ...
-6
votes
0answers
30 views
How do I create a database+tablespace+user in Oracle 11gR2? 1 [closed]
I keep getting database not open error whenever I try committing anything with the user \ as sysdba how do I get started, create a table, create a tablespace and a user?
0
votes
1answer
39 views
Unable to connect to Amazon RDS instance
I recently created an oracle instance on Amazon RDS. Unfortunately, I'm not able to connect to the instance using Oracle SQL Developer.
The (relevant) information I have from Amazon;
Endpoint - The ...
0
votes
1answer
51 views
Efficient way to perform approximated search?
I have to perform a join between table_a and table_b, using three fields as key being one of them the date of the event, say, both tables have id1, id2 and evnt_time for eache record.
As it happens ...
0
votes
1answer
41 views
How to automate Oracle DB installation (like a 'hands-free' install)?
I installed the Oracle 11g2 'standard' edition using the following zip-files:
linux.x64_11gR2_database_1of2.zip
linux.x64_11gR2_database_2of2.zip
I used the installation guide by Oracle which I ...
0
votes
2answers
93 views
How to quickly startup/shutdown Oracle 11?
I am wondering what is the quickest way to properly startup/shutdown a Oracle DB daemon (Oracle 11.2, installed on a test machine).
I need it for C/C++ programs that use the OCI/Pro*C API.
Currently ...
0
votes
1answer
249 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 ...
3
votes
1answer
1k views
How to free up disk space? which logs/directories to clean? [ORACLE 11g]
I want to free up the disk space on my Linux machine. I've drill down the space usage and found that the following directories have a big size
/u01/app/11.2.0/grid/cv/log
...
1
vote
1answer
80 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 ...
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 ...
1
vote
2answers
6k views
How do you get the explain plan?
Can some one please explain a bit about explain plan, and how does one get the explain plan for a query. Also, if there is any sql script which can be used as utility for this, that would be great.
0
votes
1answer
56 views
Creating oracle database physical files from migrated DB
I migrated a database from SQL Server to Oracle 11g and there doesn't seem to be any physical files created after migration.
The client requires that they receive a copy of the database so they can ...
2
votes
1answer
60 views
What's the most appropriate NLS_SORT parameter to cover all/most European languages?
I'm developing an Oracle 11g-based application which is likely to be used by clients in many different countries (mostly European). I want the program to change its NLS_SORT so that it's case ...
5
votes
1answer
58 views
Sudden increase in log_file_sync waits
I'm on Oracle 11gR2 with a 2 node RAC system. It's shared fiber storage to an EMC Clariion.
Last friday things went bad..fast. All of the sudden processes that normally ran fine for years became ...
0
votes
1answer
142 views
Oracle 11g install on Debian Wheezy does not start
I successfully installed Oracle 11g R2 on my laptop for evaluation, by mixing various sources of documentation. I saw the Oracle daemons after installation.
But I have a few problems :
After a ...
1
vote
1answer
43 views
Data Migration from Oracle to SQL Server [duplicate]
If you need to do a data migration from an Oracle database to SQL server, what approaches and technical solutions are the best practice?
Our database has about 100 million rows and a total of 52 ...
1
vote
0answers
69 views
Performance Hit when upgrading to Oracle 11gr2
I am experiencing the same symptoms as noted in this question which was closed and does not have a satisfactory answer.
Original Question
We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to ...
0
votes
1answer
143 views
Granting privileges to dbms_crypto
I'm trying to grant proper privileges for using DBMS_CRYPTO.
Whenever I call it from package it gives me error:
PLS-00201: identifier 'DBMS_CRYPTO' must be declared
User already has privileges:
...
0
votes
1answer
27 views
Enable Oracle Database options in an existing database
I want to enable Oracle Real Application Testing option in my existing database, but I couldn't find the way to do that using Oracle Universal Installer (OUI) or Database Configuration Assistant ...
0
votes
0answers
58 views
How to clone a database from one environment to a database on another environment?
OS: RHEL
DB: Oracle 11g version 11.1.0.7.0
I am trying to clone a database on one environment (environment A) to another database on a separate environment (Environment B). Environment B's database ...
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 ...
