Skip to main content
The 2024 Developer Survey results are live! See the results

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

Oracle Sql plan baseline already fixed but v$sql.SQL_PLAN_BASELINE still null

There're two different plan for one specific SQL_ID. Because of the cost, I prefer one of those plan. I'm trying to use plan baseline and fixed the better one but fail. My steps: load plans from ...
chen's user avatar
  • 11
1 vote
0 answers
10 views

Finding harse parse and high cpu time SQL in Oracle Database11g2

I usually check SQL statement by AWR report: SQL Statistics - SQL ordered by Elapsed Time or SQL ordered by CPU Time I need to identify the hard parse sql , maybe having inappropriate plan or other ...
chen's user avatar
  • 11
3 votes
0 answers
26 views

How to check if Oracle Database is using EBR ( EDITION-BASED REDEFINITION) or not

As per Oracle's documentation, since version 11g, Edition Based Redefinition (EBR) is initialized with a default edition named ORA$BASE. Any future EBRs are children of this so-called "edition&...
benbart's user avatar
  • 31
0 votes
0 answers
66 views

ORA-12541: TNS:no listener error when trying to login through WSL to access OracleXE Database

System Details WSL2 Ubuntu Oracle Instant Client (Inside of WSL) OracleXE (Inside of Windows) I followed the instructions guide from the Oracle Documentation, added the path to the TNS_ADMIN, ...
giovanni's user avatar
0 votes
1 answer
69 views

how to get explain plan from pl/sql function call?

consider this working pl/sql block: declare o_result boolean; io_error varchar2(30000); tab1 someTable1; tab2 someTable2; begin tab1(1).col1 := 'str'...
gabriel119435's user avatar
0 votes
0 answers
15 views

How to migrate from Oracle 11g Single Instance residing on a Azure Linux VM to Oracle 19c RAC?

What are the different ways to migrate from Oracle 11g Single Instance residing on a Azure Linux VM to Oracle 19c RAC on Azure VMs?
Sunil Bopanna's user avatar
-1 votes
1 answer
177 views

ORA-01400: cannot insert NULL into

I have two tables: create table country( country_code varchar2(2) PRIMARY KEY, country nvarchar2(57) NOT NULL ); SQL> desc airport_final Name Null? Type -------------------...
Java_Beginner's user avatar
0 votes
1 answer
116 views

Oracle statement inserting duplicate data

I am using this statement to insert records from another table: SQL> insert into city(city_code, country_code, city) select distinct(city_code), country_code, city_name from airport_final; 2 ...
Raakh's user avatar
  • 17
0 votes
2 answers
47 views

inserting data from another table

