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

0
votes
2answers
120 views

Duplicate data in a table given table name

For a performance testing application we need to have lot more data in our tables than what we have now. How do I duplicate the data in a table. In the process of duplicating I also need to set new ...
0
votes
0answers
334 views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw. but when use this extenstion i get error OCIEnvCreate failed to create environment handle after installing oracle_fdw. my ...
1
vote
2answers
4k views

Is SQL*Net message from dblink > 30% a performance issue?

I have a Java web-application running under Glassfish. App accesses Oracle using JPA (eclipselink). Last 2 days I'm observing some perfomance issues: some messages about long running queries (10 to ...
0
votes
1answer
251 views

Oracle SQL Summing Values From Two Different Tables

Hey all so I am trying to add to (or subtract from) a column of different values based on a separate column in another table. Here is a example of what I am tying to do: Table 1: (Current Inventory) ...
0
votes
1answer
37 views

Is PDO_OCI classed as ODBC? [closed]

I did post this question at Is PDO_OCI (PHP PDO using Oracle Instant Client) classed as ODBC? anonymously, but then it wouldn't allow me to comment on it so I registered and am posting it again here. ...
1
vote
1answer
175 views

User can see Oracle tables via ODBC that her account does not have access to [closed]

We have a user whose Oracle account, according to ALL_TAB_PRIVS, has access to tables A, B, and C. However, when the user creates an ODBC and connects to Oracle via Microsoft Access (using her Oracle ...
0
votes
0answers
12 views

Configure Oracle DB to store backups on External Storage [closed]

How can we Configure Oracle DB to store backups on External Storage ? Oracle DB is a vm machine on ESXi Host Let me know if you need more details. Please advise.
0
votes
0answers
87 views

Oracle Linked Server only works with Sql Login

I'm having this weird error for some time. When I login to sql server and use the linked server SELECT * FROM OPENQUERY(MEGA, 'SELECT 1 FROM DUAL') I receive this error OLE DB provider ...
1
vote
1answer
992 views

Restrict Oracle DB Login based on SQL Client being used

We have an application in our organization that is based on Oracle Forms 11g (on Oracle Fusion Middleware/WLS). Users have access to the application through accounts created at the database (i.e. they ...
0
votes
2answers
517 views

Join multiple Tables by date

i got some problems on my approach to join a couple of tables by a date range. This are my tables: RETURNS: RETURN_DATE RETURN_AMOUNT 02.05.14 2 02.05.14 3 02.05.14 1 ...
1
vote
2answers
309 views

Oracle SQL Creating Copy of Table Using DBMS_metadata

so I am trying to create a copy of a table using dbms_metadata. I know you can get the DDL of a table by executing DBMS_METADATA.get_DDL('Table','TABLENAME') so my question now is how can I use this ...
0
votes
2answers
34 views

Check attribute table referenced foreign key

I have two tables a and b like this: a: id, type b: a_id, ... There is a limitation for which the references of a in b can only be those which have type equal to a limited set of values. Is it ...
0
votes
1answer
73 views

Behavior of Oracle During Very Large Transactions

I understand that if a transaction is large enough to overflow all of the redo logs on an Oracle database, and the database is in ARCHIVELOG mode, the transaction will wait until the archive log file ...
0
votes
2answers
217 views

How to prevent users other than DBAs from creating database links

To begin, please keep in mind that I am a very green DBA. I have done prior research and have not really found anything too promising, or maybe it just seems that way because I am still struggling in ...
0
votes
0answers
21 views

How to use DMBS_LDAP to select 3 attributes for selected entries

I'm attempting to use DBMS_LDAP to create a procedure in Oracle 11.2.0.2.0 that ultimately updates an employee table from active directory. The attributes I want are sAMAccountName, GivenName, and ...
0
votes
1answer
81 views

How to directly query other user tables

I am trying to learn Oracle database and I am having difficulties on importing a backup file with impdp command. I googled deeply but couldn't find answers to my problems. What have I done until now ...
0
votes
1answer
23 views

Reusing views to do aggregate queries [closed]

I have created some views which give me some values. So some thing like the following select from a table of all ids gives me some thing like id1 id2 id3 id4 view1 gives me id1 2 id1 1 id1 3 ...
1
vote
1answer
2k views

Oracle Full Client / Database Client package locations

I need to install multiple versions of the Oracle client on an app server for an automation project. The platform I'm looking for in specific is Linux, 64 bit. Oracle's network of websites are a maze ...
0
votes
2answers
138 views

Best practices for generating a tnsnames.ora entry

I need to integrate some scripts that export and import data from Oracle databases. They are shell scripts that rely on the TNS name being set up (e.g., scott/tiger@tns). The application owners are ...
0
votes
2answers
244 views

Is there any other way that we can take auto backup in oracle 11g apart from RMAN?

I have tried with RMAN. But is there any other possible way that we can backup database and tables. backup using queries or stored procedures is possible?
8
votes
5answers
20k views

PL/SQL: best way to count elements in an array?

Given this: DECLARE TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; MY_ARRAY T_ARRAY; V_COUNT INTEGER; I would like to do: BEGIN -- ... some code filling the MY_ARRAY ...
0
votes
0answers
152 views

Reviewing ways to generate monthly tables using PL/SQL automatically

I am attempting to automate a set of tables that my colleagues produce monthly, using PL/SQL. The set of tables contain data about different metrics (e.g. Customer revenue, customer demographic ...
0
votes
1answer
101 views

Which parameters and metrics to look at when CDC based replication is causing high load?

I'm investigating the reasons why a Tungsten Replicator extractor is causing heavy load on an Oracle 11g database. The replication is set up to use synchronous CDC and there is no unusual load while ...
0
votes
0answers
90 views

Oracle In-Memory POPULATE_STATUS does not change to completed

I'm experimenting with the Oracle In-memory options for BI purposes, when I'm trying to populate one table to in-memory in the v$im_segments the POPULATE_STATUS does not change to completed, for other ...
1
vote
0answers
1k views

ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Process ID: 0 Session ID: 0 Serial number: 0 [closed]

When I tried to open a SQL connection in my command prompt I got the above exception. Can anybody tell me the solution please? I am using Oracle 11g, Windows 7 environment. I just downloaded and ...
1
vote
1answer
43 views

Is there a way to get disc space remaining using an SQL query on an Oracle database?

We experienced a problem recently where the disc space ran low and some queries started giving unpredictable results. The root cause was that we'd take too many restore points and filled up the disc. ...
0
votes
0answers
146 views

Is it possible to configure SQLNet / TNS_ADMIN options *without* sqlnet.ora file?

Background: We have an application using OC(C)I via Oracle 11 Instant Client, and we need to set DIAG_SIGHANDLER_ENABLED=FALSE to disable OCI overwriting our own crash handling. However, this is a ...
0
votes
1answer
137 views

Corrupt date fields causing query failure in Oracle

Having a real problem with date fields in Oracle. Executing the following works: ALTER SESSION set NLS_DATE_FORMAT ='DD-MON-YYYY'; select * from mytable where id = 1; However, changing the ...
1
vote
0answers
1k views

DDL trigger AFTER ALTER ON SCHEMA behavior

Can anyone please tell me what's going on here: CREATE TRIGGER afr_alt_trig AFTER ALTER ON SCHEMA cols ora_name_list_t; BEGIN IF (ora_dict_obj_type = 'TABLE') THEN select column_name bulk ...
0
votes
1answer
45 views

How to persist a lot of boolean fields?

I need to persist on Oracle Db the follow check box table : The simple thing that I can imagine is to create a Table with 15 boolean fields, but since that the rows and the columns could increase ...
0
votes
1answer
335 views

Oracle update join tables

I want to update my db records, which are related one to many. Please consider the following example : A person can have many contacts Stating above implies that contact table has a person_id ...
0
votes
2answers
2k views

Enterprise Manager cannot connect to 11g database instance

In Oracle 11g when we are opening OEM in browser it is showing the following message : "Enterprise Manager is not able to connect the database instance." Listener status is showing Up and Agent ...
0
votes
1answer
27 views

Help me define the tables in this scenario

We have 2 tables setup like this: WEB_GK_STATE PRG_CODE Varchar2(5) STATE Varchar2(2) WEB GK PRG_CODE Varchar2(5) TEXT Varchar2(4000) Here is some sample data: ...
0
votes
0answers
198 views

Connecting to Oracle database installed in one machine using Toad from another machine

I've an Oracle 12c installed in one machine in the LAN, and am able to access this database from the other machines in the LAN by the java applications using the jdbc thin driver connection ...
0
votes
2answers
320 views

Oracle: Inserting data using a database link

I am trying to use a SELECT query to populate a table in DB2 using a database link to DB1. I am using Oracle SQL Developer on a Windows 7 VM. I connect to DB2, and run the following query. INSERT ...
0
votes
1answer
2k views

Oracle alter column if exists

Hello I've messed up a certain db migration. I'm more of a postgres person than oracle, and I googled for a while and haven't find a way to do something like alter if exists. Basically what I need is ...
3
votes
3answers
2k views

how to change a user password while checking the current password in oracle?

In my application I have a change password form which users enter their current password and the new password. Then I change their password using the following command: alter user user_name ...
0
votes
1answer
60 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 ...
0
votes
1answer
552 views

dbca cant remove database cleanly

when I ran dbca as administrator under windows server 2008R2 is used to delete the database completely ( database folder, control files, datafies, redo logs, then remove all related windows services ...
0
votes
1answer
38 views

Use Oracle on Web App (New Question)

Our database project is about creating a web application where the user can add or edit information. My concern is How do I connect Oracle 11g with my PHP? Note: Our teacher did not teach us about ...
7
votes
3answers
39k views

“ORA-01950: no privileges on tablespace 'SYSTEM' ” : What error is it?

Earlier I created a user : SQL>create user suhail identified by password SQL>User created. SQL>Grant create session,create table to suhail; SQL>Grant Succeeded. but when I login as ...
1
vote
3answers
10k views

Installing scott schema for oracle 11g express

I want to install scott schema, a sample database in oracle. But in express edition, I can't find out it. Please help me install it on Oracle 11g express. I'm a newbie, pls help me
1
vote
4answers
38k views

Oracle Export using EXPDP gives ORA-06512 SYS.UTL_FILE error on Windows 7

I am using Oracle 11g and want to export a table using below statement C:\>expdp SYS/sys@MYDATABASE DIRECTORY = Mydir DUMPFILE = Customer.dmp LOGFILE = Customer.log TABLES = PERSON.CUSTOMER ...
1
vote
1answer
474 views

Comparing two different ODBC system-DNS entries. Which is faster? PS script

i want to find out which ODBC Connection is faster on my VM with Windows Server 2008 R2. I have two different ODBC providers. The first is "Oracle Instant Client ODBC" in v11. The other "Microsoft ...
1
vote
1answer
253 views

Why the optimizer does not use an Index Unique Scan?

I am on Oracle 10.2.0.4.0. I have a big Index Organized Table (IOT) with 74 columns and over 345 million of rows. An external tool execute a batch where I cannot touch the source code. The critical ...
2
votes
1answer
2k views

Can a view affect ALTER TABLE commands?

I provide a database system to a number of customers. The database is installed locally on Oracle. We periodically upgrade the database structure (either adding new fields, renaming old fields, ...
0
votes
1answer
91 views

How to prepare executon plan for given sql query on a server with 4 processors (Oracle database)

4 processors means the query runs in parallel. Select /*+ parallel(4) */ T1.A FROM T1,T2,T3 WHERE T1.A = T2.A AND T1.B=T3.B AND T1.C=T3.C Tables T1,T2 and T3 have 4,8, and 16 partitions ...
6
votes
2answers
5k views

Oracle's Merge v. Select->Update/Insert

What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value? I'm trying to optimize a database, but I haven't found any speed tests comparing the two. I found ...
8
votes
3answers
2k views

Is it bad idea to create foreign keys on the tables in a different schemas in the same database for big applications?

I am working on the transfer a big pl/sql web-based application to the dedicated server. This application is located in one schema with 70 packages of programm code. This application was made ...
0
votes
0answers
27 views

Oracle Mview - Get DML Type

I'm trying to create a replication of a table on a remote server. Table on remote Server : tbl_remote. i created a MATERIALIZED VIEW LOG on tbl_remote. also in my local server i created a ...