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
24 views

PL-SQL “LAST_VALUE” function does not give the desired result

I have a table with below structure: create table DOC_AMOUNT_DETAIL ( col_id NUMBER, amount NUMBER, operation_date DATE ) and some example data for this table is : col_id ...
0
votes
2answers
20 views

PL-SQL query to find the most recent record the “Operation_date” of which is lower than a specified date

I have a table with below structure: create table DOC_AMOUNT_DETAIL ( col_id NUMBER, amount NUMBER, operation_date DATE ) some example data for this table is : col_id | ...
0
votes
0answers
52 views

Failling to create partition

I have this challenge with creating a partition by a range in 0racle 11g. While I wrote a script top partition my table and scheduled a job to run every month. Unfortunately just discovered that ...
2
votes
2answers
51 views

Import Tables without Constraints from Schema Export

I am trying to do an import using data pump, but I am struggling to come up with the right options. The export was taken as a schema export. I am trying to import just the tables, with no other ...
0
votes
2answers
39 views

we still can't find backup archivelog in local controlfile

Oracle database is Oracle 11.2.0.3 version. Every one hour, archivelog will backup to netbackup tape. But it is strange, even when we use resync catalog command, we still can't find the backed up ...
0
votes
2answers
40 views

Are there better alternatives rathet than joining a table twice for having one record for a specific column in the output?

I have two tables with below structures: create table TABLE_LC ( lc_num NUMBER, brn_code NUMBER, brn_role NUMBER) create table BRN ( brn_code NUMBER, brn_name VARCHAR2(50)) Some example data ...
0
votes
1answer
18 views

How to see the original text of the the function that has been wrapped by “DBMS_DDL.WRAP”

As I'm new to Oracle PL-SQL,I'm trying to be familiar with DBMS_DDL package and I used it to hide my source code,below you can see the example I'm working on: Declare v_string varchar2(32676); begin ...
0
votes
1answer
29 views

How to change the thread of redo logfile group in standby database?

Is there some way to correct the thread of redo logfile group? it's ok the thread to be wrong? executing the following query I get de logfile groups, thread and members: set linesize 2000; set ...
0
votes
0answers
52 views

Compare previous column and current column in ORACLE SQL and do SUM

This is my query to get TIME_DIFFERENT-> TO_CHAR (DATE '1900-01-01' + ABS ( LAG(MODIFIED_DATE,1)OVER(ORDER BY MODIFIED_DATE DESC) - MODIFIED_DATE), 'HH24:MI:SS') AS TIME_DIFFERENT Now, I want to ...
0
votes
1answer
26 views

RMAN restore of primary database - impact on standby database

I've just restored an 11g primary DB that was destroyed. Restored the controlfile from tape and performed a full restore, recovery, and open resetlogs. Looks fine now. How will my physical standby (...
0
votes
1answer
15 views

Oracle 11g RMAN restore without catalog

I need to restore an Oracle 11.2.0.4 database from Netbackup on to a different host. Is it possible to duplicate the DB with RMAN into an Auxiliary DB but without a recovery catalog? If it’s not ...
0
votes
1answer
29 views

What happens if log files storage is full?

I'm currently studying oracle 11g,i had this question in mind but never found the answer even in the docs. What happens if log files storage is full? does the DB stop or block any DML or DDL execution?...
1
vote
1answer
98 views

Encountered Error: ORA-00979: not a GROUP BY expression 00979. 00000 - “not a GROUP BY expression”

I want to join 3 table together and display the output as below.But i encountered the error when i tried to use group by and order by together. Is there any solution can solve this error? Below is my ...
0
votes
1answer
29 views

Seperate value of a specific column with “,” delimiter in Oracle using “Regular Expression”

I have a table with below structure: create table CUSTOMER_INFO ( cust_id NUMBER, cust_name VARCHAR2(50), cust_address VARCHAR2(50) ) Example Data : cust_id | cust_name | ...
0
votes
1answer
40 views

Why is the schema/structure dump so slow?

I'm using Rails 6 with activerecord-oracle_enhanced-adapter adapter for Oracle 11 XE database. At the beginning of the project I was working with Postgres, but due to customer requirements I had to ...
0
votes
1answer
41 views

Using 'Listagg' function and 'Regular Expression' to REVERSE the value of a specific column in Oracle

I have a table with below structure: create table TBL_TEST ( col_id NUMBER, col_name VARCHAR2(500) ) Some example data : col_id | col_name ----------------- 1 | aetnap 2 | ...
0
votes
1answer
32 views

Problem using 'Regular expression' in order to split characters of a column when there is no delimiter between them

I have a table with below structure: create table TBL_TEST ( col_id NUMBER, col_name VARCHAR2(500) ) Some example data : col_id | col_name ----------------- 1 | aetnap 2 | ...
0
votes
1answer
32 views

Oracle 11g migration to AWS

I have an on premise oracle 11g database that i have to migrate to an EC2 instance hosting Oracle 11g in AWS. I have three options to achieve this Use Oracle Data guard to replicate the on premise ...
0
votes
0answers
27 views

Startup Restrict Effect on Standby

If I do a startup restrict on the primary database, does this state effect the standby? In particular, I am interested in two things: Does it do anything to hinder archivelog shipment to the standby,...
0
votes
0answers
5 views

