All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

3
votes
3answers
1k views

Selecting records where date range is current year minus #years

I want to select and eventually delete records from a point in time earlier than current year minus "x" years. I'm unsure of the most efficient way to do this.
0
votes
1answer
44 views

Location of RMAN backup

I have two servers. One is DB server another is RMAN Catalog DB server. From my RMAN Catalog, I connect to my remote DB server and I run the backup. Where will the backup files be stored? Will it be ...
1
vote
2answers
412 views

How to set Persian calendar,currency,numbers,date and time and text in oracle database?

I have installed oracle database 12c on my laptop. I want to be able to store text,numbers,calendar,currency,... in my local language (Persian) in database tables and also show contents in Persian ...
6
votes
3answers
3k views

Why would one need multiple instances of same Oracle database?

I came across following statement while reading this short article - An instance is the software (and memory) that Oracle uses to manipulate the data in the database. In order for the instance ...
0
votes
2answers
63 views

Is dmp file suitable despite ORA-01555 'snapshop too old' warnings?

Well, the title of the question is self-explanatory. Export finished successfully with warnings. Log shows several ORA-01555 snapshot too old errors. - Is it OK to use that dmp file to perform an ...
1
vote
2answers
48 views

Oracle lsnrctl stop

What does the oracle utility "lsnrctl" actually do? For instance, lsnrctl stop actually stops the listener and kills the process id, but does it also remove lock files or set/unset variables?
1
vote
0answers
49 views

trigger to first validate values in other table and then update

I have problem coding a trigger. The table structure is this: table 1: inventory field 1: item_code(PK) field 2: item_desc field 3: unit_price table 2: stock_master field 1: item_code(fk) ...
1
vote
2answers
1k views

Dropping Global Temporary Tables

In Oracle when you try to drop a Global Temporary Table that is in use you get the following exception: ORA-14452: attempt to create, alter or drop an index on temporary table already in use This ...
1
vote
0answers
58 views

Oracle migration to SQL Server

I am working in a Oracle 11g database migration to SQL Server 2012, I did the migration using SSMA v5.3 and all works fine. However, I did re-write code in some objects like triggers and stored ...
0
votes
1answer
1k views

Two Oracle clients on the same server

Is it okay to have two oracle clients (both 11g and 12c) in the same db server? What are the potential issues I could face? The reason im doing this is to connect the app server where I'm deploying ...
0
votes
1answer
70 views

Does Oracle Instant Client 12c support LDAP?

I've been using Oracle Instant Client 12c as a lighter-weight alternative to the full Oracle installation. It works fine when I resolve service names with tnsnames.ora, but now I would like to ...
0
votes
1answer
182 views

ASM RMAN BACKUP FAILED

I'm trying to migrate a filesystem to ASM but when I start with RMAN backup, I face the following error, which is weird because I haven't performed any backups before on the same database. RMAN> ...
0
votes
0answers
34 views

Oracle DB - DR corruption

One of my databases in my DR is fully corrupted ( I have checked running validate database using RMAN). Now to recover all the data files which are corrupted I need to copy the data files from my ...
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
1answer
106 views

Oracle How to Generate Multiple Ranges Based on a Result Set

