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
1answer
62 views

Oracle migration unable to create indexes

I want to move the contents of a database instance to another using the expdp and impdp utilities. The source instance is version 12.1.0.1.0 and the destination is 11.2.0.1.0 so I modified the expdp ...
0
votes
1answer
136 views

Why does Oracle Database Configuration failed on ubuntu?

I'm trying to install Oracle Database 11g Express Edition on my ubuntu server. After running command: sudo service oracle-xe start I get following result after configuration: Starting Oracle Net ...
-1
votes
1answer
163 views

How To Declare Fixed Length String Value In PL/SQL

In PL/SQL strings are declared in two different ways. These ways are fixed-length strings declaration, variable-length strings declaration. If we need to declare a variable-length string, we must ...
1
vote
1answer
203 views

Database gives “ORA-01109: Database not open” error when it IS open

Running Oracle Database 12c on Windows Server 2012 Whenever I attempt to do a log switch (alter system archive log current), I am given the error in the title. Searching online shows the simple ...
0
votes
2answers
47 views

Oracle Materialized View Overloads Database

Summary: Materialized View across DB link eating up too many system resources Question: Is there a way to throttle a materialized view refresh? We have some fairly large tables that we copy from one ...
0
votes
0answers
22 views

Killed wrong session and cannot connect to oracle anymore

I guess I killed the wrong session. Trying to connect with SQL Developer, it says ORA-12505. Search results say you may set wrong SID, but that's not for my case. How can I get my oracle back? My ...
0
votes
2answers
39 views

IF condition in connect command in SQL*Plus

I want to fire SQL queries only when the user is connected to SQL*Plus. In my case even if the connection is not there, the rest of the commands are running in my SQL script. if "connect ...
0
votes
1answer
18 views

SQL*Net message from client Inactive sessions delete

I am trying to kill a session using alter system kill session . Previously the same command used to return ora28 status. However, these days it stopped giving the status. When V$session table is ...
0
votes
2answers
53 views

Best way to update list of rows in oracle database

