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
0answers
23 views

How to use an external encryption key in an Oracle function?

I am storing People data in an Oracle 11g database. I am implementing symmetric encryption on one sensitive data column in my People table. For this, I created an Encrypt function based on the example ...
0
votes
1answer
46 views

Does Oracle randomly switch execution plans to check if there is a better solution?

I had a talk with an external colleague about oracle "swinging in" its performance. In detail it was about, that oracle optimizes its execution plans for the same query by time. I don't talk about ...
2
votes
2answers
103 views

RMAN DUPLICATE from backup without Target or Catalog: How is the aux instance created?

I am trying to duplicate an Oracle database based on backups to test if they can be used in case of complete system failure. Hence, I want to do this without a connection to the source database. I'm ...
0
votes
0answers
45 views

Using a trigger to check whether a matching record exists

I have a database model set up as in the picture above, modelling a student's enrolment into a unit with an associative entity to facilitate the m:n relationship. I wish to code a trigger that will ...
3
votes
2answers
65 views

Golden Gate realtime replication basic question

I have a task to replicate unidirectional real time data from source database A to target database B. I do not need all the table from A, only few tables and few columns inside those tables. Say 10 ...
0
votes
2answers
31 views

Can multiple Oracle databases share a single parameter file?

I'm a developer working on an application team that supports a system with one production database and 4 test databases (2 Dev, a SIT and UAT environment). The prod database exists on it's own Unix ...
1
vote
1answer
50 views

Oracle index with REGEXP_REPLACE is it a good idea?

I have a table of contacts with 1 million registers and now the user wants to search in it by the telephone. Trying to avoid a fullscan I decided to create a new index in this column, but the trick ...
0
votes
3answers
132 views

Oracle union default sort is weird [closed]

As you know union removes duplicated rows. I thought it removes from the latter ones, but it doesn't. I found out that if there's no order by clause, Oracle sorts the merged dataset by the first ...
1
vote
2answers
61 views

How can I stop Oracle Shutdown command?

I used Shutdown normal, instead of Shutdown immediate. Now I want to stop the process, because its taking too long. How can I do it?
4
votes
2answers
43 views

How to assign a constant to a value on all rows, if that value is adjacent to some value on any row

I have data like this (STATUS, and NAME are the column headers): STATUS NAME ACTIVE A1 INACTIVE A1 INACTIVE B1 INACTIVE B1 I want to include a CUSTOM field that returns a ...
1
vote
3answers
131 views

Avoid repeated traversing of hierarchical data when parsing a big tree frequently

I have a hierarchical/tree data structure in Oracle/RDBMS where I need to traverse the tree frequently. The tree is huge and therefore parsing it every time will not be efficient. A better way could ...
3
votes
1answer
99 views

Oracle 12c Rman Duplicate Archive Log Not Verified

I am trying to duplicate a DB from live to dev but it is failing. Can anybody advise what is going wrong? Both databases are running Oracle 12c. All database files seem to be copied over but it fails ...
2
votes
1answer
94 views

CRON and RMAN Backups

We run RMAN backups via CRON jobs, but find the out-of-the-box emails with CRON to almost do what we want. I know we can channel the RMAN output to /dev/null so no email is sent, which for any ...
0
votes
1answer
42 views

Syntax help in SQLPLUS

Does Oracle have any builtin SQL syntax help. I find I may have forgotten a few statements and sometimes have no Internet access. I am using Oracle 11g.
0
votes
1answer
33 views

How to remove datafile from oracle database which is already remove locally?

Created one dbf file from my database using following command, alter tablesapce PAULA_INDEX add datafile '/u02/oradata/hfd1/index/index17.dbf' size 2000M; This dbf file is created in my server ...
1
vote
1answer
70 views

How big is my oracle database?

I need to determine the total size of an oracle database. Ideas on improving the following query? SELECT 'DataFiles: ' AS TYPE, SUM (bytes / 1024 / 1024) AS MB FROM dba_data_files UNION SELECT '...
2
votes
2answers
52 views

why I can't set oracle sessions value = 35?

