Questions tagged [oracle-11g]
Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.
714
questions
0
votes
2
answers
755
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 ...
- 8,552
0
votes
1
answer
343
views
Why is the schema name necessary here and how to compile without it
I'm working on an Oracle Forms 11g application linked with an Oracle 18c database and I stumbled upon a strange error when compiling my form.
Here's a snippet to illustrate the problem (I changed the ...
CommunityBot
- 1
1
vote
1
answer
745
views
What are the ways to recover Oracle database that has corrupted data file, very old RMAN full backup, and incomplete/corrupted archived logs?
For an Oracle 10g/11g database that refuses to start due to datafile (SYSTEM01.DBF) corruption, does RMAN restore command immediately overwrite all current datafiles from the last found full RMAN ...
CommunityBot
- 1
4
votes
1
answer
21k
views
Why doesn't "As SYSDBA" work from SQL Developer?
I am a SQL Server developer/DBA consultant who is helping out a customer who recently lost their Oracle DBA. I have done development on Oracle, but not much DBA work, and not in multi-homed ...
0
votes
1
answer
2k
views
Oracle 11g - Decreasing Big Table Size
I want to reduce the size of a table.
I thought if I could do this with export / import, but when I check it
with the ESTIMATE_ONLY option, it doesn't seem to work.
I have no chance of using ...
CommunityBot
- 1
0
votes
1
answer
1k
views
How to view databases and tables after 'imp' import using Oracle SQL developer
Maybe that's a wrong form of answer...
The problem is:
I've got an Oracle database dump file (very old, created with imp utility)
At last, I've imported it. The command was imp file=E:\Downloads\...
CommunityBot
- 1
0
votes
1
answer
365
views
error writing input to commandERROR: Invalid username and/or password
This is the error I have been getting on my RMAN notifcations on my backup jobs for the last day or so. The title is exactly how the message is formatted.
~~~End Step Output/Error Log~~~
Error Log:...
CommunityBot
- 1
1
vote
1
answer
4k
views
Is it possible to show DML that is locking some table?
I already saw here how I can list my locked objects. Suppose I, using PL/SQL Developer(pink cylinder logo one), run select * from someTable for update and didn't commit nor rollback. Now using the ...
CommunityBot
- 1
0
votes
1
answer
552
views
Dataguard the datafiles are created in another diskgroup
I have a recently made physical dataguard configuration and everything works correctly so far, but I've noticed that the datafiles that are created in the primary are created in the standby in a ...
CommunityBot
- 1
0
votes
1
answer
669
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 ...
CommunityBot
- 1
4
votes
1
answer
8k
views
Oracle Database Server TNS Listener Remote Registration Vulnerability (CVE-2012-1675)
Some of our Internal DBs Oracle installed in Linux and Windows servers have been highlighted with the issue Oracle TNS Listener Remote Poisoning (CVE-2012-1675).
This security alert addresses the ...
CommunityBot
- 1
2
votes
1
answer
9k
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 ...
CommunityBot
- 1
3
votes
3
answers
8k
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-01110: ...
CommunityBot
- 1
2
votes
2
answers
25k
views
How can I find port running Oracle Enterprise Manager
I need to connect to the Oracle Enterprise manager of a remote machine. But I dont know the port on which OEM is running on the remote machine. I have access to the machine from terminal though.
Is ...
CommunityBot
- 1
1
vote
3
answers
18k
views
Use of Parallel hints in a SQL query
What's difference between parallel (4) and parallel 4 with and without brackets?
For eg :
select /*+parallel(4) */ * from table_name;
select /*+parallel 4 */ * from table_name;
0
votes
1
answer
1k
views
How to convert the JSON date with to_date function
How to convert the specific string Aug 6, 2018 3:17:11 PM with the function to_date?
That string is a result of a Java parsing mechanism for JSON. I would like to be able to recover it directly from ...
CommunityBot
- 1
0
votes
1
answer
3k
views
Resizing and reclaiming free space on Tablespaces
I would like to reclaim some free space from our tablespaces. I can see that UNDOTBS1 and USERS for example have 30GB and 10GB free.
these are the tablespaces:
| tablespace | used mb | free mb ...
CommunityBot
- 1
9
votes
1
answer
1k
views
Oracle Ref Partitioning: Deadlock due to child table row migration
My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc.
ORDERS table is LIST partitioned on STATUS column and all ...
CommunityBot
- 1
0
votes
1
answer
2k
views
ORA-32771: Cannot Add File To Bigfile Tablespace
I have 1TB tablespace that have one bigfile , The disk size now full and added extra disk to my server but there is no way to expand Drive that bigfile in it , so need to add another bigfile ,if ...
CommunityBot
- 1
2
votes
1
answer
10k
views
How can I troubleshoot issues with dynamic registration?
For testing I have been trying to install Oracle 11g on a test system with a single database several times with the aid of snapshots. This most recent iteration I have been having issues getting the ...
- 40.9k
4
votes
1
answer
4k
views
Oracle11g password expiry issue
I have a user which has a default profile in oracle db.
I want to reproduce the following warning
ERROR:
ORA-28002: the password will expire within X days
In dba_profile I set following values.
...
- 1
3
votes
2
answers
638
views
Oracle using sub-optimal index on bind variables
Let's say you have a table, called ORDERS, which contains ORDER_NUMBER, CUSTOMER_ID, and ORDER_DATE (among other fields). And you have one index on ORDER_NUMBER, another on CUSTOMER_ID, and third ...
CommunityBot
- 1
0
votes
1
answer
142
views
Calculate (n) workdays from a given date, using a calendar table
As shown on below image, I have two tables;
Customer : with about 4 million records; and 3 columns
[Customer] : Customer ID
[Date] : Creation date
[Num_Days] : Number of working days to calculate ...
- 237
-3
votes
1
answer
64
views
MYSQL workbench like gui tool for oracle 10 g or more
I am a java full stack developer who want to write oracle sql queries to simplify writing is there any mysql workbench like similar gui tool for oracle by oracle or any other please sugest
- 126
1
vote
2
answers
4k
views
Refresh materialize View fast on commit multiple table
In a materialized view with refresh fast on commit i have the below query
select pp.ROWID AS PP_ROWID,aup.rowid AS AUP_ROWID,
pp.hierarchicode,pp.id, aup.userid
from app_useracess aup,...
1
vote
1
answer
227
views
Updating an oracle updatable view through an oracle_fdw foreign table in PostgreSQL fails with ORA-02014
I created an updatable view V1 in an Oracle 11g DB (it has INSTEAD OF triggers for UPDATE, DELETE and INSERT); it originally had a GROUP BY query in it, which I moved to another view V2. This other ...
- 54.8k
6
votes
6
answers
30k
views
Oracle 11g setup closes without errors on pre-requisite check
I'm installing Oracle 11g and the setup wizard just closes automatically after the pre-requisite check.
I have administrative access
UAC is disabled
IPv6 is diabled
There is more than enough free ...
- 1,746
2
votes
3
answers
10k
views
Oracle - execute DML query as different user
I would like to be able to run DML queries (INSERT,UPDATE,DELETE) by executing a procedure on a given schema. Those queries need to change data on different schemas on the same instance. So, basically,...
- 109
1
vote
1
answer
13k
views
Why can't I log in to SYSTEM without AS SYSDBA
When loging in to sqlplus as SYSTEM, why does the login fail with an invalid username/password error if I do not include "AS SYSDBA"
C:\Windows\System32>sqlplus /nolog
SQL*Plus: Release 11.2.0.1....
- 25k
0
votes
0
answers
188
views
Top reasons for temp tablespace to go offline
It was marked offline and it is fixed already.
We want to know the list of causes that marks the temp tablespace offline.
5
votes
2
answers
53k
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);
...
- 1
0
votes
1
answer
2k
views
Improving OPENQUERY performance from Oracle to SQL Server
I'm currently working on a database migration, from Oracle 11g to SQL Server 2019. We're talking here of about 10-12 GB, in something like 300 tables (so creating them manually is out of reach, as is ...
CommunityBot
- 1
1
vote
3
answers
387
views
Which one is created first? Database or Instance?
The Oracle database includes all the physical files belonging to the server while the Oracle instance will provide the interface between the user and the data he wants to manipulate. But I want to ...
- 131
0
votes
2
answers
1k
views
expdp fails with error ORA-31626 without any details on a full export
I am exporting a complete database (full=y) on oracle 11gR2 on Linux, and always get this error. There are no other messages, so the problem is not about timeout or permissions as shown elsewhere. ...
- 5,062
1
vote
1
answer
3k
views
Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results in another table oracle
I am very much new to the DB world, so wanted to review whether I am following right approach or not.
I have two tables,
table A --> is a table with 40 columns
table B --> is a table with the 3 ...
CommunityBot
- 1
1
vote
1
answer
2k
views
Receiving error "can not access rows from a non-nested table item" while working with associative arrays
Below , I'm trying to use the data of the array I've declared,
in the where clause of the query but I receive the following error :
declare
v_input varchar2(400) := '0,1,2,3,4';
type ...
- 3,097
0
votes
1
answer
211
views
EMCA insists that it cannot connect to the service
Really new to Oracle and being a DBA in general. I am trying to set up a development environment so that I can play an learn oracle better. Enterprise Mananger failed to configure itself when I first ...
CommunityBot
- 1
0
votes
1
answer
198
views
EMStagePatches_$ORACLE_SID is using up space
We have this directory:
$ORACLE_HOME/EMStagePatches_$ORACLE_SID
We are running out of space and this directory is using up a lot of space. Is it safe to delete it? I think it is something to do with ...
CommunityBot
- 1
10
votes
4
answers
104k
views
ORA-01502: index or partition of such index is in usable state problem
I have a table in my Oracle database, where
select pkcol, count(*) from myTable group by pkcol having count(*) > 1;
yields
PKCOL COUNT(*)
------- ----------
1 2
2 ...
- 101
3
votes
4
answers
31k
views
How to calculate or track monthly database growth?
Using oracle how can i track database growth monthly? The following is a query apart of a query i used to stored information using sql server. Is there a way to replicate the same using oracle?
...
0
votes
3
answers
219
views
Command Line Creation of a new 12g Oracle Database
I am new to creating an Oracle database using the command line. I am logged in as superuser; now I would like to create a new blank database, "mynewdatabase".
$ . oraenv
ORACLE_SID = [oracle] ? ...
CommunityBot
- 1
-1
votes
1
answer
169
views
How to restore a Oracle Database if i only have the datafiles?
My Oracle database Test server crashed, I have no rman backups but I still have the datafiles because they are on a SAN Storage, so if I have datafiles, how can I restore my Oracle database?
- 9,150
-1
votes
1
answer
36k
views
ORA-01555: Snapshot too old rollback segment with name "" too small error
I have created a trigger which works on two fields, first field(blob) and second field(varchar2). This trigger updates the blob when there is some changes in second field, from a blob value of another ...
0
votes
1
answer
4k
views
Unable to connect to oracle database. Getting ORA12505 error
I am newbie to ORCALE
I am trying to connect to oracle server and i am getting this error. This is was working fine before but now i am facing this issue. Need help
An error was encountered ...
- 121
0
votes
1
answer
52
views
Query optimization [Oracle DBMS]
I have a query that always must return a single row and its execution time is about 5s, so I need to optimize it:
SELECT * FROM RESPONSE res
INNER JOIN REQUEST req ON res.ID = req.ID
WHERE res....
- 3,097
0
votes
1
answer
73
views
Migrate from Oracle 9.2 to 11g
I have a executable application that using Oracle ODBC Driver V9.2. My Old server Have Oracle 10g on windows 2003 32bit. I install and configure new server using Oracle 11g on Windows Server 2016 x64.
...
1
vote
2
answers
732
views
How to switch between Oracle 11g and 12c on same linux
I have oracle11g and 12c installed on same machine and few test cases that need to be run on both versions.
Can power up both database simultaneously and run tests parallely.
Do you know any document ...
CommunityBot
- 1
1
vote
1
answer
1k
views
Error ORA-12505 when connecting to Database Oracle 11g SQL Developer
recently I installed Oracle XE 11g with SQL Developer.
When I first ran it and created a database and a new user, everything worked perfectly - I was able to connect to the database and was able to ...
CommunityBot
- 1
0
votes
0
answers
158
views
Best approach for purging records (including related records) from an Oracle database with undo capability?
Please let me know which approach you would take.
The problem
I have a large Oracle 11g DB that needs to have old records purged to free disk space. One of the requirements for the project is that we ...
- 193
1
vote
2
answers
540
views
Connection denied from Java application
I try to connect from my little Java class to Oracle11g database and connection was refused with a SQL error.
From client (SQL Developer) connection works.
This is the ERROR when I try to run java ...
CommunityBot
- 1