New answers tagged oracle
0
... steady data income of 400 MB/hr ... 10 redo log groups with a size 100MB each ... roll over a log approx. every 15min.
... data removal process ... redo log interval shortens to 15s ...
You've sized your system to cope with the 400MB of new stuff arriving every hour. That's a good start.
However, your redo logs need to cope with everything that ...
0
Problem #1: Trying to stored objects into a tablespace on which your have no quota.
While you created a tablespace for your account, you did not make that the default tablespace for your user so, when creating objects without an explicit tablespace clause, it would have tried to put them into a [wrong] tablespace on which the account had no quota. Boom!
...
0
Oracle Dataguard does not transfer transactions, it transfers log records. Physical standby knows nothing about transactions (logical things), it knows only about changes to the files (physical changes).
From the standby side, you can check v$dataguard_stats view. It gives information for "transport lag" and "apply lag" in terms of time.
From primary and ...
0
Add the following to the impdp command:
VERSION=11.2.0
0
The problem was this part:
SQL> exec dbms_xdb.sethttpport(5500)
PL/SQL procedure successfully completed.
I configured 5500 on HTTP port but was trying to access it on HTTPS
1
The short name of your host is missing from /etc/hosts, only the FQDN is there.
It should be:
163.173.24.179 linux.mydomain.com linux
linux (hostname -s) is unreachable due to this.
0
Came here looking for ORA-48128 in Oracle 19.3
As a follow-up to @"Balazs Papp" answer above, you can get the same error message for a mounted directory (hard drive hard mounted to C:\mnt\Datapump instead of a letter), and the
ALTER SYSTEM SET "_disable_directory_link_check" = TRUE SCOPE=SPFILE;
works.
It is almost certainly a bug in Oracle.
0
Create a full backup, then restore it to new server and update the dataguard configuration. Problem is, SCN in Standby database maybe
different than New Primary database due to old Primary database is
still running. If this happen, can i apply log from old Primary to New
Primary? I'm not sure..
You can have multiple standby databases for a primary ...
1
That will not happen. You can kill a session, or cancel the SQL it is currently executing from another session, but you can not end its transaction (with leaving the session alive). Usually such blocking sessions are just simply killed.
For your other question: blocking another transaction in another session that inserts to another table may be blocked ...
0
(work-in-progress)
The following regex works, but I still need to a fix for the replacement string (&) which currently still prompts for a "substitution variable":
with data as (
select 'Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
passing ...
0
The error message clearly told you to use &.
with data as (
select 'Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
passing xmltype( '<dat>' || a.str || '</dat>' )
columns
str2 varchar2(4000) path '/dat'
) b
;
STR
-----------...
0
It is not possible to connect to another session in that way. The original client needs to submit the commit, or kill the session and allow the transaction to rollback.
2
Clients connect to the scan listener(s) listening on the scan vip(s). The scan listener forwards the request to the appropriate local listener listening on the node vip. So the node vip is still used for connecting.
The listener must run on the node you end up connecting to spawn your server process. The scan listener running on rac1 can not spawn a process ...
1
select regexp_replace('x=y and g=h and u=1','([[:space:]]*)and([[:space:]]*)', ',')
from dual;
REGEXP_REPL
-----------
x=y,g=h,u=1
1
create table data
(
first_name varchar2(20 char),
last_name varchar2(20 char),
sequence_number number(10, 0)
);
insert into data values ('John', 'Smith', null);
insert into data values ('John', 'Smith', null);
insert into data values ('Jane', 'Doe', null);
insert into data values ('Jane', 'Doe', null);
insert into data values ('Jane', 'Doe', null);
...
0
I prefer Balaszs' solution which is more direct and more simple, but it is rather easy to adapt your solution (I copied the with clause from Balaszs).
Your "total" is not wrong because it includes the "eth05" column. This column has no influence on the result set of the query. "total" is the count of all rows in a group, so the number of devices of a ...
0
Try this:
/*
WITH T (LOCATORCODE, COUNTRYCODE, SERVICEENDDATE) AS
(
VALUES
('003', '067', '12/31/9999')
, ('003', '082', '07/31/2013')
, ('011', '017', '05/31/2020')
, ('011', '034', '05/31/2020')
)
*/
SELECT *
FROM
(
SELECT T.*, MAX(CASE SERVICEENDDATE WHEN '12/31/9999' THEN 1 ELSE 0 END) OVER (PARTITION BY LOCATORCODE) IS_VALID
FROM T
)
WHERE IS_VALID =...
1
with t as
(
select 'Netgear' as device, null as eth01, 'data' as eth02, null as eth03, null as eth04, 'data' as eth05 from dual union all
select 'Netgear' as device, null as eth01, 'data' as eth02, 'data' as eth03, null as eth04, 'data' as eth05 from dual union all
select 'Netgear' as device, null as eth01, 'data' as eth02, null as eth03, null as ...
1
If your version of Oracle is 12 or above, you can use FETCH FIRST n ROWS ONLY (doc link):
SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF
FROM PRODUCT p
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001'
AND m.MANF_ID = (SELECT MANF_ID FROM MANUFACTURER man WHERE man.PRODUCT_ID = p.PRODUCT_ID ...
0
SELECT locatorCode
FROM source_table
GROUP BY locatorCode
HAVING NOT SUM(serviceEndDate = '9999-12-31')
0
Yes, I would really recommend using WSL or rather WSL 2 for Docker now.
But there is another (easier?) way to build you Oracle 18c Docker image with the official Dockerfiles (you might even use this way from inside WSL/ WSL2 if you see a need for this).
Do note that the (Linux/Bash) script buildDockerImage.sh is just a helper (checking the checksum and so ...
1
Of course it is listed/documented.
Format Model Modifiers
FM
Fill mode. Oracle uses trailing blank characters and leading zeroes to
fill format elements to a constant width. The width is equal to the
display width of the largest element for the relevant format model:
Numeric elements are padded with leading zeros to the width of the
...
2
The issue appears to be the "ON COMMIT" portion of your DDL. From the documentation:
"To create a refresh-on-commit materialized view (REFRESH ON COMMIT clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege."
So ...
0
First up, you should only change underscore parameters when instructed to do so by support. But more importantly for these parameters, Real-Time Statistics is only available on specific database offerings. See the license guide for details.
Changing these parameters is likely to be a violation of your license agreement!
LOAD TABLE CONVENTIONAL and ...
1
The data looks like it is raw XML data without XML Tags, not the translated text. You should probably get that fixed first.
In the meantime, you can run the data through XMLTable() to do the translation for you.
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
One way "on foot", via a nested replace():
SELECT replace(replace(replace(col,'Ĉ';'Ĉ'),'đ','đ'),' ',' ')
This might be my best shot, if a looped regex won't work under the given restrictions... No?
(Obviously the above would still need to be expanded for many more characters...)
0
Make sure that the user installing the software is in the local ora_dba group, and that os authentication is enabled in sqlnet.ora:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
Also, it is a good idea to make sure that any anti-virus software doing active scanning is disabled during install/db create activity, and that when running it contains exceptions for all ...
0
As time goes on, this answer changes.
Current answer should involve a move to a Multi-Tenant setup. The latest version (19c, soon 20c) allows 3 Plugable Databases (PDB) without a license.
Licensing Information for 19c:
For all offerings, if you are not licensed for Oracle Multitenant, then you may have up to 3 user-created PDBs in a given container ...
1
Case sensitive on Linux/UNIX and not case sensitive on Windows.
1
RMAN: Creating and restoring backups on both disk and tape (Doc ID 1397703.1)
I think one of the reason why backup to disk and tape cannot be simultaneously directly through RMAN, main reason would be the speed for example RMAN is creating a backup piece on disk with speed of 200 MB/s but to copy the same backup piece over network to tape would be 100 MB/s....
0
Have you thought about either using the first 8 characters or up until the first dash as a basis for partitioning? You should start by looking at how many unique values you would get when you partition by either the first 8 characters or the first dash. I don't want to assume that the first dash falls on the 9 character. Then you can try to partition with ...
0
The command should disable all instances by default, without having to specify SID. From the documentation you can see that security clauses like this don't even have an option for specifying a specific instance.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-SYSTEM.html#GUID-2C638517-D73A-41CA-9D8E-A62D1A0B7ADB
Also see here for ...
0
... Oracle will be forced to read the full table in one query if the column isn't somehow optimized.
The word you're searching for is "indexed".
... this column is a primary key.
If this column is the Primary Key of the table, then it is indexed.
As such, queries for a individual values will be fast:
select ...
from table1
where id = ?
Other, ...
1
if you are not required to create view, but just to update using join, then we can use a workaround with a MERGE statement.
merge into TEST_KEY_PRES_A pa
using TEST_KEY_PRES_B pb
on (pa.KEY1 = pb.KEY1 and pa.KEY2 = pb.KEY2 and pa.VAL1 > 3 and pa.KEY1 = 'K15')
when matched then update
set VAL2 = 'V2-5-X';
0
AFAIK Statspack is still supported (even if no more documented in Oracle Database official documentation since Oracle 10G).
In Oracle 19 Statspack files are still delivered and you can find in spcreate.sql header:
Rem MODIFIED (MM/DD/YY)
Rem kchou 01/12/17 - Bug# 25233027 - Set _oracle_script=FALSE at the
Rem end
Rem ...
-1
To solve this problem:
click on the ORCL in "Databases Detected" tab under Database Connection on the welcome page. You will then see a copy of ORCL as ORCL1 under Connection window on the left hand of SQL Developer User Interface.
Click on the green plus sign button on the left to start a new connection. A new connection window will appear.
Don't change ...
0
You can achieve that by using this select:
select ef.employee_number,ef.full_name,ef.agency_code
from Employment_Form ef, HumanResources hr
where ef.employee_number = hr.employee_number
and ef.agency_code <> hr.agency_code;
Here you select all the columns of the Employment_Form table, fetching only those rows with matching employee_number and wrong ...
3
The "First Commandment" of any DBA is: Thou Shall Not Lose Data!
To accomplish this, you'll want to teardown, build, and restore/recover the database multiple times. To that end, I'd recommend you use some sort of virtualization (eg Oracle VirtualBox).
You might be able to find both Oracle EE and MySQL pre-made virtual guests for VirtualBox.
I believe ...
0
You are only inserting one column value, not both. The second parameter in to_date should be the format used by the first parameter, not another date. Your insert should look like this:
insert into times (departure, arrival) values
(to_date('2018-05-02 12:40','YYYY-MM-DD HH24:MI'),
to_date('2018-05-02 15:08','YYYY-MM-DD HH24:MI'));
1
How did you "format" them? Oracle expect them to be block devices, not formatted with a file system. Did you read this documentation?
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ladbi/configuring-device-persistence-manually-for-oracle-asm.html#GUID-70D50812-CCB2-41E4-AA3B-4689E1DA934E
https://oracle-base.com/articles/linux/udev-scsi-...
0
Change existing:
SQL> create bigfile tablespace users2 datafile '/oradata/ORCL2/users.dbf' size 100M;
Tablespace created.
SQL> alter database default tablespace users2;
Database altered.
Create new one:
DBCA allows you to choose bigfile tablespace for a Custom Database:
A "General Purpose" or "Data Warehouse" database is actually a pre-created ...
0
Remove HOME_TEAMand AWAY_TEAM columns from the GAMES table and add a HOME_AWAY flag column to theGAME_TEAM_STATISTICS table.
Right now, you need to code for an ASSERTION (because they do not yet exist) to ensure the GAME_ID+TEAM_ID combo in GAME_TEAM_STATISTICS matches the GAMES.HOME_TEAM/GAMES.AWAY_TEAM information.
0
Auditing can capture when a user changes their own password. If a sys or admin user changes the password of another user that can't be captured in auditing.
You can use this metalink doc (ID 419942.1) to create a system trigger and change it to add an entry in a temp table.
1
I've found the problem.
The doc ID 1597395.1 says:
The problem occurs when Datapump Export is being performed on a
multi-node RAC where the dumpfile destination is not shared to all
nodes for access. Since multiple nodes will be running the Datapump
job, ALL nodes must have access to the mount point where the dump file
will be written.
So I've ...
3
That is normal behaviour. NOT IN and NOT EXISTS is not the same. They return same results only as long as nulls are not involved.
SQL> create table tablea (id number);
Table created.
SQL> create table tableb (id number);
Table created.
SQL> insert into tablea values(1);
1 row created.
SQL> insert into tablea values(2);
1 row created.
SQL&...
0
For the employee table you should have two audit tables.
1 employee_audit (employee_audit_id , ref_employee_id, reason,update_date, update_by)
and
2 employee_audit_detail (id,ref_employee_audit_id , field_name, old_value)
So you have every value saved only once and you have a complete record,what was changed and by whom grouped the date it was ...
0
Configuring Network Data Encryption and Integrity for Oracle Servers and Clients
Configuring Encryption on the Client and the Server
The sqlnet.ora file on the two systems should contain the following entries:
On the server:
SQLNET.ENCRYPTION_SERVER = [accepted | rejected | requested |
required]
SQLNET.ENCRYPTION_TYPES_SERVER = (...
1
Configuring Oracle ASM Filter Driver During Installation
This procedure must be performed after the Oracle Grid Infrastructure
installation zip files have been unzipped in the Oracle Grid
Infrastructure home, but before you enable the Configure ASM Filter
Driver check box during the installation process when the setup script
is run.
su root
# set ...
-1
I'm pretty inexperienced and still learning but I think the issue has to do with a dedicated server installation.
Top 50 recent answers are included
