Questions tagged [oracle-11g]
Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.
715
questions
0
votes
1
answer
2k
views
Import dump file schema not found
I want to import a dump file but I have this error:
Import: Release 11.2.0.4.0 - Production on Mar. Nov. 28 10:12:56 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved....
- 107
-4
votes
1
answer
352
views
Oracle Sizing pl/sql how to get past database size information?
I am learning Pl\sql developer and just starting to learn about DBA activities. I have been asked to get the sizing information of the past monthly growth of databases and tables. any suggestions?
- 98
2
votes
2
answers
56k
views
Best way to get current year minus 5 years
I'm actually have the next query that I use to obtain the current year minus 5 years in january in this format 'YYYYMM'.
select TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12*5*-1),'YYYYMM') from dual;
...
- 105
1
vote
1
answer
6k
views
How to Restrict Schema Access to Specific IP Address & user?
I want to lock down access to specific schemas. Restrict the access to specific users when the connection is made from the specific IP addresses.
Eg Only Access to USER1 and USER2
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
4
answers
4k
views
Knowing if Oracle account has been locked?
We have an Oracle that locks up randomly; we get error ORA-28000: the account is locked.
Is there some log that will tell me if an oracle account was locked throughout the day? Right now the Oracle ...
- 135
2
votes
2
answers
14k
views
Oracle 11gR2 Installation on Oracle linux 7 - Prerequisite condition failed for OS kernel parameter "semmni"
while Installing oracle 11gr2 on oracle Linux 7. The prerequisite condition to test whether the OS kernel parameter "semmni" is failed with below error.
Please find the screenshot of it.
Below is ...
- 55
1
vote
1
answer
3k
views
Mutual authentication via SSL Oracle 11g
I configure mutual authentication via SSL between client (Windows 7) and server (Windows Server 2008 R2). I create wallet via Oracle Wallet Manager (OWM) both on client and server, then I create ...
- 11
0
votes
1
answer
2k
views
Restore RMAN Level 0 without any archivelogs?
I have a level 0 backup, and control files, but I don't have the archivelogs. And I'm not using rman catalog.
I found ways to restore a L0 backup, if you have at least the first archivelog after the ...
- 107
-1
votes
2
answers
1k
views
Setting up Oracle XE on Linux
I'm trying to set up oracle-xe on my Linux machine. I just need an environment to play with Oracle SQL; this is not for production.
When I try to start SQLPLUS I keep getting one of the following ...
- 103
1
vote
1
answer
15k
views
Oracle 11.2.0.4.0 32 bits full client installation
I need to install an Oracle Client on a Windows 2008 Server R2.
The client must be 32 bits since the application that it is going to use it only works under 32 bits.
I´m getting crazy knowing what ...
- 135
4
votes
2
answers
3k
views
Applying patch oracle 11.2.0.4 on 11.2.0.2
We are going to apply Oracle patch 11.2.0.4 on Oracle 11.2.0.2. We have 11.2.0.4 patch. We are using (DBUA) for this upgrade. As DBUA will check the preupgrade requisites, we want to run preupgrade ...
- 69
2
votes
2
answers
2k
views
How to count the number of times an attribute appears in an inner query?
I have this query that I am trying to write out:
Which students (names) have taken more than one course from the same professor?
I have a students, course, and professor table. This is the ER ...
- 145
3
votes
1
answer
11k
views
Cascade primary key update to all referencing foreign keys in oracle
Is it possible to update all referencing foreign keys in Oracle when updating primary key?
A procedure like this that gets the table name and primary key and finds all foreign keys in all tables and ...
0
votes
1
answer
950
views
Migrate database through database link
There's a database called FOO in an Oracle XE 10g server that's due to be decommissioned (old.example.com). I've been asked to migrate it to some other Oracle XE 11g server (new.example.com). Both ...
- 1,079
0
votes
1
answer
1k
views
Why does this logon trigger work in one server but not another?
I created the below trigger in the same manner in the SYS schema on two different Oracle 11g servers, without any errors.
CREATE OR REPLACE TRIGGER LOGON_TRG
AFTER LOGON ON DATABASE
BEGIN
if ...
- 413
1
vote
2
answers
132
views
Is dropping foreign key constriants an event in Oracle?
I wrote the following trigger in Oracle:
create or replace trigger foreign_drop before ddl on database
begin
insert into ddl_log
select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, null, ...
- 131
5
votes
2
answers
5k
views
ORA-04091: table ExpenseTable is mutating, trigger/function may not see it
I have two tables and a trigger and am inserting values in ProjectsTable like this:
CREATE TABLE ProjectsTable
(
ProjectID NUMBER(6) NOT NULL,
ProjectName VARCHAR2(200) NOT NULL,
Cost ...
1
vote
1
answer
389
views
Need help on expdp and impdp concept [closed]
I have a multiple databases i.e (database1,database2) and i need to export a table from database1 to database2 . Here are the commands i tried
expdp hr/oracle@oracle tables=emp directory = ora ...
- 133
0
votes
1
answer
5k
views
Unable to connect to Oracle 11g server remotely using SQL developer ORA-12520
Hello everyone I'm unable to connect to my Oracle Server remotely using SQL developer ....while I can connect from SQL developer when I'm in the server directly ....I get the following error when I ...
- 140
0
votes
1
answer
510
views
Restore .bkp file of different version oracle [closed]
I have 12.1 Oracle version .bkp file. Can I restore it in 11.2 Oracle XE?
I have used below command:
RMAN> restore controlfile from
2> '/home/db_dump/201101.BKP';
And now getting below error:...
- 1
0
votes
1
answer
1k
views
Creating a After Insert Trigger
I created a trigger in table tbl_user_order.
and I want to insert data in retailer_deliver_customer table.
CODE:-
create or replace trigger insert_record
after insert on tbl_user_order
for each row
...
- 13
0
votes
1
answer
6k
views
Oracle Database authentication through Active Directory
We have Oracle 11g database running on a RHEL 6 server.
and we have a Active Directory domain running on windows 2008 server in same network.
I want to authenticate Oracle database users using this ...
- 101
2
votes
1
answer
9k
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 ...
- 355
-1
votes
2
answers
5k
views
ORA-01555:Snapshot too old error during execution of expdp command [duplicate]
After performing expdp command in production database (Oracle 11g - linux):
expdp abc/123 DIRECTORY=data_pump DUMPFILE=SA10_%u.dmp LOGFILE=SA10.log
TABLES="abc"."/1FE/019" PARALLEL=4 CONTENT=...
- 17
0
votes
1
answer
193
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 ...
- 355
-1
votes
1
answer
1k
views
Restore a dump file from a different server, with a different name
I Expdp a user with the name user_prd for example. I copied the dump file to another server, and I want to restore it with the name user_hom.
I tried remap_schema with no succes ( Im a SQL DBA, so it'...
- 7,374
0
votes
1
answer
70
views
How to implement if (t1.ID=t2.ID) returned nothing do the stuff
I have two tables one table1 containing ID, NAME another table2 is only ID.
I want to have all IDs in table2 and return ID in table2 and NAME in table1 but I also want IDs in table2 which is not ...
user108219
3
votes
2
answers
608
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 ...
- 131
0
votes
1
answer
483
views
limiting db user password to 3 days
Can any one help me with this query?
I want to change expiry date for db user to 3 days from current date, but I am not getting the query for the same.
I have installed Oracle Express 11g, it is ...
- 1
2
votes
1
answer
1k
views
Slice a period of time (start and end date) into 20 minute chunks, and group by a separate row
I have an Oracle database where I want to create time slots of 20 minutes, and would like to retrieve all the time slots available for a doctor on each clinic based on his shift AND the appointments ...
- 123
1
vote
1
answer
2k
views
Generate & Email report from Oracle Forms 11g
I generate a report in PDF through passing of parameters from Oracle Forms 11g. I have a requirement to email the same report automatically as well. Following is the part of forms where I have passed ...
- 11
1
vote
1
answer
1k
views
Huge difference in execution time of update statement with default value
Last week I tried deploying a new version of an application on an app server. Prior deploying, some database scripts needed to be executed on an Oracle 11g database.
The exact version of the database ...
- 113
4
votes
2
answers
915
views
RDS Oracle SE - Performance troubleshooting options
I have an RDS Oracle SE2 instance with Amazon RDS. Our team is looking forward to move couple of applications to RDS. But we’re looking for options that are available in RDS Oracle SE to perform ...
- 435
0
votes
2
answers
720
views
Optimization by replacing subquery with named subquery having INLINE hint
Let's have these two tables:
create table table_x(
x_id varchar2(100) primary key
);
create table table_y(
x_id varchar2(100) references table_x(x_id),
stream varchar2(10),
val_a number,
...
- 131
1
vote
2
answers
948
views
ORA-04031: unable to allocate X bytes of shared memory
Trying to start oracle service, shows me the follow error:
I have the follow SPFILEXE.ORA:
Im using ORACLE XE, but really I don't know which parameter change and what should be the value.
Please ...
0
votes
1
answer
482
views
Oracle DBID what is logic to create oracle DBID?
Oracle DBID what is logic to create oracle DBID ? Does it ensures uniqueness across multiple database in different host.?
Can I relay on DBID to identify database among 100 database in the ...
- 194
1
vote
1
answer
67
views
List of unreferenced objects
I need a list of unreferenced objects, that is, objects that neither have any reference with other objects nor are used in any API (Oracle stored procedures).
This is required for reducing and ...
1
vote
2
answers
290
views
Can `sys.aud$` recycle itself?
In Oracle 11, what happens if I leave sys.aud$ to get bloated up to soak up the whole SYSTEM TABLESPACE?
Does it start rewriting the oldest records? Can Oracle be configured to do so?
Does it stop ...
- 66
0
votes
1
answer
2k
views
Resolving TNS Permision Denied for XE on Windows 7
I am working with Oracle 11g XE on a Win7 laptop. I have had this up and running without issue for more nearly two years. Went about a month without connecting, and the next time I tried to I got “...
- 101
0
votes
1
answer
390
views
Is wrapping only possible for package bodies?
I am using oracle 11gR2 database in which I was trying to wrap my scripts. I read the documents and tried to wrap a sql script which contains a create table statement. I used below commands:
wrap ...
- 152
0
votes
2
answers
203
views
Load clob from "insert into" output file
In the process of moving data from live to the dev system (completely isolated), I have been given a series of "insert into" files for the affected tables.
One of these tables contains a clob that ...
- 101
0
votes
2
answers
769
views
incomplete sysaux expdp unload (ORACLE)
my question is really simple, and does not need any particular documentation.
When I run expdp command like this using Oracle Database 11g Release 11.2.0.4.0 - 64bit:
expdp system/******* directory=...
- 3
-1
votes
2
answers
3k
views
Rootcause Oracle database crash due to open connections
We have a Windows 2003 server with a Oracle 11g DB installed and running a Java backend application that mines and inserts data onto the DB. The data is then distributed with PHP via a web server
...
- 123
1
vote
1
answer
2k
views
How do you create a new database with new sid on Oracle's pre-built 11g virtual machine?
I downloaded DeveloperDaysVM2016-06-02_13.ova from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and successfully loaded it into Virtual Box.
I logged ...
- 411
1
vote
1
answer
6k
views
How to pass data and loop in procedure Oracle
I have a procedure in an Oracle database which I use to insert users into a table. But now, I need to implement a loop, so that I use that same procedure to insert many users.
I have little knowledge ...
- 113
1
vote
1
answer
991
views
drop table causes ORA-08103 with another one
In my process I have 2 tables.
target table, load table.
The target table holds the current data, and a lot of queries are fired against it.
When new data coming we use a FULL LOAD and the goal ...
- 115
0
votes
1
answer
602
views
ORACLE- Database unable to START/OPEN
When I'm trying to start a database (after a server patch was applied), I get the following error and I am unable to open the database.
SQL> startup;
ORACLE instance started.
Total System Global ...
- 73
2
votes
1
answer
3k
views
Oracle 11g database datafile addition to ASM without '+' sign
what will happen, if we miss the '+' sign while adding datafile to ASM in oracle 11g database? Whether datafile added will be ASM based or Non-ASM based?
0
votes
3
answers
11k
views
Display department name,the number of employees and their names
I have two tables (employees) (departments)
I want to generate the following output:
num_of_employees department employee_name
10 marketing john
10 ...
- 1