Oracle Database 11g Release 2. Please also tag oracle for search purposes.

learn more… | top users | synonyms

0
votes
1answer
33 views

Error “In a grant or revoke statement, the user attempted to grant a tablespace quota above the upper limit.” when trying to increase user quota

I'm attempting to increase the space quota on a particular schema. I'm using the following query to do so: ALTER USER MY_SCHEMA QUOTA "100 M" ON "USERS"; This results in the following error: SQL ...
0
votes
1answer
205 views

Can we expdp using JOINS in QUERY clause

I have a task to export data from database using data pump. I have successfully exported database using subquery in QUERY clause. But I want to know can we expdp using joins in QUERY clause?
0
votes
1answer
252 views

Can we export Selected Row with Subquery using data pump

I am trying to export selected rowS from my database using SUBQUERY but I got error. expdp directory=exp_data dumpfile=selected_row.dmp logfile=rows.log tables=sample,super query="where name IN (...
1
vote
2answers
134 views

Performance improvement by placing tables on a virtual RAM disk

I am trying to improve performance of queries by placing the tables in a virtual RAM DISK. I am using in Oracle 11.2 Standard edition with 4G of SGA and 2G of PGA. I created a Virtual RAM DISK of ...
1
vote
3answers
679 views

Oracle 11g ORA-01190: control file or data file 1 is from before the last RESETLOGS

I am running Oracle 11GR2, Enterprise Edition Release 11.2.0.3.0. I got one of my Test DBs corrupted today. Luckily, I have 2 weeks of backups in the Net Backup server, so I thought, "This is easy, ...
0
votes
1answer
136 views

NLS_LANG AL32UTF8/UTF8 for oracle database

I recently install a new database oracle on ubuntu using this tutorial. But a get an error oracle@ubuntu:/home/bruno$ echo $NLS_LANG /u01/app/oracle/product/11.2.0/xe/bin/nls_lang.sh oracle@ubuntu:/...
0
votes
1answer
47 views

data guard / network failure: deadline before global crash?

In a Maximum Availability scenario where there's one primary and one physical standby database, how long can we expect the system to run without problem when the standby site becomes out of reach (...
3
votes
1answer
116 views

Asynchronous batch commits in Oracle

We have an Oracle Database data warehouse that averages 120 commits per second (according to AWR). The top wait activity is log file sync, which is to be expected... For now, overall response times ...
0
votes
1answer
92 views

Not getting current IP address in my oracle database [closed]

I have installed Oracle database in my local machine and my local machine IP address is - 192.168.1.55 but in Toad its showing different SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual; ...
2
votes
1answer
157 views

LGWR is switching frequently in every 3,4,7 minutes. what should i do

I am running Oracle 11g R2 single instance on Linux 6.7. Data guard is also configured. In a database I have 4 groups of redologs, each group has 2 members and each logfile size is 200mb. The ...
0
votes
1answer
192 views

How to change initial extent of an existing partition

I'm having trouble with something that should be rather simple ... I'm sure I'm missing something obvious. I'm trying to change the initial extent of an existing table partition. For some reason, I ...
1
vote
1answer
52 views

Determine structure of out cursor

I have a function in Oracle that neither I nor my team wrote and is heavily obfuscated. However, it has a cursor out parameter. How can I find out the number and types that the cursor contains? For ...
0
votes
0answers
43 views

Oracle from_tz with Russian timezones shifts the input timestamp?

