Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.

learn more… | top users | synonyms

1
vote
1answer
96 views

Can we reuse a .dmp file(expdp) to perform import using data pump utility

I'm an accidental DBA. We use Oracle 11g database. I had to do a database refresh in the test environment. I created a .dmp file using expdp command and performed an import using impdp command a week ...
-2
votes
1answer
297 views

Moving from Oracle 11.2.0.1 to 11.2.0.3

We are going to upgrade our database soon from Oracle 11.2.0.1 to 11.2.0.3 We have our customer relation application that runs on the Oracle background. Both Oracle and the company that designed the ...
1
vote
1answer
60 views

Union of data from all tables with name containing a substring

I have 500+ tables all containing a column called COLUMNDATA. The table names are like: TABLE_FIRST, TABLE_SECOND, ... ,TABLE_FIVE_HUNDREDTH. What I am trying to get is a column containing all the ...
2
votes
2answers
5k views

Oracle.exe (SHAD) process keeps increasing

I have an Oracle Server 11g that we use for integration tests. For some reason, the processes count [select count(*) from v$process;] keeps increasing up to the limit, causing ORA-12514: TNS:listener ...
2
votes
2answers
526 views

Oracle connection suddenly refused on windows 8

I am having all kinds of connection issues with sql developer all of a sudden. It was working till yesterday and stopped working on windows 8 machine. i am still able to connect via command prompt. ...
0
votes
1answer
151 views

Duration of adding versus dropping a column

I have a table containing more than 50 million rows. When I add a column: ALTER TABLE my_table ADD my_column NUMBER; It's very fast. Just after adding the column, I drop it. ALTER TABLE my_table ...
1
vote
1answer
25 views

Matching a column from a table to another table

I have 2 tables in a Oracle database and if I do: SELECT ID,ID_EXTERN_FOR_JOB FROM SOL_PUBLIC.SOL_SUBSOL_CHESTIONARE@RACDB WHERE ID=1207; I get: ID ID_EXTERN_FOR_JOB 1207 -1 On the other ...
1
vote
1answer
126 views

Oracle: Recover source code/table structures from “utterly wiped out” development database

Given an Oracle 11.1 database used for development, running in NOARCHIVELOG mode, where all datafiles (including undo) have been wiped out; and only SYSTEM and SYSAUX datafiles and control file remain ...
0
votes
1answer
407 views

ORA-01461 in a msaccess linked table

In an Oracle 11 database I have a table that all fields are varchar2. This table is linked in an access database, and when I try to do an insert from a data aggregate query, it throws the error ‘can ...
3
votes
2answers
17k views

Adding subpartitions to existing partitioned table

In Oracle 11g, is there a way to introduce subpartitions into an existing partitioned table? I cannot seem to find a combination of EXCHANGE and SPLIT partition that does the right thing. SPLIT will ...
0
votes
0answers
683 views

Use of temporary table variable inside a stored procedure

I am new to oracle, I was trying to create a procedure which consist of temporary table inside. I have tried this code below and getting an error in the declaration of temporary variable. DECLARE @...
1
vote
2answers
152 views

what is the most simple and easiest way to backup and restore on oracle 11g?

Im new on oracle 11g i come from SQL Server administration and the process to back up and restore its very easy and intuitive. I was searching how to make backup and restore on oracle 11g in many ...
0
votes
0answers
33 views

I want to pivot a table and aggregate a column into strings [duplicate]

I have a select statement producing the following: +---------+---------+------+ | Patient | Pattern | Rank | +---------+---------+------+ | a | 0 | 1 | | a | 1 | 2 | | a ...
4
votes
2answers
4k views

Hibernate Query Running Slow

I am currently facing a problem with a slow running Hibernate query that is being kicked off from a Java program. It is making a call to Oracle 11g at the back end. This query is taking anything from ...
1
vote
2answers
71 views

How to check if restored database is the same as original

Is there a way to check if restored backup of Oracle database is the same as the original database? Is there a simple way to check it if I have the original database?
0
votes
1answer
160 views

Select * from (every table in an subselect)