Given a table like this: with qtys as ( select 3 as qty from dual union all select 10 as qty from dual union all select 9 as qty from dual union all select 7 as qty from dual union all ...
0
votes
0answers
97 views

Export backup from a different location

I have two servers. One database server and another is an app server. I have client installed in my app server and I have the respective the tns in my app server to connect to the db server. Now can I ...
0
votes
2answers
1k views

Unable to create Central Inventory

I'm trying to install Oracle 11g on Linux and have already configured the variables and X-host but can't able to create Central Inventory. My Central Inventory location is /u01/app/u0 and group name ...
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 ...
0
votes
1answer
213 views

Why is the result cache always disabled in Oracle 11g

I'm trying to enable the result cache in Oracle 11g. I have used Enterprise Manager to change the result_cache_max_size and result_cache_max_result values to '1M' and '100' respectively (maybe these ...
0
votes
1answer
70 views

Is it necessary for rownum = 1 with Query.createQuery().getSingleResult()? [closed]

I'm a newie and I'm not really sure if MIN(My_Date) returns just one row if 2 or more dates are identical. Reference is a FOREIGN KEY. so to limit query: SELECT MIN(Shipping_Date) FROM ...
0
votes
1answer
360 views

How to insert all the dates in a date range into Oracle database?

For instance, The date range is from 26th Jul to 1st Aug, that is 26th Jul, 27th Jul, 28th Jul, 29th Jul, 30th Jul, 31th Jul and 1st Aug How can I construct a function (of course, I need to ...
0
votes
1answer
87 views

pfile - client parameter file - starting an instance remotely

I do not understand how we can start an Oracle instance remotely using a pfile. If we ssh into the Oracle server and issue: startup pfile='/path/to/pfile';` the pfile is on the server side. How ...
0
votes
1answer
754 views

How to see query executed for a specific user from V$SQL

I am trying to have the queries used for a specific user at the end of the day. But it seems the queries in this view V$SQL are deleted every short time. I am not sure how much time. Also i dont know ...
0
votes
1answer
48 views

what are the prerequisite for installing oracle 11gr2

I have windows 2008 R2 sever and I want install oracle 11gr2 on this server. So any one tell me what are the prerequisite tools for install oracle 11gr2 like as a jdk and ect...
4
votes
1answer
79 views

How can I specify the DDL of the index that is implicitly created when creating a materialized view log?

When I create a materialized view log, Oracle implicitly creates an index on that log. For example, when creating a materialized view log on table foo, Oracle creates the view log table mlog$_foo and ...
1
vote
0answers
53 views

Allow user select from table in another user's schema that has policies set

I have an oracle10g database with 2 users - user A and user B. I want to select data from a table in user A's schema from user B. I have granted user B select privileges on the table in question but I'...
0
votes
1answer
59 views

what exactly does an RMAN backup do?

I have a database with multiple schemas and data in the tables. I create an rman backup and backup archivelogs. I drop 90% of the tables. I do rman restore and recovery with controfile from autobackup....
1
vote
0answers
121 views

“Cut” records from one table into another in one oracle sql statement

I have a very big table T1 and i would like to copy every 15000 records to T2. After that I want to delete these same records. I want a fast and efficient way. I tried this but it wouldn't work : ...
10
votes
2answers
718 views

Where to start with Oracle?

This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL. You may find your question has been closed as a ...
0
votes
0answers
11 views

Monitoring external Oracle db, creating changes locally to SQL Server

I have a local mirror database of an external database that our vendor provides us read access to. Occasionally they add columns and/or table to their database. The addition of columns used to play ...
0
votes
2answers
156 views

generate/analyze trace for error 1461

I am trying to generate a trace file when arise errors with code ORA-01461. I have executed the following statements: alter system set events '1461 trace name context forever, level 4'; from alert....
0
votes
1answer
37 views

Oracle instant client memory footprint

In the link mentioned here --> http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html it says there is increased virtual memory footprint when running my applications in ...
0
votes
0answers
319 views

Error while creating encryption wallet in Oracle

I need to encrypt tablespaces in my Oracle DB. Following the instructions I edited sqlnet.ora file by adding ENCRYPTION_WALLET_LOCATION. Full content of file listed below: NAMES.DIRECTORY_PATH= (...
2
votes
1answer
521 views

Oracle instant client

What is the difference between instant client and the original Oracle client? The Oracle client is almost 800MB and the instant client is not so much. I need to install oracle client, ODBC drivers and ...
3
votes
2answers
26k views

Using Oracle client to connect to remote database

Although I can do a telnet to remote oracle port I am unable to connect. I have a following tnsname.ora connecting to oracle database using sql plus: remoteOracle = (DESCRIPTION = (ADDRESS_LIST ...
1
vote
1answer
1k views

Package Check Failed: Oracle DB 11g on RHEL 7

Trying to install Oracle DB 11g EE on RHEL 7. Installed the necessary packages as mentioned in the below link (Section 6.1). But I am stuck at the package check step, which fails because it couldn't ...
2
votes
1answer
1k views

Oracle Performance - Varchar2 (x char) x Varchar2 (x byte)

I know the definition diference between Varchar2 (x char) x Varchar2 (x byte). varchar2(20 char) means you can store 20 characters -- whereas varchar2(20) means you can store 20 bytes. What ...
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 ...
1
vote
0answers
29 views

How to best distinguish between “types” of missing data?

I am working on large tables (> 100 fields each) currently organized in flat-files in csv-format. An Oracle DB is to be created to store all the data and allow for several more or less intricate ...
7
votes
2answers
8k views

Can I use OpenLDAP to authenticate users in Oracle?

I like to use OpenLDAP to authenticate my Oracle users. It should be possible to fetch at least the password from the directory. There appearently is something for the Oracle LDAP servers, but would ...
0
votes
1answer
42 views

Oracle ODBC Driver, Tables and Columns Remarks

I'm using the Oracle ODBC Driver 11.2 to access an 11.2 Oracle Database. It seems that the driver does not return tables nor columns remarks (comments) when calling the SQLTables() or SQLColumns ODBC ...
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 ...
0
votes
1answer
33 views

database not opening

While altering a DB to open getting the below error. SQL> alter database open; alter database open * ERROR at line 1: ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [...
2
votes
1answer
268 views

Oracle table and number of blocks

What is the easiest way of finding out the number of blocks a table (oracle) occupies and the number of extents?
3
votes
1answer
212 views

DECODE or CASE STATEMENT for PARAMETER in WHERE CLAUSE [closed]

I am having an issue where I need to make 1 parameter have multiple options, almost like a case statement or decode function. Here is a snippit of the query below, We were using 2 parameters so he can ...
1
vote
1answer
982 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 ...
2
votes
1answer
53 views

Tkprof in critical environments [closed]

Why tkprof utility is not adviced to be used in production setups? What concerns does it have?
2
votes
2answers
3k views

Oracle 9: Convert date from mm/dd/yyyy hh:mm:ss format to iso8601 formatted datetime [closed]

Note: I only have read access to this database. I need to convert an Oracle 9 date type from d/mm/yyyy hh:mm:ss format to the iso8601 standard. Like the example below: 4/22/2015 12:02:56 AM => ...
1
vote
0answers
80 views

Oracle in hyper-convergence system

How does Oracle perform in hyperconvergence systems such as nutanix, simplivity? Oracle doesn't certify the underlying storage which nutanix or simplivity offers (file level storage) but in reality ...
0
votes
3answers
105 views

SQL developer licensing [closed]

SQL developer is free or do you require license for oracle database to use the sql or pl/sql developer? Also, Is it secure to connect to the oracle database since it connects to the db over a remote ...