I want to simulate the error "ORA-00018.Maximum number of sessions exceed",so I want to limit sessions = 35 and I am going to open serverl sqlplus to simulate this error,but sessions number didnt ...
0
votes
1answer
48 views

Oracle date not returning the right data

I don't know whats going on here, but here is my table (budget) with select * from budget : |budget_id|as_of_date|quarter_1|quarter_2|quarter_3|quarter_4| |1 |15-MAY-16 |400 |100 |...
1
vote
1answer
25 views

Ranking based off conditional field

I am looking to select only 1 row based off the IMMS_ID field. Which row I select is based off this if statement (excuse the syntax) if (status group based off IMMS_ID) status='DI' select the DI row ...
0
votes
1answer
82 views

How to do data migration between different schemas?

My company is in the process of migrating a customer's data from their third party system into our own system. We currently use a java application that we have developed in house that selects data ...
1
vote
1answer
34 views

ORACLE: Changing the grouping of a data to be based on month breakdowns

To keep this short and to the point, I am looking at modifying the grouping for this SQL query to go from NAME to Month/yy Here is a example of the script, and screenshots of the output file This is ...
0
votes
3answers
90 views

Get current time, on commit, with oracle database

When inserting a row I want to make a column, equal to the current time on commit. If I use default value sysdatetime on the column, it uses the current time at insert (which could be significantly ...
1
vote
0answers
48 views

How to recover LogMiner after its tablespace had been deleted?

Here is my story: I created separate table space for Log Miner, create tablespace logminer_ts datafile '/path/to/logminer.dbf' size 50M AUTOEXTEND ON And moved Log Miner to the created table ...
0
votes
2answers
57 views

Oracle ASM : Alter asm_diskstring when ASM Instance is Down,

I am beginner for Oracle ASM I know we can alter asm_diskstring with following ways SqlPlus ASMCA but these requires +ASM to be UP and running. Is there way to set asm_diskstring when +ASM ...
0
votes
2answers
55 views

Is is posible to sample a database

Is it possible to sample any SQL database? By sampling I means the database table should remain and retaining the foreign key constraints. But the actual rows from the table should be reduced.If I ...
1
vote
1answer
34 views

Reset schema oracle

I'm running systemtest that updates a oracle schema. I need to reset the whole scheme between runs. Is there an easy way to do this in oracle 12. I have looked at flashback but it doesn't seem to be ...
1
vote
1answer
86 views

Connecting Oracle 11g to Postgres via dblink

I have administrator access to our Oracle database , but was wondering if its possible to connect an Oracle 11g database to Postgres database and if so, what steps would I need to follow. I've seen ...
0
votes
2answers
85 views

INSERT OR REPLACE INTO + SELECT as source in Oracle

I have two tables A and B. I want to insert B's content into table A. The issue is there's a row in B that already exists in A. I've searched and I found some answers talking about MERGE, but no ...
1
vote
2answers
107 views

Need advice on database normalization

I am working on a basic blood bank database on Oracle. I would like to create a completely basic normalized table and then I will add more details to it. I have done it to the 2NF and I will further ...
0
votes
0answers
41 views

jdbc Datasource error BEA-001131 Closed Connection

I have Installed weblogic 10.3.5. On database side I have RAC environment with Two instance. I have created GRIDLINK DataSource with XA DRIVERS. NAME: test JNDI: jdbc/testDS NOTE: I ...
1
vote
0answers
66 views

Getting email notification when jobs are created under user_schema rather than sys?

I log in as sys and set the smtp for scheduler: BEGIN DBMS_SCHEDULER.set_scheduler_attribute('email_server', '10.1.1.15'); DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'user@compny.com')...
1
vote
1answer
52 views

Copy a single record to a replica table before update using trigger (Oracle 12c)