I need to update a variable list of database rows. The list of the affected ids will be generated by an application, the statement will be executed about 100 times a day (with an always different ...
0
votes
2answers
59 views

UPDATE with Oracle MERGE statement

I have following MERGE statement. Table table_A contains list of ID (cli_id) and column fb_flag. The query inside update the column fb_flag in table_A when cli_id is matching. Which works. However ...
3
votes
3answers
375 views

How to purge an already dropped table?

I dropped a table but didn't purge it. How can I purge it, so I can release the tablespace that the table uses?
0
votes
1answer
15 views

cannot modify a column which maps to a non key-preserved table

Hello everyone am trying to insert data into base tables via a view that joins more than two tables. I have an instead of trigger that should insert the data into the tables, not sure where have gone ...
0
votes
1answer
40 views

ora-04091 table is mutating trigger/function may not see it

I have two tables say tab1, tab2 here tab1 is the parent table of tab2, Both have triggers enabled say tab1_trg, tab2_trg. Also in tab2_trg there is a SELECT on tab1. Now I try to delete some records ...
0
votes
0answers
27 views

Oracle Listener stoped as soon as it started

I started oracle listener. it seemed working well. but I didn't actually. I hope someone could help me with blow ========================================================================= ...
0
votes
0answers
23 views

If a table is partitioned by a single column should that column be included in index definitions?

Elaborate: CREATE TABLE TEST( school_year DATE, B VARCHAR2(12), C VARCHAR2(15), D NUMBER ) And the table is already partitioned on column school_year. Should column school_year be part of ...
0
votes
1answer
48 views

Cannot fix ERROR:ORA-01031: insufficient privileges

I have tried almost every solution on the internet however I still cannot fix this problem. I cannot login as sysdba every time I try SQL>connect /as sysdba I get the error ERROR:ORA-01031: ...
0
votes
2answers
138 views

Can't login ORA-28031: maximum of 148 enabled roles exceeded

I can't login - and receive the error code above. How do I fix this?
1
vote
3answers
76 views

Whether Cold backup method can be used to “migrate oracle DB”?

I have to migrate one database. I have exact server replica. (Same directory structure on target as well as source database) Previously I have performed DB migration using export/import datapump (As ...
1
vote
1answer
22 views

Whether Cold backup method can be used to “migrate oracle DB”?

I have to migrate one database. I have exact server replica. (Same directory structure on target as well as source database) Previously i have performed DB migration using export/import datapump (As ...
2
votes
1answer
124 views

ORA-12546: TNS:permission denied, on Windows through Task Scheduler

I am trying to export all objects of specific oracle user by executing a batch file through Windows Task Scheduler: exp.exe myuser/pass@dbname owner=myuser log=mylog.txt The Oracle 11.2.0.4 x64 and ...
1
vote
2answers
44 views

Sync data between 2 databases Oracle 10g

I have 2 databases DB_LIVE and DB_NEW, both have different credentials. I have a Data Read access on DB_LIVE and I am owner of DB_NEW which is a completely blank and raw db provided to me. I want to ...
-1
votes
1answer
40 views

Oracle - Service Account vs Database Account?

I'm a developer and not a DBA, specifically not an Oracle DBA. I have a Java application that is connecting to a Oracle 11g R2 database. I have an account and schema called "OBR". I am being told the ...
0
votes
0answers
41 views

Need to find DDLs that were executed in a week in Oracle 11g

Is there any way to find the list of DDLs that were executed in a period of time in a particular schema? It should contain all types of DDLs that were executed on any of the tables of the schema. ...
1
vote
2answers
62 views

Testing on Logical Standby Database

Primary DB: 11.2.0.3 Enterprise Standby DB: 12.1.0.2 Enterprise I've found several articles on making changes on a Physical Standby DB, but I've been having difficulty locating how to enable ...
1
vote
2answers
35 views

Fetching recent/historical queries run on a database from an application

I'm getting some database errors from an application related to unique constraints however none of the records have any duplicate values so I want to check what query was run that produced the error. ...
2
votes
1answer
22 views

Could Not Be Accessed Value Of The For Loop Counter In PL/SQL

I try to learn PL/SQL for loops in tutorials. I learn flow of control in a for loops. (And i saw that it is same with most of the other programming languages) Here is the flow of control in a for ...
0
votes
1answer
42 views

ORA-12154 when PLSQL Developer 11 is trying to connect to database

Hi i have troubles when i'm trying to connect my PLSQL Developer to Oracle 11g R2 Express Edition. I created a new folder in c:\Oraclex86, in that folder I installed the oracle 11g R2 Client for x86 ...
2
votes
2answers
87 views

Does any SQL engine have navigation support in CHECK clauses?

Imagine a simple database to track personal spending. The most basic form of the model should let you create any number of accounts and any number of transactions (specifying the spending amount, date ...
1
vote
0answers
35 views

Oracle Table - game like script - need solution to find out the values from current column

I have a little "game". I have the following Oracle Table (a view more exactly), where I have the values for my columns id, cod, val, t1, tall and I need to calculate v1,v2,v3,v1a,v2a,v3a. ID Cod ...
0
votes
1answer
2 views

Creating pluggable database using dbca

I have created a PDB using Database Configuration Assistant.Is there a way I can look at where the data files got created? I did not get the option in DBCA to specify my data file. Thanks in advance
0
votes
2answers
209 views

Oracle database patch update doubts

Oracle Recommended Patches -- Oracle Database (Doc ID 756671.1) says the following 4 patches are recommended to be applied to 11.2.0.4 database. Note:21744343.8 Combo of 11.2.0.4.5 OJVM PSU and ...
1
vote
2answers
76 views

What reasons can lock an Oracle account

There could be a lot of reasons why an Oracle account can be locked. When an account is locked and the users is asking for our help, following could be the possible cause of account lock: A user ...
0
votes
1answer
35 views

Where does SYSTIMESTAMP come from in multi-server Oracle RAC environment?

Where does SYSTIMESTAMP come from in multi-server Oracle RAC environment? I'm hoping that the SYSTIMESTAMP derives from a common location (master node?) as opposed to the server that is currently ...
0
votes
2answers
69 views

SELECT fields from table having some conditions

I have two tables 1. Named as t1 2. Named as t2 example of the tables are : t1 : id name 1 one 2 two 3 three t2 : id name 2 five I need to select all the data ...
0
votes
0answers
24 views

How to delete alogs fom Oracle downstream target

I try to automate deleting logs from Oracle Downstream target database, which are not needed for capture process anymore. The query: SELECT NAME , PURGEABLE from DBA_REGISTERED_ARCHIVED_LOG; ...
0
votes
1answer
11 views

Oracle generated column aliases different on two DB's

We have a legacy application we cannot modify that connects to Oracle 11g and at some point runs a query and returns a result. The application however is using the "generated" column name from Oracle ...
0
votes
1answer
56 views

Increase sga_max_size on Oracle 10g Windows

i would like to increase the max memory size of one Oracle 10g database installed on Windows. The v$parameter shows sga_max_size 452984832 and sga_target 452984832 Note that the ...
0
votes
1answer
25 views

How to create a Table in Oracle which has a variable of type number?

Suppose I want to store the social security numbers with a prefix letter 'S' (eg. S123456789) or a driving license number D (eg. D12345). Is there any function or method to prefix it? Or patters? I ...
-3
votes
1answer
32 views

Oracle db - get rid of doubled rows

I have an Oracle database with a table like this: yr op v1 v2 v3 2014 op1 10 15 20 2014 op1 10 15 20 2014 op1 10 15 20 2015 op2 33 44 55 2015 op2 ...
0
votes
1answer
25 views

Can I take backup of those tables which are created with NOLOGGING in Oracle?

Can I take backup of those tables with data which were created with NOLOGIING option in Oracle. Is it true that backup can not be taken for these tables and I can not restore my data if I have some ...
1
vote
1answer
23 views

Find out where length semantics gets changed

I've code to create tables that doesn't specify length semantics: CREATE TABLE CHAR_TEST ( CHAR_TEST_ID NUMBER(*,0) NOT NULL, FOO VARCHAR2(6) NOT NULL, BAR VARCHAR2(6) NOT NULL, ...
0
votes
1answer
52 views

mac os - virtualbox - oracle connection error - invalid username/password; logon denied

I have followed the below step guide(URL) to setup Oracle on Mac OS. Everything is fine on the guest side. But, I am facing some issues in host level. I can connect database in Guest (Virtual box) ...
0
votes
1answer
66 views

Extract data from Oracle DBF files

I'm completely new to Oracle and I was provided with what appears to be an Oracle database backup (170 DBF files and 3 CTL files). I'd like to extract the information using SSIS or Pentaho's Kettle ...
0
votes
0answers
49 views

How to document SQL statements (DDL & DML)? [duplicate]

Working on legacy code that was written in part by the software vendor and in part by the client (some for SQL Server 2012 & some for Oracle 10g). The application consumes scripts (file.txt) in ...
0
votes
1answer
56 views

Need a sum on my current column

On an Oracle database, I need to calculate something like this - on column my id yr m v1 v2 v3 st my 1 2015 10 1 0 0 0 0 2 2015 10 0 0 2 1 4 3 2015 10 2 0 0 1 0 4 ...
1
vote
0answers
14 views

Oracle unable to extend index but I have many space free

I have application using Oracle database, and when I'm trying to call my pl/sql function which inserts many rows in tables I get an error: unable to extend index MY_INDEX by 128 in tablespace ...
0
votes
1answer
110 views

ORA-01113: file 1 needs media recovery — system01.dbf

The only way to recover is from media backup? I know I cannot "rebuild" system datafile, but if I don't have a backup - I know shame on me! - Is there anyway to recover database?
0
votes
0answers
28 views

Set a variable outside loop and fetch it every item of loop

My oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi I'm trying to set a variable outside loop and make its value change as the each item of loop. (Just like we ...
2
votes
1answer
1k views

sqldeveloper ORA-12505. TNS: listener does not currently know of SID given in connect descriptor

There are similar questions asked and answered in this network related to the error message given in the question, however this problem is a little different from each. I have installed oracle 12c in ...
0
votes
1answer
41 views

Oracle: i want to setup some variables for login.sql

I use this login.sql for my oracle session select 'You are: '||upper(user) "Welcome to Oracle on AIX 7.1" from dual union all select 'Logged into: ...