All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

2
votes
1answer
27 views

CRON and RMAN Backups

We run RMAN backups via CRON jobs, but find the out-of-the-box emails with CRON to almost do what we want. I know we can channel the RMAN output to /dev/null so no email is sent, which for any ...
0
votes
1answer
963 views

How to select data out of an Oracle collection/array?

Due to needing some complex data manipulation and not being able to use PHP, I crafted a procedure (not stored, it's just run when we run the query) in Oracle to manipulate the data and store it in an ...
7
votes
4answers
21k views

Why use explicit cursors instead of regular loops?

I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data: for i in (select * from STUDENTS) ...
2
votes
1answer
2k views

sqlplus is not starting

I have a strange error on a Red Hat machine. InstantClient is installed in and ORACLE_HOME is set to /opt/oracle/product/112030_cl_64/cl/ The user which installed the client was oracle:oinstall. My ...
0
votes
1answer
177 views

RMAN catalog database

I'm about to create a RMAN catalog database for my setup, what are the general precautions or major pre-requisites as in DB parameters, compatibility,etc that I should take care while creating the ...
0
votes
1answer
109 views

Better ways than rownum limits on a union all for fallback-style queries?

I have three queries that generates some "reference" statistics and should be tried in order until one returns a result. These are currently coded as three separate calls to the DB, but I want to ...
1
vote
2answers
1k views

Specify NLS-Settings during Oracle installation under Windows

Is there an easy way to specify NLS parameters for the database during the installation? We want to install a new database (environment: Windows). For testing purpose we need NLS settings for ...
1
vote
1answer
29 views

ORACLE: Changing the grouping of a data to be based on month breakdowns

To keep this short and to the point, I am looking at modifying the grouping for this SQL query to go from NAME to Month/yy Here is a example of the script, and screenshots of the output file This is ...
1
vote
3answers
69 views

Get current time, on commit, with oracle database

When inserting a row I want to make a column, equal to the current time on commit. If I use default value sysdatetime on the column, it uses the current time at insert (which could be significantly ...
0
votes
2answers
279 views

Select all from table using procedure oracle

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the ...
-1
votes
0answers
13 views

oracle join from multiple tables [on hold]

I have to extract data(Table1.Name,Table1.ID,Table2.desc,Table4.desc) from four tables. ->Table1(Name,ID,Group_Id) ->Table2(Group_Id,desc) ->Table3(Group_Id,perm_id) ->Table4(perm_id,desc) In ...
0
votes
1answer
29 views

Syntax help in SQLPLUS

Does Oracle have any builtin SQL syntax help. I find I may have forgotten a few statements and sometimes have no Internet access. I am using Oracle 11g.
0
votes
1answer
25 views

How to remove datafile from oracle database which is already remove locally?

Created one dbf file from my database using following command, alter tablesapce PAULA_INDEX add datafile '/u02/oradata/hfd1/index/index17.dbf' size 2000M; This dbf file is created in my server ...
4
votes
1answer
372 views

How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

I have the following table: InStores +--------+---------+-----+ | ProdID | StoreID | qty | +--------+---------+-----+ | p1 | s1 | 25 | | p1 | s2 | 70 | +--------+---------+-----+ ...
0
votes
1answer
120 views

How to connect to Oracle database on VM1 from host with 11gClient

I have a VM running Oracle Linux 6.5, I loaded Oracle server x64bit and created a database. The database functions fine and the listener is up and running for the database. I want to connect to the ...
0
votes
1answer
477 views

Oracle DB Point In Time Recovery faulted

I'm testing a procedure via RMAN backup to retore/recover at recover-point, but it fails. Exactly I've executed the first time and it works: Backup: RMAN> backup database INCLUDE CURRENT ...
0
votes
1answer
788 views

Running Oracle Export Utility within a Shell script

I have wasted a lot of time on this. Any ideas of what I am doing wrong here? Script in bash #!/usr/bin/bash . . . /export/home/app/oracle/product/10.2.0/bin/exp $uname/$passw \ ...
0
votes
1answer
158 views

missing files, installing Oracle DB 12c on Oracle Linux 7.2

when I try to Install the Database I get this error message: Source location is incomplete. Make sure you have downloaded and extracted all the relevant archives. I did try this twice with the same ...
0
votes
1answer
38 views

How big is my oracle database?

I need to determine the total size of an oracle database. Ideas on improving the following query? SELECT 'DataFiles: ' AS TYPE, SUM (bytes / 1024 / 1024) AS MB FROM dba_data_files UNION SELECT ...
1
vote
1answer
123 views

Which parameters and metrics to look at when CDC based replication is causing high load?

I'm investigating the reasons why a Tungsten Replicator extractor is causing heavy load on an Oracle 11g database. The replication is set up to use synchronous CDC and there is no unusual load while ...
3
votes
3answers
997 views

Import Database from Oracle .DBF files

Recently my Oracle Installation has been crashed and I reinstalled the Oracle again. I have my old Oracle Data files (.DBF files) which have been recovered from old installation. Now, I need to ...
3
votes
2answers
1k views

FileWatcher Troubleshooting Options

I have setup a Credential/Procedure/Program/File Watcher/Job to monitor a folder for new files, but it is not working. I am trying to figure out what I can check to troubleshoot why this isn't ...
1
vote
1answer
29 views

why I can't set oracle sessions value = 35?

I want to simulate the error "ORA-00018.Maximum number of sessions exceed",so I want to limit sessions = 35 and I am going to open serverl sqlplus to simulate this error,but sessions number didnt ...
0
votes
1answer
28 views

Oracle date not returning the right data

I don't know whats going on here, but here is my table (budget) with select * from budget : |budget_id|as_of_date|quarter_1|quarter_2|quarter_3|quarter_4| |1 |15-MAY-16 |400 |100 ...
7
votes
2answers
26k views

How to free up disk space? which logs/directories to clean?

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 ...
0
votes
1answer
28 views

Configure Oracle EM to Send Emails

We are using Oracle 11gr2 installed on Linux. Tried to use Enterprise Manager to send email alerts in case of tablespace is near to full. Alerts are generated on EM but no emails are received when ...
0
votes
1answer
42 views

How to do data migration between different schemas?

My company is in the process of migrating a customer's data from their third party system into our own system. We currently use a java application that we have developed in house that selects data ...
0
votes
2answers
142 views

Unable to start Oracle database

We have a oracle database, one data file(users table space) of the instance got corrupt and another data file(containing application data) was deleted due to some issue. The database is in ARCHIVELOG ...
0
votes
2answers
43 views

Oracle ASM : Alter asm_diskstring when ASM Instance is Down,

I am beginner for Oracle ASM I know we can alter asm_diskstring with following ways SqlPlus ASMCA but these requires +ASM to be UP and running. Is there way to set asm_diskstring when +ASM ...
1
vote
1answer
23 views

Ranking based off conditional field

I am looking to select only 1 row based off the IMMS_ID field. Which row I select is based off this if statement (excuse the syntax) if (status group based off IMMS_ID) status='DI' select the DI row ...
6
votes
1answer
122 views

ANALYZE TABLE..VALIDATE STRUCTURE runs forever

So, as I've mentioned before, I am a SQL Server DBA/Consultant who is helping out a customer with their Oracle databases. Mostly, I've been able to figure out what I need to and work through things ...
0
votes
3answers
553 views

Oracle RESTRICTED SESSION error

I tried to install Oracle database 12c on my machine following the documentation: ...
3
votes
1answer
829 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 ...
7
votes
5answers
17k views

Oracle returns an error ORA-01031: insufficient privileges

Oracle returns an error "ORA-01031: insufficient privileges" When I run sqlplus / as sysdba but sqlplus sys/oracle123 as sysdba doesn't, why? Oracle version: Oracle Database 10g Enterprise Edition ...
0
votes
1answer
205 views

ORA02063 error while connecting to remote databse

I have connected to remote database using DB link. From prod databases it works fine but from test env reporting error invalid username/password. Please help.
0
votes
2answers
46 views

INSERT OR REPLACE INTO + SELECT as source in Oracle

I have two tables A and B. I want to insert B's content into table A. The issue is there's a row in B that already exists in A. I've searched and I found some answers talking about MERGE, but no ...
1
vote
0answers
35 views

How to recover LogMiner after its tablespace had been deleted?

Here is my story: I created separate table space for Log Miner, create tablespace logminer_ts datafile '/path/to/logminer.dbf' size 50M AUTOEXTEND ON And moved Log Miner to the created table ...
0
votes
2answers
52 views

Is is posible to sample a database

Is it possible to sample any SQL database? By sampling I means the database table should remain and retaining the foreign key constraints. But the actual rows from the table should be reduced.If I ...
1
vote
1answer
30 views

Reset schema oracle

I'm running systemtest that updates a oracle schema. I need to reset the whole scheme between runs. Is there an easy way to do this in oracle 12. I have looked at flashback but it doesn't seem to be ...
2
votes
2answers
2k views

Repopulating Oracle tables using data from Excel/CSV file

I'm developing a Java EE application that utilizes Hibernate/JPA. Some of the data I want to use comes from another Business unit's spreadsheet that I need to periodically (re)load into 5 Oracle ...
0
votes
0answers
36 views

Connecting Oracle 11g to Postgres via dblink

I have administrator access to our Oracle database , but was wondering if its possible to connect an Oracle 11g database to Postgres database and if so, what steps would I need to follow. I've seen ...
0
votes
1answer
38 views

ORA-02049 Timeout: Distributed Transaction Waiting for Lock Solution needed

We are cleaning up the last year's transactions from our primary database. We use public database link to delete the records a day by day from another database. Each days records are around 30-40k, ...
0
votes
1answer
3k views

Oracle Duplicate Database with no archived logs

I am trying to perform a duplicate operation from one server to another (testing disaster recovery) on oracle 11g. I am very much still learning about Oracle. The system is not in production, it is ...
1
vote
1answer
716 views

Restoring Oracle prod backup to test server with new database name

I need to restore an Oracle production database (aaprod) to a test server that already is home to a test database (aatest) under a new name (bbtest). I have a cold backup of the production database ...
1
vote
2answers
95 views

Need advice on database normalization

I am working on a basic blood bank database on Oracle. I would like to create a completely basic normalized table and then I will add more details to it. I have done it to the 2NF and I will further ...
1
vote
1answer
447 views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw. but when use this extenstion i get error OCIEnvCreate failed to create environment handle after installing oracle_fdw. my ...
1
vote
0answers
37 views

How to get email notification when dbms_scheduler jobs are created under user_schema rather then sys?

i login as sys and set the smtp for scheduler BEGIN DBMS_SCHEDULER.set_scheduler_attribute('email_server', '10.1.1.15'); DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'user@compny.com'); ...
1
vote
2answers
9k views

Create Oracle DB LINK to a database with no DB_DOMAIN

I'm trying to create a database link between this two DBs. My local DB is 11.2.0.3. SQL> select * from global_name; GLOBAL_NAME ...
0
votes
0answers
22 views

jdbc Datasource error BEA-001131 Closed Connection

I have Installed weblogic 10.3.5. On database side I have RAC environment with Two instance. I have created GRIDLINK DataSource with XA DRIVERS. NAME: test JNDI: jdbc/testDS NOTE: I ...
2
votes
5answers
44k views

Oracle Export using EXPDP gives ORA-06512 SYS.UTL_FILE error on Windows 7

I am using Oracle 11g and want to export a table using below statement C:\>expdp SYS/sys@MYDATABASE DIRECTORY = Mydir DUMPFILE = Customer.dmp LOGFILE = Customer.log TABLES = PERSON.CUSTOMER ...