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

2
votes
1answer
3k 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, ...
2
votes
3answers
3k 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 ...
2
votes
1answer
401 views

Filter on a window function without writing an outer SELECT statement

Since window functions cannot be included in the WHERE clause of the inner SELECT, is there another method that could be used to write this query without the outer SELECT statement? I'm using Oracle. ...
0
votes
2answers
4k views

How to forcibly flush the undo log/tablespace of UNDOTBS1

I'm looking for a way to flush the undo logs so that the UNDOTBS1 doesn't give me an error saying that my tablespace isn't large enough. I tried using a commit, but without success, it still gives me ...
1
vote
2answers
429 views

Jobs view is not visible to read-only account users

I am the admin of one of the database, and I have created a view in SYS schema using below code which returns the status of the running jobs: CREATE OR REPLACE VIEW CAF_DB_REFRESH AS SELECT * FROM ...
3
votes
2answers
10k 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 ...
-4
votes
1answer
98 views

Oracle database and data has been corrupted. Is there a way to get it back? [closed]

The Oracle database has been corrupted and both the data and backups were lost in the corruption. Is there anything I can do to recover my data? It would be terrible if it were all lost.
1
vote
2answers
1k views

Need an Oracle query to get the Oracle Home directory for a particular instance

I am searching for a query to get the Oracle Home for a particular instance name, say Eagle (if there are more than one instance on the host).
0
votes
0answers
2k 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
1answer
83 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 ...
3
votes
1answer
1k views

Is it possible to use 'restore UNTIL SCN' with rman and Oracle 9i in NOCATALOG mode?

I'm trying to do a hot backup with rman that I can copy to a USB drive so I can recover it on a new server. The backup was done two days ago and the restore database command was working on the same ...
2
votes
1answer
85 views

can rman backup a subset of a table

I have been asked to make incremental backups of a table based on the value of one of the fields in the table (called groupid). They want a separate incremental backup for groupid=1, groupid=2, ...
1
vote
1answer
229 views

Finding oracle physical database files

I'm using oracle 11g and I need to find the database files to copied to another server. The database was created from migrating from SQL Server 2008 to 11g. There doesn't seem to be a counterpart for ...
-2
votes
1answer
2k views

what's the fastest method to export a table through sql query ? 11g - performance

I'm connected to the oracle server remotely. I need to export a table data(the whole table) with millions of records but due the hosting restrictions I can only use SQL (no exp , expdp) . Therefore ...
-1
votes
1answer
610 views

Adding a column at a specific position using DBMS_REDEFINITION

Can anybody tell me how I can add a column to table at a specific position using the DBMS_REDEFINITION package from Oracle?
1
vote
1answer
137 views

Appending data from one program to the other using batch commands

I have a database which sometimes crashes. I want to create a batch program that does this: Runs SQL*Plus. Makes SQL*Plus run a script from a plain text file. Gets the results back to the main batch ...
-4
votes
0answers
46 views

oracle database query [closed]

I have a problem in writing a Database Query Problem: I have the below fields in my table Fields: Primary_ID(PK) ,E_ID, Bank, REQUEST_STATUS, START_DATE, STATUS Data: REQUEST_STATUS may contains ...
1
vote
1answer
684 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 ...
2
votes
1answer
3k 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 ...
6
votes
1answer
393 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 ...
3
votes
2answers
885 views

Understanding oracle SCN

I am currently working as junior Oracle DBA in a company.Now my team leads always tell me that I have to understand how SCN works and what I can do with the knowledge about SCN.So,are they really ...
1
vote
1answer
300 views

Oracle DB 11gR2 netca installation failing