On Oracle 11g, I noticed that using from_tz to attach a TZ to a timestamp gives weird results for Russian timezones, but only after 25th October 2014. select from_tz(cast( '01-JUL-2015 12:00:00' as ...
0
votes
1answer
175 views

Load data from flat file into oracle table [closed]

I created a new database, then created a table "users". Now, I'm trying to load data to this table from a flat file called 'users.tbl', that has some records with the following format: 1|john|email@...
0
votes
2answers
59 views

Space is removing at end of the line

While running the insert script in sql*plus, space is removing. Can you please help us is there any plus command available in Oracle to not remove space at end of the line. Ex: After the text '...
0
votes
1answer
122 views

How do I kill a non-running impdp job?

I am trying to run an import operation into an Oracle 11G R2 database from a .dmp file using SQL Developer 4.1's Data Pump Import Wizard. I set everything up with IMMEDIATE start, but then the job ...
0
votes
0answers
269 views

How do I open Data Pump Import Wizard in SQL Developer 4.1?

How do I find and open the Data Pump Import Wizard in SQL Developer 4.1? I opened the Help search tool in SQL Developer 4.1 and searched for Data Pump, and found directions that include: 1.) ...
3
votes
1answer
104 views

Global command to destroy all disconnected data files in Oracle 11g

Is there a global command to delete/purge all disconnected .bdf files in an Oracle 11g R2 database? Here is the scenario: Every user-defined user and user-defined tablespace was deleted from an ...
0
votes
1answer
115 views

How can I launch SQL Developer?

I recently installed Oracle 11g R2 on a Windows 8.1 machine, but for some reason SQL Developer does not seem to launch easily. What specific steps do I need to take in order to launch a working SQL ...
1
vote
1answer
374 views

How do I import data into oracle 11g r2 using a `.dmp` file and a `.par` file?

How do I import data into a new installation of oracle 11g r2 using a .dmp file and a .par file? Here is what I have done so far, on a Windows 8.1 devbox: 1.) Open command line as administrator 2.)...
1
vote
1answer
51 views

Fetching user info from many databases at one go

I have a requirement to fetch all the schema available in 100+ Oracle instances running in RHEL environment. All the schema information such as username, roles assigned and is to be dumped in one ...
0
votes
0answers
114 views

From RAC to Standby RAC With Active Dataguard

I have oracle database 11.2.0.3 with RAC feature , with 2 node on oracle linux 6.4 : ol6-112-rac1 and ol6-112-rac2 I want to create active data guard with RAC feature from this database , these ...
4
votes
2answers
117 views

Selecting from random amount of tables with identical structure

I am programming along the lines of an unfavorable design. I have no influence on the schema because it belongs to a readymade standard software. For simplicity let's say this software stores address ...
0
votes
1answer
246 views

Cannot startup Oracle due to ORA-15041

I'm troubleshooting an Oracle instance that won't startup due to ORA-15041. The trc file shows this error: Failed to create file '+FRA_DATA' (file not accessible?) ORA-19816: WARNING: Files may ...
0
votes
1answer
82 views

how to write code for logon/logoff triggers in oracle?

