Questions tagged [oracle-11g-r2]
Oracle Database 11g Release 2. Please also tag oracle for search purposes.
1,348
questions
0
votes
1
answer
53
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?
- 123
0
votes
1
answer
32
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 ...
- 123
0
votes
0
answers
22
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&...
- 141
0
votes
0
answers
34
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?
- 123
0
votes
2
answers
63
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. ...
- 4,742
0
votes
0
answers
17
views
In Oracle SQL, How do I select an aggregate column from the result of a group by function? [duplicate]
SELECT *
FROM Clients c
JOIN Info i
ON i.id = c.info_id
JOIN (
SELECT
site_owner_id,
SUM(p.amount_paid)
FROM Site s
JOIN SecurityArrangement sa
ON sa.site_id = ...
- 1
1
vote
1
answer
72
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 ...
- 135
0
votes
1
answer
133
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 ...
- 1,402
2
votes
1
answer
127
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 ...
- 21
0
votes
1
answer
114
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),...
- 1,402
0
votes
1
answer
34
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, ...
- 41
0
votes
1
answer
150
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 ...
- 41
0
votes
0
answers
80
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 ...
0
votes
1
answer
22
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 ...
0
votes
0
answers
15
views
To_number() function with rpad() function , how it works?
I saw this piece of code and I have no idea how it works . I was wondering if you could help me figure it out :
column Col_one data type is number and you can see the code below:
select to_number([...
- 1,402
0
votes
1
answer
53
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
83
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?
- 123
0
votes
1
answer
55
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 ...
0
votes
0
answers
334
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 ...
- 1,402
0
votes
0
answers
130
views
query to know list of the queries executed by all users from the last one hour with Username , SQL query and its execution time
I would like to know the list of the queries executed by all users from the last one hour with Username , SQL query and its execution time details.
Please share the query to get those details in ...
- 141
5
votes
2
answers
492
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 ...
- 1,402
0
votes
1
answer
29
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, ...
- 103
0
votes
0
answers
101
views
ORA-01122 ORA-01210 corrupt header
Oracle version: 11gr2
Database has a tablespace with 20 datafiles. While creating 21st datafile on enterprise manager there was an error in creating file and datafile is now showing size zero with ...
- 1
0
votes
0
answers
35
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 ...
- 171
0
votes
2
answers
4k
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 ...
- 60
0
votes
1
answer
23
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
)
...
- 1,402
1
vote
1
answer
49
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
(
...
- 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 ...
- 29
0
votes
1
answer
69
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 ...
- 1,402
0
votes
1
answer
187
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 ...
- 3
0
votes
2
answers
277
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 ...
- 13
0
votes
1
answer
43
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 ...
- 1,402
0
votes
0
answers
167
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: ...
- 19
0
votes
1
answer
37
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] ...
- 314
1
vote
1
answer
422
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 = ...
- 1,402
0
votes
1
answer
263
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
...
- 1,402
-1
votes
1
answer
72
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
0
votes
1
answer
1k
views
Oracle SQL query: Best way to remove unwanted characters?
I want to remove all characters that are neither underscore, hyphen or alpha-numeric. Additionally, I don't want underscore or hyphen as the first character, so that needs to be removed as well.
This ...
- 6,161
0
votes
0
answers
219
views
Oracle 11g to 19c - Upgrade Error
We upgraded our test database from 11gR2 to 19c (19.12) (OS OL 7.9).
After the upgrade, all the components in the dba_registry seem to be in the UPGRADED state, not VALID. At the same time, we ...
- 29
0
votes
2
answers
249
views
Create view with inline function in db<>fiddle
I'm trying to create a view with in inline function in db<>fiddle.
I've removed the CREATE VIEW part for now, and am just trying to run the query, just to get to the root issue.
select /*+ ...
- 1,427
0
votes
0
answers
240
views
See Max Simultaneous Sessions Per User
In an effort to set a reasonable sessions_per_user profile setting for shared database accounts or other non-unique account (ie. an account an application uses to log into the database for all app ...
- 173
0
votes
1
answer
69
views
Query to show whether a record is appeared in the previous date or not
I have a table with below structure and sample data:
create table TEST_ONE
(
purchase_date DATE,
customer_id NUMBER
)
| purchase_date | customer_id
----------------------------------
|...
- 1,402
0
votes
3
answers
473
views
Does using "qb_name()" hint (query block name) in a query have significant effect on performance?
I've just become familiar with the hint "qb_name" (query block name) in Oracle and below you can see an example of using this hint in a query:
select
/*+
qb_name(main)...
- 1,402
2
votes
1
answer
896
views
Tuple comparison with ORACLE and SQLSERVER
I am working on cursor based pagination using multiple columns. I need to make a tuple comparison since date might not be a unique value.
When I am using MySQL and PostgreSQL, I can do it like this.
...
0
votes
2
answers
130
views
How to Escape dot(.) In a substitution variable
I have a variable called ERROR_DETAIL and inside my file file.sql I have this line:
SELECT 'error detail : &ERROR_DETAIL ' FROM DUAL;
Once executed sqlplus complains with the following error ...
- 11
0
votes
1
answer
94
views
Index not used in a partitioned Oracle table
I have a table partitioned by list
create table ICT_ENCOURS_BRUT
(
code_exercice VARCHAR2(4),
CODE_PRODUIT VARCHAR2(2),
.......
)
partition by list (CODE_EXERCICE)
(
...
0
votes
1
answer
115
views
Oracle 11gR2 (11.2.0.4.0) - Drop and Remove Datafiles
We have a database of 2000 users.
We will drop all remaining schemas, leaving only 2 end users, excluding Internal (Oracle maintained) users.
After this operation, there will be a very high reduction ...
- 29
0
votes
2
answers
1k
views
Inserting data into the VIEW , result in data being inserted into the original table, why is this happening?
I have a table as you can see below:
create table table_test
(col_id number(1))
The table has below data:
col_id
------
1
2
3
I create a view based on this table:
create view v_table_test as
...
- 1,402
0
votes
3
answers
575
views
Oracle database won't start, ORA-03113
Running Oracle 11.2.0.4.0 on CentOS and discovered the hard drive filled up, giving error in alert log:
ORA-19815: WARNING: db_recovery_file_dest_size of 64424509440 bytes is 99.98% used, and has ...
- 149
0
votes
1
answer
62
views
When to perform which type of incomplete recovery?
This is purely learning purpose, that which incomplete recovery type is use in which condition? And DBA is bound to choose one or can choose anyone of following Incomplete recovery type?
Scn-based ...
- 93