Questions tagged [oracle-11g-r2]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
10 views

Oracle 11g - Database Job About User Lock

I have an Oracle database (Oracle 11gR2) and I want to create a job. The job will trigger my procedure. The procedure must comply with the rules I have specified below: If a user's expiry_date value ...
0
votes
0answers
16 views

The difference between using parallelism “/*+parallel(4)*/” in the subquery or the outer query in terms of performance in Oracle

I have the below query : select /*+parallel(4)*/ ---> First Location <Desc_1> Amount, <Desc_2> Amount, <Desc_3> Amount from (select /*+parallel(4)*/ ---> ...
0
votes
1answer
15 views

Query to select 'Customer_Num' with digit characters using regular expression

I have a table with below structure: Example_table(Customer_Num varchar2(50)) Some Example data in this table : Example_table ---------------------------- Customer_Num ...
1
vote
1answer
33 views

Using SQL*Plus to connect to remote DB from local server, how do I export a backup to my local server?

I am trying to have my entire remote DB backup made to my local server. Running the following command from my local server: expdp \"user/user@ip as SYSDBA\" full=Y directory=/backup/backup/ ...
0
votes
1answer
23 views

How to join two table without getting duplicate from both left & right table in Oracle

Tables Details: CREATE TABLE Test2 ( ID INT, Value INT, other INT); CREATE TABLE Test1 ( ID INT, TYPE INT, other INT); INSERT INTO Test2 VALUES (123456, 5, 12); INSERT INTO Test2 VALUES (123456, 10, ...
0
votes
0answers
17 views

Oracle 11g Rac One Node - How To Change SYSMAN Password

How can I change the SYSMAN password on Oracle 11g RAC One Node without affecting anything else? I am considering following the steps below. Can you help if there is wrong step please. cd <OMS_HOME&...
0
votes
1answer
40 views

How to add a new listener on a new port and restrict it to one instance only

I am running oracle 11.2.3 and we have a server with multiple instances running. All of them are registered to port 1521 with the default listener. Now we are required to make one instance available ...
0
votes
0answers
37 views

“INNER JOIN” vs “INTERSECT” performance comparison when dealing with huge amounts of data in Oracle

I have two tables with the structure below: create table Table_One (Ef_date Date, Customer_Num Number, <Other_Columns> <Combination of Number/varchar2(50)>); create ...
1
vote
2answers
43 views

Merge Datetime Ranges

I have been struggling to merge datetime ranges in oracle SQL/PLSQL (Database Standard Edition 11gR2). I am trying to merge datetime ranges so that the following data order_id start_date_time ...
0
votes
1answer
15 views

RMAN Disk Backups

I have to run RMAN full backups of my 11g Oracle DB to disk. As part of that process the backups will all be changed to have a backup tag of 'UNAVAILABLE'. This is because the filesystem location of ...
0
votes
3answers
41 views

How to allow for ampersand in XMLtable()?

Starting with this Q&A, note that while this works: with data as ( select 'Hallöle sloven&#264;ina Hallöle sloven&#273;ina' str from dual ) select a.str, b.str2 from data a, xmltable( '/...
1
vote
2answers
23 views

Oracle PL-SQL “Regular expression” to replace each “(space)and(space)” with ',' in a string

I have a string like this x=y and g=h and u=1 and I need to replace each (space)and(space) with ,. I'm using the powerful regular_expression for this but it does not give me the desired result. ...
1
vote
1answer
42 views

Are there better alternatives rather than using a “Full outer join” when it is not possible to use a “left or right join”?

I have two tables with below structure : Create table Modern_transaction (Eeffective_date Date, Customer_Num Number, Branch_Code Number, MTrns_type_one Number, ...
1
vote
1answer
18 views

Error “ORA-12726:unmatched bracket in regular expression” while trying to separate a part of a string from other part

I have a string like this : v_sgtring:'sum:[sum(x)>1 and sum(y)>2 and sum(z)>3],where:[n=1 and m=2]' and I need to separate sum:[] from where:[] and assign them to different variables like ...
0
votes
0answers
21 views

How to separate a part of a string from other parts in Oracle PL-SQL using “regular_expression”

I have a string like this : string := ' (sum(x) = 12 and sum(y) = 13 and sum(z) = 14 ) and w = 'p' AND q = 'p' AND ...
0
votes
1answer
12 views

Query with “Logical operator (AND)” that does not return the desired result set

I have a table: create table test_table (customer_num NUMBER, dep_group VARCHAR2(50), dep_type VARCHAR2(50), balance NUMBER) the table has two records: " ...
3
votes
2answers
81 views

How to convert xml entities to unicode characters (with read-only access) in Oracle?

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4), with read-only access. Into this database, some of the data is uploaded via, or rather as, XML - and quite a few entries contain ...
1
vote
1answer
26 views

Create a key-preserved view across Oracle tables with descending indexes

I'm trying to create an updatable view between two Oracle tables which have exactly the same key structure. The existing tables are created by an ERP system and: Do not have primary keys - only a ...
0
votes
1answer
29 views

Error “ORA-0091:Invalid Character” when executing a PL-SQL statement in Oracle [closed]

I'm new to oracle pl-sql and I'm facing error ORA-0091:Invalid character when executing this query below. But when I comment the Execute Immediate statement and use dbms_output.put_line the result of ...
0
votes
1answer
22 views

Changing default permenant USERS tablespace to bigfile

I'm trying to build an oracle database server but it's setting up the users tablespace as a smallfile. How can I either install brand new database with users being bigfile or changing the existing ...
0
votes
1answer
28 views

Pass values to column to create graphs in excel

I'm trying to convert the result of a sentence into columns to create a graph in excel. I have this sql select hss.instance_number INST, to_char(snap.begin_interval_time,'dd/mm/yyyy hh24:mi:ss') as ...
-2
votes
1answer
35 views

Oracle Delete From a View [duplicate]

I need to compile below query: delete from dba_audit_trail where TIMESTAMP > SYSDATE - 30; But it doesn't allow to delete from a view. I HAVE TO delete records from dba_audit_trail, I can't use ...
-1
votes
1answer
65 views

How To Delete Record From dba_audit_trail in Oracle 11gR2

I want to delete records older than one month from AUD$ table, but I have to do this on dba_audit_trail, because I have to do this job according dba_audit_trail's timestamp. I can't find any clear ...
0
votes
1answer
15 views

Filter rows by range dates and only get the rows in a range of hours

I want you to filter the rows between a date range but I only want you to return the rows of a time range of each of the days included in the previous date range. For example, you would like to ...
1
vote
0answers
49 views

How do I configure Oracle DB 11g Enterprise to be able to be accessed from HTTPS PORT?

I'm trying to create a TSA Listener so I could access my Oracle database from HTTPS Port. I already checked edelivery.oracle.com but I found no Web Tier for Windows. Is there any other way for me to ...
0
votes
0answers
22 views

Column Relationship between DBA_AUDIT_TRAIL and DBA_HIST_ACTIVE_SESS_HISTORY

I have been trying to find out which column in DBA_AUDIT_TRAIL links to DBA_HIST_ACTIVE_SESS_HISTORY but haven't succeeded up to this point. My goal is to get the sql statements for each session in ...
0
votes
2answers
72 views

Recover database until cancel?

I'm trying to use the command restore database until cancel, but I'm getting the following erro: RMAN-00571: =========================================================== RMAN-00569: =============== ...
0
votes
2answers
35 views

RMAN incremental restore

Can you anyone tell me if it's possible to restore an 11g Oracle database using media management software like Net Backup or Commvault incrementally? I need to perform a restore of a DB into a new ...
0
votes
1answer
25 views

Can I do a standard restore/recover using image file backups?

We have a database backed up with incrementally updated image file backups. I understand the typical use case would be to switch to the copies, but would a point in time recovery using restore/...
1
vote
1answer
18 views

re size datafile oracle

thanks for reading. I am currently working with an Oracle 11G.I have a problem with a tablespace, I have very little space left, and I need to increase its capacity.I know what to do with this ...
0
votes
0answers
11 views

Can you specify wildcards for transport_tablespaces during export?

Very simple question .. I'm trying to do a Transportable tablespace export in Oracle 11, and have a database with a large list of tablespaces. They have a common name/pattern, however, so wildcarding ...
-2
votes
1answer
27 views

Query inside proc erroring out

I am getting an error while running this query: SELECT index_name, index_description, ltrim(sys_connect_by_path(index_keys,','),',') as Index_Keys, include_cols, index_filter, ...
0
votes
0answers
158 views

Remove/distinct duplicates on listagg - oracle

I'm using the oracle-xe 11g version and I have the below sql code that I aggregation the course code, but the output has duplicates. SELECT b.alu_cpf AS login, b.alu_nomeco ...
0
votes
1answer
65 views

SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')

I upgrade Oracle from 11.1.0.6.0 (32bit version) to 11.2.0.1.0 (64bit version) using RMAN on the same Windows computer (64bit). After RMAN restored and recovered, I then try to run SQL> startup ...
0
votes
0answers
62 views

Oracle DB divide by almost smallest number ever

Oracle DB 11 or 12. Got this code: select 1/0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 from dual; That's ...
1
vote
1answer
150 views

How to manage disk space allocation for materialized views?

Summary: I have materialized views in oracle 11g that seem to hog disk space, unlike normal tables that mark rows as deleted and stats eventually show them as free space (allocated to the table, ...
3
votes
1answer
307 views

Can shrinking temporary TEMP tablespace in Oracle cause any harm?

I have a massive TEMP tablespace that is 1TB large. It consists of 30datafiles, each one is like 31GB in size. Entire TEMP tablespace has a size of 1TB, while used size is only ~600MB. I want to ...
0
votes
1answer
27 views

Oracle restore to a differnet drive

I have the following script for rman to backup the main database and archive logs: connect target sysdba/password@localhost/db run { allocate channel d1 type disk; crosscheck archivelog all; delete ...
0
votes
1answer
66 views

What could possibly cause a numeric overflow when enqueuing an Oracle AQ message?

We create and enqueue messages with various properties, like this: message := sys.aq$_jms_message.construct(0); message.set_string_property('a', v_a)); ...
0
votes
2answers
88 views

CSSCAN for the character set change, database version 11.2.0.3.0

According to Document 745809.1, Installing Csscan. While running the "Csminst.sql" we are getting below error SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql create user csmig identified by csmig ...
0
votes
1answer
154 views

RMAN log file with date and name in Oracle 11gR2 in Linux

I have a script and I want to create my log file with datetime and with unique name. It's my script: ....... ....... rman target/ LOG='/u01/backup/logs/rman.log' <<EOF ....... ....... ....... ...
0
votes
1answer
84 views

Using of delete obsolete in Oracle 11gR2 RMAN on Linux

I'm using crontab for automatic backup (full backup). It works 2 times in a day (8 AM and 23 PM). In my script I use delete obsolete but I have a question. I want to delete old backups without last 2 ...
0
votes
1answer
56 views

Oracle 11gR2 can I delete datafiles to reduce tablespace?

I have the following doubt, maybe a little silly, if I have a tablespace for example TBS_MYDATA of 155 GB, this tablespace is composed of 5 datafiles of 31GB each, in turn, each of these datafiles ...
0
votes
1answer
66 views

How To Create A New bash_profile And Connect New Database To It In Oracle [closed]

OS -> Linux DB -> Oracle 11gR2 Normally my .bash_profile file looks like this: TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_HOSTNAME=abcde.localdomain; export ORACLE_HOSTNAME ...
1
vote
0answers
56 views

Oracle Still Auditing after NOAUDIT

We're redoing what is and isn't being auditing in a database my team has been put in charge of. Old audit settings were producing ~2M audit entries a day. Audit_trail parameter is set to DB. So we ...
1
vote
1answer
49 views

Patching after upgrade (windows 11gr2 -> 12cR2)

I have a following scenario where I had a patched oracle 11gR2 DB (applied Windows Bundle and OJVM patches) and then it was upgrade to Oracle 12cR2. 12cR2 was a clean installation (no patches applied, ...
1
vote
1answer
36 views

Oracle Database 11g Failing to mount on windows server 2012

I have an Oracle 11g database installed on Windows Server 2012. It's failing to mount after shutdown. The reason why i want to mount it is that i want to enable Archive log mode, and i have been able ...
0
votes
0answers
607 views

Export SCHEMAS and TABLES at the same time

My task is export schemas sch1, sch2 Also need to export tablespaces ts88, ts89 from schema sch3 How can I do it as one operation? Because, if I run different operations : DIRECTORY=dir ...
1
vote
1answer
143 views

Help needed to tidy up deprecated (and mal-functioning) SQL code [closed]

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4). I've inherited a chunk of SQL which apparently uses quite a lot of deprecated stuff and, importantly, "sometimes" yields wrong results (...
-1
votes
1answer
35 views

Adding columns for group by result values in oracle

Lets say my table like this and the table name is Employee. Employee ID Name Age 1 Dilshan 22 2 Udara 25 3 ...

1
2 3 4 5
26