I was trying to get an select * from (for column table result) every table_name with the result bellow, how can i achieve this? (SELECT a.table_name FROM all_cons_columns a JOIN all_constraints c ...
1
vote
1answer
676 views

cluster_database TRUE/FALSE what does it mean in Oracle RAC?

I have 3 databases on an Oracle RAC (two nodes) one database which is running with CLUSTER_DATABASE=TRUE on both nodes, the other two runs on each node with CLUSTER_DATABASE=FALSE what does it mean ...
14
votes
4answers
44k views

How can I unload Oracle database tables into a csv files?

I need to retrieve certain data from an Oracle 11g release 1 database and provide it as one or more CSV files. The data resides in multiple tables and/or views. All this should work via the command ...
0
votes
1answer
609 views

Oracle Package in Data Dictionary that can't be dropped

I've encountered a very interesting problem that I can't figure out how to fix. I have a package that I wanted to create on my database with a wrapped body. I mistakenly omitted the word "BODY" from ...
0
votes
2answers
73 views

Oracle query optimization

We have a query that runs on a view and its execution time is about 6 min. SELECT COUNT(*) FROM someschema.item_total_violations WHERE channel_id = 122507833 AND item_date >= TIMESTAMP'2015-02-...
0
votes
2answers
476 views

Oracle DB license for only non-production environments [closed]

My company is a software vendor. We develop software that is later deployed on the customer's environment, on their own machines. Our software uses various DBMS, depending on what the customer has. We ...
0
votes
2answers
238 views

Why is Oracle's number datatype so HUGE? and why do I seem to have no alternatives?

Oracle has a favorite numerical class called NUMBER. It is a 22 byte value that just seems ludicrous in an index. I have some values that would easily fit in a 6 byte integer and would therefore fit ...
1
vote
0answers
346 views

Oracle listener configuration

I have oracle 11.2.0 installed on linux. And I have arcgis server 10.2 on the same machine. I created an geodatabase. But when i try the sde functions like sde.st_asText i got: -ORA-28575: unable to ...
0
votes
1answer
345 views

Oracle: Create a hidden user for RMAN backups only

It's been a long time since I worked with Oracle and I can't remember whether the following is possible: I'm trying to create a hidden DBA user [ alternatively do this process as root user ] to ...
0
votes
0answers
32 views

SQL Server to Oracle migration

Converted model resulted into incomplete script generation as all stored procedures, triggers and views were missing, only table structures were generated and i did not get any error messages for it. ...
0
votes
1answer
56 views

How do i prevent executing os command in oracle 11g

I have Oracle 11g installed on a linux serveur and i'm looking for a way to prevent sysdba from executing system command through oracle(Using java for example).
0
votes
1answer
50 views

Oracle grant select on a table

I want to grant select permission on a specific table to a remote ip address I had done it before in Mysql with this: grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd'; ...
2
votes
1answer
155 views

With Clause vs View of the same name

In my database, there is a view named VAL_DATES_V which is used in many other views in the same schema. For example, the SQL code in the view SECTOR_V uses VAL_DATES_V. CREATE OR REPLACE VIEW ...
2
votes
1answer
7k views

Net Start OracleXETNSListner

I am trying to run Oracle Express Edition on Windows 7 and getting the following error. C:\oraclexe\app\oracle\product\1O.2.O\server\BIN>net start OracleXETNSListener System error 5 has ...
0
votes
0answers
286 views

Portable Method of Deploying Oracle Express on Windows (Ideally without Installer)

I'm looking for a portable way of deploying Oracle Express alongside a C# application on a continuous integration machine running CruiseControl. Ideally I would not have to use the installer - this ...
0
votes
1answer
163 views

What is contained in the controlfile of oracle database?

As we all know that controlfile contains datafile locations and redo log locations along with their names.It also contains dbname,timestamp,checkpoint info,log sequence number,rman backup info.In ...
1
vote
1answer
230 views

What to do when archive logs are lost/corrupted that are not backed up

All recently generated archive logs were corrupted but datafiles are fine.What if recently generated archives corrupted and the archives backup is not available, so what would be the strategy to ...
1
vote
1answer
47 views

Is there a way to get disc space remaining using an SQL query on an Oracle database?

We experienced a problem recently where the disc space ran low and some queries started giving unpredictable results. The root cause was that we'd take too many restore points and filled up the disc. ...
1
vote
1answer
119 views

How do I include zeros in a count(column)?

I am attempting to build a report for a client. I need a display of all the values in Tab1, Col1 (a validation table), a count of each time that occurred, and a percentage of the total. Here's what I'...
0
votes
1answer
70 views

Partitioning on a computed column

Using - Oracle 11g I have created a table say X. of this table, a column say C3 is a computed column which will have a value based on incoming values for the other 2 columns say C1 and C2. I am using ...
0
votes
1answer
44 views

Use Oracle on Web App (New Question)

Our database project is about creating a web application where the user can add or edit information. My concern is How do I connect Oracle 11g with my PHP? Note: Our teacher did not teach us about ...
1
vote
2answers
20k views

How to see profile assigned to a user

Let's say i have created a user as 'TEST_USER', and then i assigned a profile to it. Now how can i see for TEST_USER which profile has been assigned? I want some query that will check what profile a ...
0
votes
2answers
1k views

Changes in Oracle Database implicit conversion rules for select statement

Due to hardware failure after a power outage we had to recreate the database for one of our 3rd party applications from an dumpfile (created via exp). The old hardware had Oracle 10G R2 running while ...
0
votes
1answer
728 views

Update does not working while select returns right results

I am trying to execute the query below: UPDATE( SELECT Ord.Completed FROM Product P INNER JOIN Order1 Ord ON Ord.OrderID = P.OrderID WHERE EXISTS( SELECT * FROM Delivery ...
1
vote
1answer
64 views

What is {Oracle}… {Oracle Off}?

I inherited an old script, that should, as part of a pl/sql routine, create a temporary index for me. I tried it on Oracle 11.2.0.4.0, but it does not accept the options. CREATE INDEX x ON y(gid) {IN ...
4
votes
2answers
20k views

How to execute this procedure in PL/SQL?

I have this table in the below format: Persnbr | Userfieldcd | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank 02 | Vip1 | Authority This is how I want ...
0
votes
1answer
189 views

Problem creating Oracle database manually

I am using Windows 7 and Oracle 11. When I want to create a database manually, it is giving me two problems: I set my oracle_home to c:\app\best\product\11.2.0\dbhome_1. Under home I have a ...
-2
votes
2answers
35 views

creating tablespace failed!

I've restored a db and its failed with many errors. When I look into the log file, I found that the tablespace does not exist. So when I try to create a tablespace in plsql am getting the following ...
0
votes
1answer
84 views

Update table from different table [closed]

UPDATE RBI.RBI_OV_F_DETAIL A SET (BRANCH_ID, BRANCH_NUMBER) = ( SELECT PRICE_BRANCH_ID, PRICE_BRANCH FROM RSE.RSE_SO_F_ORDERS B WHERE A.SOURCE_ID = B....
17
votes
6answers
47k views

Best way to delete very large recordset in Oracle

I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end. The database doesn't really do anything (no SProcs, no ...
0
votes
0answers
56 views

How to store values from a loop and compare them?

I have to select child items from a parent item and then compare text of each child and if the text is same I should not be displaying the heading. I have written a procedure for this, which is like ...
7
votes
2answers
27k views

How and when to use sys_refcursor in oracle

Can some one give me a small explanation about how and when should someone use sys_refcursor ?
1
vote
1answer
62 views

Rollback in Oracle

I am having a problem in Oracle rollback. In my stored procedure I have multiple insert and update statements. If every thing is fine I am sending success message and also I am tracing success status ...
0
votes
1answer
228 views

Create a new table oracle

Hello I recently changed companies and the databases as well. Having worked with postgres so far, oracle is still fuzzy to me, so here I am stuck with creating a table. This is my SQL attempt : ...
-1
votes
1answer
8k views

Commit and rollback in Oracle stored procedure

In my Oracle stored procedure, I have multiple insert and update statements like below: create or replace PROCEDURE SPTest AS BEGIN insert into emptest(empid,empname,deptno) (1,'ravi',10); ...