I want to insert a complete record (before updating original record) to a replica table using trigger. Following are the sample tables: create table customer ( cID number, cName varchar2(30)...
0
votes
1answer
37 views

Configure Oracle EM to Send Emails

We are using Oracle 11gr2 installed on Linux. Tried to use Enterprise Manager to send email alerts in case of tablespace is near to full. Alerts are generated on EM but no emails are received when ...
6
votes
2answers
191 views

ANALYZE TABLE..VALIDATE STRUCTURE runs forever

So, as I've mentioned before, I am a SQL Server DBA/Consultant who is helping out a customer with their Oracle databases. Mostly, I've been able to figure out what I need to and work through things ...
0
votes
2answers
84 views

oracle rman connecting to a database in not mount state

When connected to oracle database through rman directly from the terminal, everything works fine, but when the same connection string is executed through a shell script rman connects to the database ...
1
vote
2answers
101 views

Find percentage of NULL storage in Oracle table

I have a table in Oracle 11g Standard One Edition: table1 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 1 NULL 2 3 4 5 NULL NULL 19 21 22 1 NULL 2 3 ...
1
vote
0answers
103 views

Oracle Forms 11g R2 connect to Oracle 11g

Thank in advance! I have 64bit Windows 7 with installed Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit. I installed Oracle WebLogic 10.3.6 how it described here. But it's install ...
0
votes
1answer
96 views

ORA-02049 Timeout: Distributed Transaction Waiting for Lock Solution needed

We are cleaning up the last year's transactions from our primary database. We use public database link to delete the records a day by day from another database. Each days records are around 30-40k, ...
2
votes
1answer
34 views

Return only one row from table with similar values in column that match a criteria

I want to run a query from a table with similar values in the same column that match a certain criteria, but only return 1 row per similar value. Let's say I have this simplified table: Column1 ...
2
votes
1answer
33 views

SQLPLUS Default Column Widths

For almost all my databases, when I run a SELECT statement, the length of the column displayed is sized to the width of the data being queried. However, I have two databases (Dev and Prod for the ...
1
vote
2answers
40 views

Oracle DATE column - How to convert to UTC

I have an oracle 10g table with a DATE column type. I need to compare it in a where clause to a value generated by a script that looks like this: TIMESTAMP '2016-05-12 08:00:00.258000 +00:00'. I ...
0
votes
1answer
22 views

not a single-group group function when trying to add two sum() function [closed]

I have two tables: create table a ( index_ numeric(1), value_ numeric(2) ); create table b ( index_ numeric(1), value_ numeric(2) ); Now I want to get the number of these two tables sum,I ...
0
votes
1answer
16 views

Oracle drop database - Can images stored in RMAN catalog be retained?

It seems the default behavior when an Oracle database is dropped / deleted (entire server retired) that any RMAN entries for that system are also dropped. Is it possible for RMAN to retain records ...
1
vote
1answer
104 views

Oracle Triggers - before any UPDATE or DELETE or INSERT

I am trying to create a database trigger in Oracle SQL Developer that would fire before any UPDATE, DELETE, or INSERT statement occurs on the sessions table. My sessions table: CREATE TABLE sessions ...
4
votes
2answers
112 views

Query “last” records in a table with non-sequential key fields

My goal is to clone a table from an Oracle database to a SQL Server database every midnight. I am using Talend Data Integration for this task. The problem is that this table has around 70 million ...
-1
votes
1answer
30 views

Generate and fetch table statistics [closed]

I am trying to generate statistics for my tables. I am doing it with the following command: exec dbms_stats.gather_table_stats(USER, 'tab_name', cascade => TRUE); Then I am trying to fetch these ...
2
votes
1answer
54 views

Getting ORA-01017: invalid username/password in non-CDB database

I have installed Oracle 12c (software only in Linux) and created a non-CDB database (single tenant) database. Following is my listener.ora. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (...
1
vote
2answers
83 views

How to get the DDL for Oracle object without extra characters

When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script. For example, if I create this sample ...
0
votes
1answer
69 views

Oracle log miner permission: which permission authorize user to use logMiner as scheduled JOB?

I'm trying to config user to run logMiner from scheduled job periodly. my user called admin I've grant the admin user the following: grant select on v_$database to admin grant execute on ...
0
votes
2answers
103 views

Query against history table not using index (Oracle 12c)

I have a history table which has an 'ID' and 'TIMESTAMP' column as such CREATE TABLE hist ( HIST_ID INTEGER, HIST_TIMESTAMP TIMESTAMP, ID INTEGER, -- this is the id of the table that is being ...