New answers tagged oracle
0
The correct format for the connection string is:
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = "HOST name")(PORT = "PORT number")) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = "SERVICE Name") ) )
0
If you deleted the SYSTEM tablespace datafile as you say, and do not have a backup, there is no recovery. You will need to recreate the database.
Side note, you should never delete a datafile directly at the OS level and never the SYSTEM.
0
The number of concurrent sessions is determined by the values of PROCESSES and SESSIONS initialization parameters, provided that your hardware can handle that many of them. You can check the limits and current utilization by querying v$resource_limit:
select * from v$resource_limit where resource_name in ('processes','sessions')
Typically you don't want to ...
1
exec is a PL/SQL block:
SQL> var teste number
SQL> exec select trunc(avg(user_id)) into :teste from dba_users;
PL/SQL procedure successfully completed.
SQL> print :teste
TESTE
----------
447392453
It is the same as:
begin
select trunc(avg(user_id)) into :teste from dba_users;
end;
/
SQL*Plus substitution variable:
SQL> column ...
0
Below worked for me:
Checked services using services.msc
Restarted the services OracleOraDB12Home1TNSListener and OracleServiceORCL
After that connected using SQL developer. It worked!
0
Only deleting lines from tables will not gain the disk space.
You need to reclaim the table space after deleting data.
I would suggest go through the Oracle documents on reclaiming space.
0
I managed to solve the issue of performance as well as making sure that the total amount data is transferred.
I dropped the use of the OLEDB connection in favor of "ADO NET".
I used ".Net Providers\OracleClient Data Provider" for the source and ".Net Providers\SqlClient Data Provider" for the destination.
The ADO NET source and connection provide a ...
0
My suggestion is don't use SSIS. I have found it to be notoriously slow on transfers from Oracle to MSSQL. I was once presented with an SSIS job that was taking in excess of 4 hours to move less than 1 million rows. I did an Oracle 10046 trace on the process and found it was doing row-by-row, slow-by-slow processing over a cursor. I wrote a PL/SQL ...
1
The UPDATE syntax differs a lot from DBMS to DBMS, especially when there are joins. For Oracle this would work:
update crs_regd r
set marks = marks + 5
where marks < 50
and exists
( select 1
from crs_offrd o
where r. crs_cd = o. crs_code
and o. crs_name = 'DBMS'
) ;
0
This query should help you:
WITH t(CountN, mon) AS
(SELECT 1000, 'Jan' FROM dual
UNION ALL SELECT 1200, 'Feb' FROM dual
UNION ALL SELECT 1500, 'Mar' FROM dual
UNION ALL SELECT 3000, 'April' FROM dual)
SELECT CountN, mon,
ROUND(100*RATIO_TO_REPORT(CountN) OVER (), 2)||'%' AS ratio
FROM t;
+-------------------+
|COUNTN|MON |RATIO |
+----...
0
Click the button “Change discovery Path” and put /dev/oracleasm/disks/* and your disks will be there.
1
If you're using sqlplus (I use it for a lot of batch processes) you can just ignore the error:
WHENEVER SQLERROR CONTINUE;
DROP VIEW my_view;
WHENEVER SQLERROR EXIT 1;
After you ignore the possible error from dropping the view, you might want to turn it back to the regular behavior (exit on error). The 1 is optional, in case you want to raise the ...
0
For lost contact, check the hostname configured in Listener.
You can then check which IP is that host name resolving to.
For trial, just modify your hosts file and map localhost, system name and the FQDN to 127.0.0.1.
1
A child table may be exported at some point of time, let's say 12:00. Then as expdp goes on, the parent table may be exported minutes later, let's say at 12:05. Your dump file contains the content of those tables from different points of times.
Yes, if those tables were changing during that 5 minutes, their relation may become inconsistent and you will get ...
0
That is not an unnamed/empty function. That is a column expression made from a constant, between parentheses.
And it is listed as function-based index, because:
CREATE INDEX
When you specify column_expression, you create a function-based index.
The below is a function-based index too by definition, while it is nothing more than the a constant for all ...
3
Imagine the following.
On 2019-08-19 18:00:00 you started a full online database backup that took 5 minutes. This backup included all the datafiles listed above.
Backuppieces created from this operation:
arch1.bkp - backup of archivelogs that existed before starting the full database backup + the last archivelog that was created as a result of the ...
1
It would seem that the control files know that the data file should exist, but the data file might not be a part of your backup. Do you have the log from your most recent backup? Does it show that this data file is a part of the backup? What does your RMAN backup script look like? Are you excluding any table spaces from your RMAN backup script?
0
When you "moved it to the oracle DB" ... from where? Loading a table from an external, delimited file? If so, that file doesn't have the overhead of extent allocation inherent in a database. You need to read up on 'blocks', 'extents', and 'segments' in the Concepts manual.
When oracle inserts a row (1 record from your external file?) it has to have ...
3
An alternate method:
DECLARE
dne_942 EXCEPTION;
PRAGMA EXCEPTION_INIT(dne_942, -942);
BEGIN
EXECUTE IMMEDIATE 'DROP VIEW my_view';
EXCEPTION
WHEN dne_942 THEN
NULL; -- if it doesn't exist, do nothing .. no error, nothing .. ignore.
END;
/
This has the added benefit of not needing to query the data dictionary .. just ...
0
There is no way to do it in a single command, but it can be achieved with a small PL/SQL block as follows:
DECLARE cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO cnt FROM user_views WHERE view_name = 'MY_VIEW';
IF cnt <> 0 THEN
EXECUTE IMMEDIATE 'DROP VIEW my_view';
END IF;
END;
/
0
Restarting the front end server have fixed the issue and it is working now.
0
Run the below scrip, It will release unused space from the datafiles of the respective tablespace.
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
set pages 0
set lines 300
column cmd format a300 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)...
0
No, tnsping is not sufficient, tnsping is inadequate for this task. tnsping checks the host and port specified, and if there is a listener listening there that responds, it reports OK. It does not even check the presence of the service/SID specified, so no, it is just not sufficient for checking database (and not even service/SID) availability.
If you want ...
0
For standalone database to get the list of databases which are started automatically after host rebooting:
cat /etc/oratab | grep -i ":y" | grep -v "^#"
or just to get the list of all databases:
cat /etc/oratab | grep -v "^#"
For RAC databases following method can be useful:
crsctl stat res -t | grep "\.db"
Also as it was already mentioned the database ...
1
Normally it is the ASM_DISKSTRING parameter.
But the above screenshot shows that you use ASMLib, which uses a fix format for disks, starting with ORCL:.
Whenever you label a disk for ASMLib with oracleasm createdisk, that disk becomes available with the name starting with ORCL:.
So if you set ASM_DISKSTRING to ORCL:*, the ASM instance can find these disks....
1
Take a look at the ASM_DISKSTRING initiallization parameter for your ASM instance.
ASM_DISKSTRING
0
The point of WINDOW NOSORT STOPKEY operation is to stop the child operation after reaching the required amount of rows (without sorting). A COUNT STOPKEY operation is similar to that.
So no, the database does not read the whole table, and the above plan does not mean that.
Create a table with high number of rows:
SQL> create table t1 as select * from ...
0
I am trying to determine if data corruption/lost is possible when moving large data from one table to another in the same schema.
Whilst it is possible for this to occur, it is very, very unlikely.
Insert should store exactly the same data as the select feeds to it.
That data is initially written to the redo log - any corruption at that point should ...
0
This is based on Balazs Papp's answer and help:
select "ID", max("Relevant") as "Relevant", "LongDescription",
max("A") as "A",
max("B") as "B",
max("C") as "C",
listagg("Comment", ' // ') within group (order by null) as "Comment",
listagg("FurtherDetails", ' // ') within group (order by null) as "FD",
count(*) as "RowCount"
from dummyTable2
group by "ID", ...
1
SELECT
T1.FIELD1,
T2.FIELD2,
...
NVL(T2.COMMENTS, 0) AS COMMENTS
FROM T1
LEFT OUTER JOIN
(SELECT FOREIGN_KEY, COUNT (FIELD1) AS COMMENTS FROM OUTER_TABLE_NAME GROUP BY FOREIGN_KEY) T2
ON T2.FOREIGN_KEY = T1.PRIMARY_KEY
;
2
select "ID", max("Relevant") as "Relevant", "LongDescription",
max("A") as "A",
max("B") as "B",
max("C") as "C",
listagg("Comment", ' // ') within group (order by null) as "Comment"
from dummyTable
group by "ID", "LongDescription"
order by "ID";
http://www.sqlfiddle.com/#!4/af6db4/15
2
Because in the same database you have multiple applications, multiple workloads,... and services help to:
- identify sessions when troubleshooting performance issue
- manage resources for those with higher or lower priority
- balance between several nodes
- offload some read-only workloads to a standby database
...
0
Since Template must be related to a Region, you need a foreign key from Template.region_id to Region.id
Template.region_id will be NOT NULLable.
Template.country_id will be NULLable.
Make a unique constraint on the composite key (Country.id, Country.region_id)
Then you can create a foreign key from (Template.country_id , Template.region_id) to (Country....
1
It is because no privilege on tablespace. Grant tablespace quota to the user to fix this:
SQL> alter user quota unlimited on tablespace_name;
SQL> GRANT UNLIMITED TABLESPACE TO username;
Detailed on ora-01950
0
You can simply get the desired result using LEFT OUTER JOIN as following:
SELECT DISTINCT
*
FROM
(
SELECT
A.COLUMN_A,
CASE
WHEN B.COLUMN_A IS NOT NULL THEN 'YES'
ELSE 'NO'
END AS "EXISTS IN TABLE_B"
FROM
TABLE_A A
LEFT JOIN TABLE_B B ON ( A....
0
From this answer which I reworked a little and came up with (depending on the OP's exact requirements which aren't entirely clear) the following answer (fiddle available here).
The data is the same (with the addition of a few records). First query run was this:
SELECT
UNIQUE columnA AS col_a,
COUNT(columnA) AS count_a,
'A' AS tab
FROM A
GROUP BY ...
0
ALTER DATABASE
set_time_zone_clause::=
set_time_zone_clause
This clause has the same semantics in CREATE DATABASE and ALTER
DATABASE statements. When used in with ALTER DATABASE, this clause
resets the time zone of the database. To determine the time zone of
the database, query the built-in function DBTIMEZONE. After setting or
changing the ...
0
Maybe this will help: use analytics to count (the duplicate) values in each table, and use the results of these operations in a full outer join. Example (see dbfiddle):
Tables
create table A ( columnA )
as
select 'a' from dual union all
select 'b' from dual union all
select 'b' from dual union all
select 'c' from dual union all
select 'c' from dual union ...
-1
IF that is actually your script, then after you connect "/ as sysdba", then sqlplus is simply waiting for input. It doesn't see the 'alter session', etc, because those aren't being redirected into sqlplus. Rather, they are simply treated as the next command for the shell to process after sqlplus exits.
Try this:
sqlplus / as sysdba <<EOF
alter ...
0
One point to be wary of: tables (also indexes, materialised views ..) are allocated more space as you INSERT data into those tables. When you DELETE, that space is only freed inside those allocations. Meaning that the space will be reused when you insert new rows into that same table. But it will not be reused by other objects (other tables for example).
...
-1
The various data files that comprise the physical storage of the Oracle database instance can vary depending on the operating system, endian format, and pretty sure some of the files may have a hostname hiding in them somewhere. And if those files were obtained from a database instance that was running, there is no way those files could be used to recreate ...
1
I'm running Oracle 11g Personal edition docker container
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d84af138d77a oracleinanutshell/oracle-xe-11g:latest "/bin/sh -c '/usr/sb…" ...
0
So in full, you could have something like this:
SELECT NVL(#a.account_number,#PS.ACCOUNT_CODE__C) account_number
, NVL(#a.id,#ps.I) id
, DECODE(#ps.id, NULL, 'N', 'Y') table_PS
, DECODE(#a.id, NULL, 'N', 'Y') table_a
FROM #A
FULL OUTER JOIN #PS ON #A.ACCOUNT_NUMBER = #PS.ACCOUNT_CODE__C
AND ( (#A.ID = #PS.I AND INSTR(...
1
Joining (or the expression in SELECT) looks similar to:
FROM #A
FULL OUTER JOIN #PS ON #A.ACCOUNT_NUMBER = #PS.ACCOUNT_CODE__C
AND ( (#A.ID = #PS.I AND INSTR(#A.Id_Type, 'I') > 0)
OR (#A.ID = #PS.T AND INSTR(#A.Id_Type, 'T') > 0)
OR (#A.ID = #PS.C AND INSTR(#A.Id_Type, 'C') > ...
0
You can not directly use the alias in ON clause or in WHERE condition
So simply use your expression in ON clause:
SELECT t.indicator,
regexp_replace(t.indicator,'[^0-9]') as numbers
from temp t
Left join temp2 t2
on (regexp_replace(t.indicator,'[^0-9]') = t2.<Join column name>);
Cheers!!
1
In Oracle you can use the TRANSLATE function
select TRANSLATE(Indicator, '0123456789'||Indicator,'0123456789')
from Table1
Digits are translated to digits, other characters of the column are removed.
From Oracle SQL Language Reference
TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding ...
0
Thanks for your help. Found i could do this way.
SELECT
indicator,
regexp_replace(indicator,'[^0-9]') as Numbers,
regexp_replace(indicator, '[^a-z and ^A-Z]') as Characters
from temp
0
I am not sure I understood your requires completely, but anyway.
In Postgres I would probably do something like this:
with per_month as (
select to_char(a1.created_at, 'yyyy-mm') as month,
array_agg(distinct a1.user_id) as active_users
from user_activity a1
where a1.created_at >= timestamp '2018-12-01'
group by month
), m2 as (
...
2
However I am presented with the following error
"SUBSTRING": invalid identifier"
The provided query should be able to run, here is a test on SQL Server 2017
Unless your tag is wrong and you are using oracle?
Then you would get an error like this:
ORA-00904: "SUBSTRING": invalid identifier
DB<>Fiddle
If that is the case, then you could use ...
0
The best solution would be to ditch the RR (or RRRR) notation completely. It was meant as a short-term, kick the can down the road, fix to buy some time remediating for Y2k. That was TWENTY YEARS ago. Long, long past time to wean ourselves it and simply start using 4-digit years in everything we do.
Top 50 recent answers are included