create user TESTING identified by "Test"; grant connect, resource to TESTING; grant dba to TESTING; connect TESTING/Test; set define off; create table airport_final ( iata_code varchar2(...
Raakh's user avatar
  • 17
-1 votes
1 answer
146 views

Need help with DBMS_COMPRESSION.GET_COMPRESSION_RATIO

I need some help on how to run the DBMS_COMPRESSION.GET_COMPRESSION_RATIO package on all the tables of a database to see if is worth the implementation or not. This is the procedure I'm running alone ...
Daniel Soto's user avatar
-1 votes
2 answers
68 views

SQL breaking my query where there is NULL value

Here is my sql statement and the error I am facing: SQL> INSERT INTO COUNTRY(COUNTRY_CODE) select unique(country_code) from AIRPORTDEMODATA where country_code IS NOT NULL; 2 INSERT INTO COUNTRY(...
Java_Beginner's user avatar
-1 votes
1 answer
74 views

Expecting "," or ")", found "nvarchar2"

Here is my SQL*Loader: Release 11.2.0.2.0 - Production on Fri Nov 10 00:39:56 2023 OS=UBUNTU 20 I am trying to load data from csv file with the help of following code: OPTIONS (SKIP=1) LOAD DATA ...
Java_Beginner's user avatar
0 votes
0 answers
78 views

Discrepancy Between ORACLE_UNQNAME, db_unique_name, and v$database Causing OEM Startup Issues

I am experiencing an issue with Oracle 11g where the ORACLE_UNQNAME, db_unique_name, and the name from v$database are not aligned, causing problems with Oracle Enterprise Manager (OEM) startup and ...
Trevor's user avatar
  • 101
0 votes
1 answer
26 views

Is that possible to count number of specific columns in Oracle

I have a table like this: Is that possible to return the number of rules that pass or fail based on the no? Thanks, T
tnddld's user avatar
  • 3
1 vote
0 answers
53 views

Restore oracle database on a new machine with only datafiles and control files?

I lost my laptop and I had an oracle database 11g r2 in it but I still have all the data files, create control files script and nothing else now I bought new laptop and I want to get my data back with ...
Hussein Sarea's user avatar
0 votes
1 answer
183 views

Update or insert records to the same table based on a similar table but with conditions

I'm tryin to update or insert records on the same table based on a similar table using Merge but based on the count of records in the source table. I have 2 tables say one is POV [pov, dc, item](the ...
Akash Reddy's user avatar
0 votes
1 answer
2k views

ORA-32771: Cannot Add File To Bigfile Tablespace

I have 1TB tablespace that have one bigfile , The disk size now full and added extra disk to my server but there is no way to expand Drive that bigfile in it , so need to add another bigfile ,if ...
FarshiD Naqizadeh's user avatar
0 votes
1 answer
329 views

Why is the SYS user granted both the DBA role and the SYSDBA privilege?

If the SYSDBA privilege gives the grantee ultimate authority, then why does the SYS user also have the DBA role in addition to the SYSDBA privilege?
Mehdi Charife's user avatar
0 votes
1 answer
44 views

Can database administrators access and modify the database before it's opened?

I'm trying to answer the following question (asked in a previous exam and supposedly having one correct answer): To create a DATAFILE and Assign it to a DATABASE, the instance can't be started in ...
Mehdi Charife's user avatar
0 votes
0 answers
121 views

Cross Version EXPDP/IMPDP of Indexes

Our source database is 11g and target is 19c. My question is about exporting and importing indexes. Does the EXPDP command "exports" the actual index structure or just the "Create index&...
oradbanj's user avatar
  • 141
0 votes
0 answers
72 views

Can a user connected as sysdba always bypass auditing mechanisms?

Can a user connected as sysdba always bypass auditing mechanisms? What could be done to minimize (or prevent) auditing prevention?
Mehdi Charife's user avatar
0 votes
2 answers
959 views

expdp fails with error ORA-31626 without any details on a full export

I am exporting a complete database (full=y) on oracle 11gR2 on Linux, and always get this error. There are no other messages, so the problem is not about timeout or permissions as shown elsewhere. ...
eppesuig's user avatar
  • 5,062
1 vote
1 answer
111 views

Patching Oracle Client 11g2 versions on SQL Server

We have a SQL Server 2014 running on a Windows Machine, with Oracle Client 11.2.0.1.0 running. This client is used to Allow SQL server connect to remote Oracle Data sources. This is the directory for ...
Depth of Field's user avatar
0 votes
1 answer
1k views

How To turn a string with "pipe-separated" values into individual rows in Oracle PL/SQL

I have a table with below structure : create table student_info ( item_number number, st_firstname varchar2(50), st_lastname varchar2(50), st_score varchar2(50) ) Here is a ...
Pantea's user avatar
  • 1,432
2 votes
1 answer
1k views

Listener error ORA-12518 & ORA-12520 after changing the IP with domain

We have a server with Oracle 11g and a database. The network administrator added an alias domain to the ip of the server where oracle is. We haven't changed anything else. After that, when SSRS server ...
eathan's user avatar
  • 23
0 votes
1 answer
505 views

Receiving error "Local collection types not allowed" while working with associative arrays

I have an input parameter with this format '0,1,2,3,4' , what I want to do is to declare an array with a variable size (by this I mean the input parameter might be like '0,1' , so the size is not fix),...
Pantea's user avatar
  • 1,432
0 votes
1 answer
113 views

create table based on another table

I have a table called " Product " it has: ID_P, Name, expiration date Now I want to create another table called "Stock" this table has ID, Quantity_of_the_product, ...
kylie's user avatar
  • 39
0 votes
1 answer
3k views

how to create a calculated column

I want to create a table, this table's name is "Prod", in his table, I have the: ID, name, Buying_price and selling_price when the user inserts the data, I want him to enter "name, and ...
kylie's user avatar
  • 39
0 votes
0 answers
186 views

How to restore an oracle database from a manual cold backup on different server?

I have all the contents of the oradata folder and my intuition tells me that on the new server I just need to install oracle binaries of the same version, recreate the directory structure, create a ...
number09128's user avatar
0 votes
1 answer
25 views

Filtering a table by different properties id, value in another table

I have this structure of data TABLE_MAIN ID NAME 1 object 1 2 object 2 TABLE_COMPL_DATA ID ID_MAIN ID_TYPE VALUE 1 1 5 'house' 1 1 7 '2' 1 1 3 '1' 1 2 7 '2' 1 2 3 '1' I want to extract all ...
JuniorGuy's user avatar
0 votes
1 answer
105 views

Oracle 11g Understanding UNDO behaviour

Couple of weeks ago i faced UNDO issue at my database. Now it is always full and system asks to add some space. I have added another 32GB autoextensible file now it is 32+32+32, but in a couple days, ...
0 votes
1 answer
210 views

Restrict access to certain tables for users who have DBA roles?

I have an Oracle Database which includes many users and some of them have DBA roles. The thing is, DBA's shouldn't delete any records from SYS.AUD$ table. Is there a way that i can achieve this?
postgresnewbie's user avatar
1 vote
1 answer
194 views

My subdirectories under $ORACLE_HOME are missing

Im using oracle 11g. I cannot access the software products since they are missing. When I cd to $ORACLE_HOME, there are no files under it. They are missing. I cannot access the sqlplus. What to do in ...
beginner_01's user avatar
0 votes
0 answers
527 views

Procedure updating table with CLOB data type column does not work properly

I have a table with below structure: create table bb_analysisitems_batch ( batch_id number, batch_name varchar2(100), batch_result clob ) There are other ...
Pantea's user avatar
  • 1,432
5 votes
2 answers
633 views

How to create intervals of 100 based on a column in a query?

I have a table with below structure: create table item_test (item_id varchar2(10), item_row number) Sample data is like below , please note that numbers in Item_Row column starts at 1 and ...
Pantea's user avatar
  • 1,432
0 votes
1 answer
36 views

"Unconfigure" oracle database

I have an Oracle 11g RAC installation which I want to "unconfigure". This is a cloned machine which has an Oracle 11g RAC installation. I want to "reset" it (drop databases, ...
user2465039's user avatar
0 votes
0 answers
90 views

Make the SQL statement stop! Stopping supplemental log data never ends

We’re running a single-instance Oracle 11gR2 database for QA and development on Exadata hardware. I was asked to enable database-level minimal supplemental logging. The database has just over 2T of ...
S M's user avatar
  • 196
0 votes
3 answers
13k views

export multiple tables using expdp with query condition

I have requirement to export multiple tables using datapump job and the ultimate goal is that we are doing a migration from on-Prem . They have something like 6TB of AUD data and we are not required ...
sarat's user avatar
  • 60
0 votes
1 answer
30 views

Better ways of concating the value of columns

I have a table with below structure and sample data: create table CUSTOMER_TEST ( customer_num Number, rel_one Number, rel_two Number, rel_three Number, rel_four Number ) ...
Pantea's user avatar
  • 1,432
1 vote
1 answer
146 views

insertion in a table of objects with nested table in oracle

I have a problem inserting in a nested table in oracle These are the relevant types and tables; create type movies_type as Table of ref movie_type; create type actor_type under person_type ( ...
user062's user avatar
  • 13
0 votes
1 answer
2k views

Oracle 11gR2 - Expdp Full Database With Exclude Some Table Data

We will migrate our database using datapump. It will be full database export with exclude some table's datas. But we want to do a different operation for 2 different tables. We want to export the data ...
jrdba123's user avatar
0 votes
1 answer
145 views

Query to update one column based on the amount of another column

I have a table with below structure : create table Z_TEST_ONE ( col_id NUMBER, bed_amnt NUMBER, bes_amnt NUMBER, bop NUMBER ) Sample data is like this : col_id bed_amnt ...
Pantea's user avatar
  • 1,432
0 votes
1 answer
247 views

Oracle: sub select with AS, COALESCE, GROUP BY, HAVING CASE WHEN and ORDER BY

I am typing the below code and it is returning me "ORA-00923: FROM keyword not found where expected". What is happening? SELECT max(alias.id) AS "id", (SELECT ...
Boneman's user avatar
0 votes
2 answers
464 views

Silent Installation of Oracle 11gR2 Fails During NETCA setup in Redhat Linux 8

I am trying to install Oracle 11gR2 in Rhel 8. There was a few incidents of missing packages here and there which I managed to fix during the SOFTWARE ONLY installation. But now I am completely stuck ...
the_ruffler's user avatar
0 votes
1 answer
56 views

what does the number in the "A_ROWS" column of the execution plan shows for operation "Index scan"?

I have a query as you can see below : select /*+gather_plan_statistics*/ * from mi_dimcustomer t where t.CUSTOMER_NUM = 321937 There's a Unique Index (IDX1_DIMCUSTOMER) on Customer_Num column of ...
Pantea's user avatar
  • 1,432
0 votes
0 answers
247 views

Help recovering Oracle Datafile :selecting from or loading data into an oracle 11g r2 ED Datatabase: ORA-00376 & ORA-01110

A user created a datafile with size 200M and when the datafile was created it appeared in the OS as a Zero Kilobytes file. And then the user tried to drop the datafile but after that using query: ...
Obert's user avatar
  • 19
0 votes
1 answer
59 views

which process has start my db?

I have to restart a database, but it seems, it has not been start by the good process: ps -ef | grep smon | grep mydb oracle 54621 1 0 2021 ? 00:10:55 ora_smon_mydb . oraenv ORACLE_SID = [oracle] ...
eliatou's user avatar
  • 316
1 vote
1 answer
656 views

Three "Nested loop join" in the Execution plan despite using two inner joins in the main query

I have a query as you can see below : with cte as (select customer_num from vmi_segment_customer_relation where effective_date = to_date('12/30/2021', 'mm/dd/yyyy') and segment_id = ...
Pantea's user avatar
  • 1,432
0 votes
1 answer
526 views

Getting warnings while generating query Execution plan in Oracle , PLSQL developer

These are the steps I follow to generate a real Execution Plan in PLSQL developer : 1)Using this hint in my main query /+gather_plan_statistics/ select /*+gather_plan_statistics*/ from mi_factcustomer ...
Pantea's user avatar
  • 1,432
-1 votes
1 answer
99 views

Example tablespcae in Oracle 11g

I need to know why example table space is created by Oracle 11g ? Does it effects the performance if it is full ? if yes how can we stop it being used ? Thanks
Waseem Hassan's user avatar

1
2 3 4 5
28