I am getting below error when I run netca from command line cmd: /u01/app/oracle/product/11.2.0/db_1/bin/netca /silent /responsefile=/oracle_binaries/database/response/netca.rsp [main] [ 2013-05-17 ...
0
votes
2answers
759 views

copy package from schema B to schema C

I am in the next situation: I am using oracle 11g. I am connected to an user, lets say schema1, where are a lot of permissions. I want to create a script which will copy the Package1 (and its body) ...
3
votes
1answer
1k views

How do DELETE / TRUNCATE affect table / tablespace?

I am piggybacking off of this question, but asking it from the Oracle perspective. Specifically, how does a DELETE/TRUNCATE affect the size of the table and/or tablespace?
1
vote
0answers
229 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
2answers
7k views

Use an Alias in Where Clause Subquery

I need to show some fields from another table in Oracle. Here is my query: SELECT ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE, ANGGARAN.SIMPEG_PEGAWAI.NAMA, ANGGARAN.SIMPEG_PEGAWAI.NIP, ...
7
votes
3answers
133 views

Tablespace on ephemeral storage

For performance reasons in some scenarios, e.g. Amazon EC2, you have access to a faster and cheaper storage device, which loses all its data on reboots, so it is called "ephemeral". This question is ...
1
vote
0answers
2k views

PL/SQL extremely slow FETCH performance

I have a stored procedure that includes (among many other things) a dynamic query with a LOOP and a FETCH. The fetch has to potential retrieve a large amount of data as one column contains spatial ...
0
votes
1answer
517 views

What's the most effective way to back-up / save data on Oracle 11g large data sets?

We have a database(actually more databases split by primary access path, modulo on a key.) with millions of records (about 400 millions ). Right now I need to save a specific table userData(which has ...
1
vote
3answers
4k views

Should I use two databases or two schemas?

We are currently having one Oracle instance on RAC with one database created. A new requirement came to our database team to temporarily (1-2 months) host a new application that is independent of ...
0
votes
1answer
573 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
1answer
332 views

How to load .dbf to run select queries?

I had asked for database(Oracle 11g) back-up files (for a particular date)from a third party vendor. The files I received were with the following extensions : .dbf , .svd , .arc , .ctl (PFA ...
4
votes
1answer
438 views

Oracle UPDATE followed by SELECT in LinqPad

I'm trying to UPDATE values in an Oracle database table (of salary information) that has no row IDs or a primary key. I'm writing these statements in LinqPad. This syntax should work just fine in ...
1
vote
3answers
3k views

Oracle TNS names configuration

I've edited added new TNS name to tnsnames.ora. Toad for Oracle started to work at once. SQL Plus doesn't work even after logoff/logon, the same problem with my build scripts. Getting the error: ...
1
vote
1answer
107 views

Accessing the insert statement which triggers a trigger inside the trigger's body

I have some table and I have enabled auditing on this table. Currently, I am collecting the data about who has inserted records and at what time. Now I also want to log what was the exact query he/she ...
1
vote
0answers
479 views

What are the host credentials for Enterprise Manager 11g?

I am trying to use Database Replay in 11g Release 1 using Enterprise Manager. In the last step it asks for 'Host Credentials' to start the capture. But when I give the username and password for the ...
0
votes
0answers
473 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 ...
1
vote
1answer
7k views

Passing parameters to a procedure executed by DBMS_SCHEDULER

I have a scheduler program that runs a stored procedure that requires input parameters. BEGIN DBMS_SCHEDULER.DROP_PROGRAM (program_name => 'MYSCHEMA.EXPORT_SCHEMA_STARTING'); END; ...
0
votes
1answer
2k views

Error in trigger: table is mutating

CREATE OR REPLACE TRIGGER "TRANSACTION" before update of totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD for each row begin declare mportfolioname varchar2(20); ...
3
votes
3answers
3k views

Most efficient and practical way to store time values (HH:MM) in Oracle that makes it easily searchable

I have a set of starting and ending times that I need to store in an Oracle database. I need to store them in a manner that makes them easily searchable (i.e. a typical search is find all rows where ...
0
votes
1answer
76 views

How to use database users as a foreign key field in any table

I want to create a table where I can have a column username which will be the foreign key and it will only contain usernames of existing users on that database. For example, if I want to insert 'John' ...
0
votes
2answers
490 views

How find all the data loaded on previous day?

Issue: Data Loading into development environment from production database My production database is 40gb but I do not want all of those 40gb of data into my dev env and so my question is How can I ...
1
vote
1answer
215 views

Mistake during Oracle 11g PITR [closed]

I tried using set time until.. and mis-typed the date. Can anyone help me understand how to get my backups into a manageable state? After the accidental recover, most of my backupset disappeared. I ...
4
votes
2answers
4k views

How to remove all hidden parameters from Oracle Database

How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended? SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ...
2
votes
1answer
158 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 ...
1
vote
1answer
1k views

Oracle RAC 11g R2 DB silent install: parameter needed for system class

I'm installing Oracle RAC DB 11gR2 using response file and silent mode. The installation is failing with the error message: [INS-35421] This options installs a single instance database only You ...
0
votes
3answers
185 views

Built-in XML functions

I am trying to use built-in XML functions, but I'm getting an %s: invalid identifier error. Selects works fine without the XML functions. Any ideas where is the mistake? select id,full_name from ...
3
votes
3answers
16k views

User Password Expiry date is null in user_users?

Is it possible to find my password expiry date without asking the DBA? My user does not have privileges to view the DBA_USERS table. Also, I tried select * from USER_USERS table, but expiry_date is ...
2
votes
1answer
608 views

How do I identify the remote db agent name to use in create_database_destination on Oracle 11gR2?

I am trying to setup DBMS_SCHEDULER in Oracle 11g to run a remote database job. I have a remote Oracle 11g R2 database on unix and a local one on Windows. I read that you can install the oracle ...
3
votes
2answers
502 views

General tool to load dump files [closed]

I am a big fan of Postgres both for its price but also for its features. I am going to have to need to upload into it both Oracle dump and SQL Server files. I will try to ask and beg for plain .csv ...