I have written the code for a logon/logoff triggers. Logon is working but the logoff trigger is created with an error. Please give some suggestions. Logging table: create table logon_off_audit( ...
1
vote
2answers
78 views

Varchar2 datatypes are longer than expected

When I run the following script : create table tbl1 ( col1 varchar2(30), col2 varchar2(50), col3 number(4,2) ); desc tbl1; alter table tbl1 modify (col1 varchar2(35)); desc tbl1; drop table tbl1; ...
1
vote
2answers
123 views

TNS errors using Oracle instant client

I'm struggling to get my Oracle application working. I'm using the Oracle 11.2.0.4.0 instant client with a C++ application that is compiled using Visual Studio 2010. The machine is running Windows ...
0
votes
1answer
194 views

SGA size not decreasing after reducing memory_target (Oracle 11.2.0.1)

I have an Oracle 11.2.0.1 database using dynamic memory allocation with parameter MEMORY_TARGET. When I decrease it with SCOPE=BOTH, the size of the SGA doesn't seem to decrease along with it. I have ...
0
votes
0answers
61 views

oracle update statement is too slow

I have oracle database 11.2.0.4 on oracle linux 6.4 I have configure huge page on this OS . After 5 months of my projects , when i want to update any tables their have over 20,000,000 records , this ...
2
votes
2answers
79 views

Performance improvement after table analyze

I used a function that contains 3 tables a,b, and c. Table a has an index ind1 on two columns. I call a stored procedure that contains code and gets data using the above function. I analyzed my ...
2
votes
1answer
2k views

understanding ORA-01792 maximum number of columns in a table or view is 1000

We've recently migrated a server from Oracle 11 to Oracle 12c and ran into a few annoying issues. One of them is that a few a reports give the following error: ORA-01792: maximum number of columns ...
0
votes
1answer
83 views

Error ORA-12505 when trying to access a newly installed instance of oracle-11g express

I encounter problems installing a local instance of oracle express on my Windows 7 - SP1 computer. I did a basic installation, as administrator (run as 'administrator' on setup.exe). Then I tried to ...
1
vote
1answer
39 views

ORA-13506 An attempt was made to perform operation with an invalid Start/End Snapshot Pair during AWR report creation

When I create an AWR Report, the following error occurs: ORA-13506 Cause: An attempt was made to perform operation with an invalid Start/End Snapshot Pair. What does this mean, and what ...
1
vote
0answers
130 views

Is it possible to resize a blob image?

I've got a table with an image blob field in it, which stores jpg files. As those jpg files are shown in a web page, I would like to resize them in order to spend less bandwidth when my web page call ...
1
vote
1answer
108 views

What is the purpose of %ORACLE_HOME%/dbs directory in Windows Environment?

I see some files in there like an init.ora and opctrn11.h, but what is the purpose of this directory, if we use %ORACLE_HOME%/database to store the p/spfile instead?
1
vote
2answers
78 views

Serialize :new in trigger

I have a trigger that needs to create an Oracle Advanced queue message. The payload of that message should contain the new record that was created, that is accessible via :new. What I am looking for ...
0
votes
0answers
62 views

Oem 11g download link

Is the only way to get the following softwares from OTN by making service request? If suppose I dont have an OTN Account then how can I get it? Download Oracle Enterprise Manager Grid Control 11g ...
1
vote
0answers
68 views

Oracle Database Change Notifications only sent every 15 minutes, not immediately

I'm registering a listener for Oracle Database Change Notifications through JDBC (using the EclipseLink implementation of JPA, Oracle 11.2.0.3, Oracle JDBC thin driver 11.2.0.4). I'm getting ...
0
votes
2answers
119 views

Database not able to startup showing an error in reading control file

On Windows Server 2008 R2, when I am trying to startup Oracle 11g database it shows an error: SQL> startup ORACLE instance started. Total System Global Area 3206836224 bytes Fixed Size ...
2
votes
2answers
111 views

In Oracle what is the difference between STDEV and STDEV_SAMP functions?

We have a query that should return NULL when doing standard deviations in groups that have only 1 row. The normal STDDEV function returns zero. However I found another function called STDDEV_SAMP ...
1
vote
1answer
88 views

Oracle automatically unmount

I have a weird issue in my Oracle database, after 10 - 15 minutes of not using it (Not performing any query, insert on SQL Developer tool) then it will say ORA-27101: shared memory realm does not ...
0
votes
0answers
59 views

CDC Service for Oracle by Attunity Stops with Status Code ABORTED

We use Oracle 11gR2 as our OLTP layer behind our ERP product and we want to capture changes on SQL server for DWH use. In a new VM i make a clean install of SQL Server 2014 with SP1 (DB Engine and ...
1
vote
0answers
31 views

Diagnosing Oracle query problems by a non-DBA [closed]

I have a query (insert) run on Oracle 11gR2 (inside a stored procedure). It goes something like this: execute immediate 'truncate table global_temp_table'; insert into global_temp_table select /*+ ...
0
votes
0answers
34 views

ROUND function behaviour with dates

My question is about rounding the week. My understanding is when rounding y week oracle will round to same day of the week as the first day of the month. Does this mean if the first day of the month ...
0
votes
0answers
56 views

Oracle - compressed subpartitions in uncompressed table

I am working with Oracle 11.2. I have table which on subpartition level has in DBA_Tab_subPartitions column COMPRESSION set to ENABLED for all subpartitions. But on partition level in ...
0
votes
2answers
87 views

Can Oracle databases start automatically when Windows machine is booted/rebooted?

In one of my client machines every time Windows starts we have to start the Oracle database manually. Is there a solution/ command to start the database automatically when the machine is started/...
0
votes
0answers
64 views

How to concatenate using cast(collect()) in Oracle DB?

My current query: select distinct mb.BP_TYPE,mb.ABS,mb.BLD_SEQ_CODE, mb.BPCODE||'~'||(select LISTAGG(mbp.PROCEDURE,' ') within group(order by mbp.LINE,mbp.SLN1,mbp.SLN2) from MIS_BS_BB_PROC mbp ...
1
vote
1answer
79 views

sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows

While i am trying to connect with with database by the command sqlplus /nolog than the error message is coming "sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows" ...
0
votes
1answer
46 views

Oracle connection through visual studio

I installed oracle client 11.2.0 version in my machine and also installed Oracle data access providers for visual studio.I also installed Oracle Data Access 11.2.0.3.20. I am trying to connect to the ...
1
vote
0answers
80 views

Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS

I have the following scenario: Oracle 11.2.0.3 A 3rd party application, that requires cursor_sharing = TRUE A table (T2310) with a indexed field (C1000000161) that has no duplicated values Result ...