How to appy database security patches in an oracle failsafe environment

We have 4 databases in oracle failsafe cluster. 2 are dc(primary dbs) in failsafe cluster and 2 are dr(standby dbs) in failsafe cluster. Can anyone provide steps to apply patches to these databases.
2
votes
0answers
57 views

Better ways to remove duplicate values of columns when using pl-sql “listagg” function in oracle

I have a table as you can see below : c_id | Name1 | Name2 | Name3 -------------------------------- 1 | a | b | c 1 | c | d | a 2 | d | e | a 2 |...
1
vote
1answer
36 views

How to prevent a column from repeating in the final result when using the LISTAGG function

I have a table like this: CUST_NUMBER | CUST_NAME | CUST_PROFESSION ------------------------------------------ 1 | Mike | Teacher 1 | Mike | Engineer 2 |...
0
votes
0answers
14 views

Oracle - How to find out ADVM file system type?

Is there some built-in command from Oracle to find out ADVM file system type? I was looking for it in advmutil command or acfsutil command. But I don't find anything. In Linux usually I use file -s [...
0
votes
1answer
33 views

RMAN Backups - Can more than one media management software solution be used?

Does anyone know if it's possible for two different backup utilities to be used to backup the same Oracle database? Both utilities would use RMAN. So, for example, take an Oracle 11g standby DB with a ...
0
votes
1answer
22 views

Java application not using Transparent Application failover in Oracle database

I have a customer that have a RAC environment (2 nodes, database version is 11gR2). Clusterware version: [grid@server2 ~]$ crsctl query crs releaseversion Oracle High Availability Services release ...
0
votes
1answer
32 views

Is there a better way of displaying 'Count' of records beside other columns in a select query?

I have a table with below structure : Test1(c_num number ,c_first_name varchar2(50), c_last_name varchar2(50)) 1)There is a normal index on c_num column. 2)The table has nearly 5 million records. I ...
1
vote
2answers
52 views

How to use 'Bind Variable' with 'With clause' in a Dynamic query using PLSQL

I have three tables with these structures: 1) z_test_a(c_num number , <other columns>) 2) z_test_b(c_num number , Amount number , <other columns>) 3) z_test_b(c_num number , Amount ...
0
votes
1answer
28 views

Oracle - Should processes limit be lower than sessions parameter

SQL> select * from v$resource_limit; RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE ------------- ------------------- --------------- ------------------ ------...
0
votes
0answers
11 views

Oracle 11g data guard in windows physical standby

I was wondering if there is a difference in setting up physical standby in windows and in Linux flavors. I don't find any documents or guides in setting up data guard in windows environment. All I ...
0
votes
0answers
8 views

How to create hs oracle to SQLSERVER with rac scan

hi i am trying to create an hs for oracle rac for a course but i am unable to configure the hs How to configure the listener with scan_listener? hs example: odbc windows64 bits system dns: ...
0
votes
2answers
102 views

How to use 'Bind Variables' in a 'Dynamic Query ' when the exact number of variables are not known

I have a procedure in which I'm using Dynamic SQL(variable actual_query) and I'm generating this query based on the input parameter i_tables which is a concatenation of the name of some tables. It has ...
0
votes
1answer
42 views

How to display the exact query in the output section of a stored procedure when using 'Dynamic SQL' and 'Bind variables' in Oracle

I have a Procedure as you can see below. In this procedure I have a dynamic querywhich is qry variable and I'm using Bind variable in order to eliminate the risk of injection and improve performance: ...
0
votes
1answer
26 views

Oracle 11gR2 - How To Audit SYS.USER$ Table

I have a Oracle 11gR2 database and I want to audit all select operations on SYS.USER$ table for which users select on this table. When I try this command (I'm trying to run this command with SYS user):...
2
votes
1answer
48 views

Raise application error in a stored procedure when inputs contain keywords like 'delete' , 'insert','update'

I have a Stored Procedure which gets 6 input queries and the inputs are the same as where clause of a query. For instance where x=1 and z=5 and e=4. The Application makes these input strings and pass ...
0
votes
1answer
44 views

How to prevent a highly dynamic PL-SQL query from injection using bind variables

I have a procedure as you can see below: create or replace procedure Injection_test(qry1 varchar2, qry2 varchar2, ...
0
votes
2answers
64 views

Oracle 12c - Logically There Is A Redo Group But Not Physically - Database Can't Open

I have an Oracle 12c Database for my enterprise manager. I could not connect to enterprise manager because the database was closed. I opened listener. However, the database remained mounted mode. When ...
0
votes
0answers
19 views

AIX Oracle 11g db hangs on startup

ENV: AIX 7.1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bitNo RAC, No PDB. (Pretty plain-vanilla) We recently had an outage of some of our Oracle AIX environments. After the servers ...
0
votes
2answers
110 views

What is the main difference between '[[:space:]]' and '\s' in Oracle pl-sql regular expression?

I have a string like this x=y AND z=y AND p=q , I need to replace every (space)AND(space) pattern with * so that the original string turns into x=y*z=y*p=q. I'm using Regular expression for this ...
0
votes
1answer
15 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
1answer
34 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
17 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
40 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
29 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
1answer
63 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
44 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 ...
2
votes
2answers
47 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
17 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
101 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
31 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
43 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
2 3 4 5
27