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
2answers
15 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Ĉina Hallöle slovenđina' str from dual ) select a.str, b.str2 from data a, xmltable( '/...
0
votes
1answer
13 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
0answers
23 views

Problem using the “listagg” function to produce the desired result in Oracle PL-SQL

I'm working with listagg and I have a problem here. It does not give the desired result and I just do not know where the problem is . The deposit_query might be like this : sum (dep_balance) >1 ...
1
vote
1answer
40 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, ...
0
votes
0answers
11 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
18 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 ...
3
votes
2answers
68 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
27 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
34 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
42 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
14 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
19 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
52 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
33 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
133 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
59 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
61 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
116 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
239 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
61 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
86 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
133 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
78 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
62 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
55 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
48 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
35 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
497 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
135 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
vote
1answer
237 views

Managing AUD$ without DBMS_AUDIT_MGMT

I have a SYSTEM's table AUD$ where AUDIT records are stored. My AUDIT_TRAIL is set to DB,EXTENDED. I am on Oracle DB version 12c (12.2.0.1.0). With time (in 3 years) my AUD$ table grew to 500GB in ...
0
votes
2answers
1k views

Error During IMPDP in oracle database 12c

I create Dump file with EXPDP command in oracle database 11gR2 with operating system Oracle Linux 6.7 EXPDP Command: expdp hmis/pass directory=MY_DATA_PUMP_DIR dumpfile=12c_db.dmp logfile=12c_db.log ...
0
votes
0answers
36 views

Search string into dbms_xplan.display_awr output

I want to search into the result of this query. Select cursor(select * from table(dbms_xplan.display_awr(sql_id, plan_hash_value,null,'all +outline +peeked_binds'))) "PLAN" from gv$sql where ...
1
vote
1answer
47 views

Oracle 11g alternative options for oracle 12c with function

I am using (with function) in Oracle 12c. in 11g this is not available. Is there any other way we can implement with function in 11g? with function x(p_NUM in number) return number is n ...
0
votes
0answers
67 views

Update Certificate in Oracle Wallet

We have been downloading data from our vendor for over ten years over the Internet through an encrypted connection enabled by an Oracle Wallet. The self-signed certificate expired, so the vendor sent ...
0
votes
1answer
107 views

Migrate a remote oracle database (11g) into a docker oracle XE database (11g)

I am brand new to Oracle and I am trying to migrate an existing VM that has deployment scripts run on it every release to use a docker container instead. Then I can run the release scripts and tag a ...
2
votes
2answers
340 views

How to “flatten” multiple rows with same ID (Oracle 11g) by concatenating **multiple** fields per row?

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4). I'd like to "flatten"/"merge" all rows with the same ID, pretty much as is well delineated here: https://www.w3resource.com/mysql/...
0
votes
1answer
490 views

How to alter database hour and date in Oracle?

How can I alter the date and hour of the database? I don't want to alter the hour and date from the server, only in database.
0
votes
0answers
28 views

How to refresh the URI type data : insert into temptable values (SYS.URIFACTORY.getURI('URL'));

How to refresh the URI type data and reload to table , whenever the website url got refresh. My complete code: Created table with URI TYPE create table vignesh3(url URIType) loaded data ...
1
vote
0answers
59 views

Problems with Standalone grid : Cannot start database with srvctl, Enterprise manager cannot find listener

First of all, thanks for your time and effort on this, I've been looking into this problem for about two weeks but still haven't figured it out yet. I know it might be due to different root causes, ...

1
2 3 4 5
26