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

learn more… | top users | synonyms

0
votes
0answers
233 views

Troubles on export data with PL/SQL developer

I'm trying to export data from a specific table using PL/SQL developer and Oracle 11g. Here's how I did : select * from mytable right click on mytable then "Export data" chose my table on a grid ...
0
votes
1answer
60 views

Prompt user for input using substitution variable

DECLARE localvar char; BEGIN localvar := &ENUMBER; IF localvar ='A' then dbms_output.put_line('Please proceed to Level1'); ELSIF localvar='B' or localvar='C' then dbms_output.put_line('...
0
votes
1answer
207 views

Combining data of multiple rows in view

I have a table that holds timetracking data of users and looks somewhat like this: projectid activityid worklocationid year week day hours 1 22 21 2014 37 09/...
1
vote
1answer
58 views

Oracle11g - Convert alpha characters to numeric in select

I am only able to convert alpha characters to numeric by nesting REPLACE. Is there a better way to handle this? Desired conversion: a - 1 A - 1 b - 2 B - 2 c - 3 C - 3 ... z - 26 Z - 26 Example: ...
10
votes
1answer
60k views

Oracle 11g listener fails with ORA-12514 and ORA-12505 errors

I run an instance of Oracle 11g locally on my development machine and can connect to the local instance directly via SqlPlus: c:\>sqlplus ace SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar ...
4
votes
4answers
36k views

Convert a Unix timestamp to a DATETIME in a View

I have a table that stores a unix timestamp. To query this as a date, I'm attempting to convert this timestamp to a datetime type in a view. Unfortunately, I can only seem to get the DATE portion out. ...
0
votes
1answer
394 views

What privileges are needed to run EXPLAIN PLAN FOR CREATE INDEX?

My user can EXPLAIN PLAN FOR any query I like, but not for CREATE INDEX. I need to do that in order to estimate a suggested index's size, and using sysdba is out of question. What privileges should I ...
2
votes
1answer
113 views

Is it possible to copy mounted database without RMAN?

I need to make a copy of production database for development. For some reason I can't use RMAN. My question is: can I copy standby database while its in mounted state and recovery is canceled? Of ...
3
votes
3answers
4k views

cursor: pin S wait on X in 11g

This has been one of top5 wait events in our database, even though there were not many waits but we see it as a potential threat, we want to find the root cause and solution. Any advice will be ...
0
votes
0answers
102 views

Oracle database doesn't go to upgrade mode

I am trying to upgrade/migrate from 10g to 11g. When I start the database in upgrade mode with: startup upgrade select status from v$instance STATUS -------- MOUNTED It doesn't go to upgrade ...
1
vote
0answers
2k views

ORA-01157: cannot identify/lock data file 201

SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files; * ERROR at line 1: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-...
1
vote
1answer
190 views

Remote connection issue from windows machine

I have installed database 11g on machine A (Windows). Now I need to access that database from machine B (Windows), where my application is placed. I am able to ping B --> A but telnet is not working. ...
0
votes
1answer
370 views

Is there are a way to restore System schema to it's original state?

I've installed Oracle 11g Database Enterprise Edition and my task was to import schemas. I'm totally new so I ran command: imp system/password file=path.dmp full=y And import everything (10 dumps, ...
1
vote
0answers
705 views

How to drop invalid Java classes from Oracle?

I tried to load some jars into my database, but I think I messed up something, because I tried as DBA and then with some oracle user... This was my first command: call dbms_java.loadjava('-genmissing ...
0
votes
0answers
41 views

column values combination

I have chains of nodes like A -> B -> C -> F D -> E and store them in an Oracle table pair with 2 columns left_node and right_node left_node right_node A B B C ...
3
votes
3answers
6k views

What are the mandatory/required oracle 11g tablespaces?

Ok, what are the mandatory tablespaces in oracle 11g. My research has lead me to believe there are three of them: System, Sysaux, and Undo. However since I am taking the OCA and all the questions(...
2
votes
1answer
1k views

How to create an Oracle Database Link when the password has special characters

I'm trying to create a DBLink. SQL Developer is the client I'm using. The password to my remote user has *'s in it. I'm having trouble figuring this out. This syntax isn't working: CREATE DATABASE ...
0
votes
1answer
19 views

Do I need to create tablepspace and also add all datafiles before import schema using datapump

Do I need to create tablespace and also add all data files before import schema using data pump or impdp add all data files itself first and then unload data into it..?
1
vote
0answers
68 views

Oracle Application Server Integration With APEX

This might not be directly related to DBA, if so kindly advise. I need to integrated some reports created via Oracle Reports Builder with APEX. Reports are in RDF format. Which version of Oracle ...
0
votes
1answer
56 views

How to get a list of stored procedure along with their execution count in oracle 12c?

Hi I am new to Oracle 12c, though I was working on SQL Server for a long time. Is there a way to get a list of stored procedures with their execution count in oracle 12c. We can get it using DMV in ...
1
vote
0answers
404 views

Script to find unused indexes over a period of time doesn't work in Oracle 11g

The statspack_unused_indexes.sql script described in this page would be incredibly useful to me, but it has been coded for an earlier version of Oracle. When I try to run it on 11g, I get the ...
0
votes
0answers
38 views

Recover Oracle database with damaged system datafile

I'm working with Oracle version 11. My system01.dbf is corrupted and there is no backup. The database was in archive log mode and logs exists. Is there any way to recover data and import to a new ...
0
votes
1answer
51 views

Oracle DB: Can one db file be shared between table space

Can one db file be shared between table space? I am deleting temp table space as below, Get all the files in table space: SELECT FILE_NAME FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME = '...
0
votes
1answer
29 views

How can I get a single row on a inner join not to double my sum

I have two tables (DEP_DEZ & CAR_EXP): ID FK_ID_CHESTIONAR TOTAL_E TOTAL_F TOTAL_STOC 261 644 87 152 48 ID MASA_TOTALA FK_ID_CHESTIONAR 405 20 644 406 45 ...
0
votes
1answer
144 views

Oracle 11g PGA advice view is empty when it shouldn't

I have the following situation in an Oracle 11g server (it's part of a RAC). PGA_AGGREGATE_TARGET is set, STATISTICS_LEVEL is TYPICAL, and yet V$PGA_TARGET_ADVICE shows up empty. Why does this happen? ...
0
votes
1answer
288 views

Moving data from one DB to another where the table/column structure is identical

Question about the import / export process. I know is SQL Developer I can export all the columns from a table and import into the same table on another server. My question is - what happens with the ...
2
votes
2answers
18k views

insufficient privileges while executing oracle stored procedure?

Im getting insufficient privileges error while executing the following oracle stored procedure. Im using Oracle Database 10g Express Edition. CREATE OR REPLACE PROCEDURE sp_update_acounts( ...
1
vote
2answers
64 views

Getting results without using IN

Oracle has a limit on the arguments in the IN statement of 1000, I believe, is there a way around this? If I had a statement similar to this (with 1000 ids or more): SELECT COUNT(*) FROM "VERSION" ...
0
votes
0answers
44 views

Case statement in a where clause in Oracle

I am writing a report for a customer using SQL. The end result of this report is to show a group of people with an expired certification or expiring certification. The report has a paramter,&param,...
1
vote
2answers
98 views

Tables Visible to a Specific User?

Is there a way to get a list of tables and views that a specific user can see, without logging in as that user? Initially, I tried something like select distinct p.table_name as value from ...
1
vote
0answers
48 views

Oracle tuning strategy for columns with long tails

We have an Oracle 11g database, where the two biggest tables are Customer and Order. The order profile of our customers has a long tail: we have millions of customers, with most only having a small ...
2
votes
1answer
9k views

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

I have a problem with a SPFILE as shown below.How can I get rid of that problem?Any suggestions? SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE ...
6
votes
5answers
21k views

Simplified/automated datafile free disk space reclaim

On Oracle version 11g: After Googling, I can't find a simple way to reclaim free space after deleting a table. I've found a lot of explanations, telling how the datafile becomes fragmented, the big ...
1
vote
1answer
979 views

How to know when a query was executed?

How to find out when a query was executed? I ran a query couple of days ago, I'm not sure about the exact time. I want to know at what date and time the query was executed. Is it possible to get that ...
0
votes
0answers
108 views

Connecting to oracle 11g through maestro gives initialization error

I'm very new to oracle DB. When trying to connect to oracle through maestro, this error comes up: Initialization error SQL*Net not properly installed OracleHomeKey OracleHomeDir Dont have any ...
3
votes
2answers
2k views

Is index performance on a TIMESTAMP(6) column impacted when converting it to a number

If I have an index on a TIMESTAMP(6) column, am I forced to use this column as a TIMESTAMP(6) to get the benefits of the index? Can I convert this column to a number with second precision (...
-1
votes
1answer
9k views

error:ORA-01008: not all variables bound

I created a bus table and when I try to insert values in the table like: insert into bus values(&bus_no,&source, etc.,); I get an error: ORA-01008 not all variables bound
8
votes
1answer
583 views

Designing Simple Schema for Disaggregation of Demand Forecast

I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw ...
1
vote
1answer
92 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
289 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
51 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
495 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
144 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
123 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
364 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
16k 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
622 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
138 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 ...