New answers tagged oracle
4
votes
Accepted
How to terminate user's sessions in Oracle DB exactly 8 hours after user connected?
CREATE PROFILE
To specify resource limits for a user, you must:
Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. This parameter
...
- 38.1k
-1
votes
How do I execute a PL/SQL package from my database SQL> prompt?
It is a function so you have to use
SELECT C_FIX_STUCK_COS.FIX_STUCK_COS() FROM DUAL
0
votes
Accepted
Cannot connect to a pluggable database via IDE
In order to connect to a pluggable database, you need to specify the SERVICE_NAME for it (forslash notation '/XEPDB1'), so you will have to specify database type "Oracle Database" and not &...
- 602
0
votes
Access Oracle Database remotely via DBeaver
Yes, remote connections to an Oracle database is the standard way in real deployments.
Assuming there is nothing blocking communication between the two devices on your WiFi.
Start a listener on your ...
- 2,468
1
vote
Update statement runs for longtime and throws ORA-01654 table space error
One option might be to use a procedure.
CREATE OR REPLACE PROCEDURE update_ids
AS
BEGIN
FOR r IN (
SELECT new_system_id,
old_system_id
FROM table2 ) LOOP
...
- 2,815
2
votes
schema ownership/permissions in MS-SQL vs Oracle
Roughly speaking an Oracle schema is analogous to a SQL Server database and login, combined. An Oracle user may own schema objects or not, similar to the way an OS user may own files and directories ...
- 2,185
2
votes
Accepted
Update statement runs for longtime and throws ORA-01654 table space error
То overcome the error "ORA-01654: unable to extend index ..." in most cases you have to extend the corresponding tablespace. There are three different ways to do that, depending on the case ...
3
votes
Update statement runs for longtime and throws ORA-01654 table space error
Such updates should be done with MERGE:
MERGE INTO TABLE1 F
USING TABLE2 TC
ON (TC.OLD_SYSTEM_ID = F.SYSTEM_ID)
WHEN MATCHED THEN UPDATE F.SYSTEM_ID = TC.NEW_SYSTEM_ID);
This should work faster, but ...
- 38.1k
2
votes
Is the SQL dialect in SQL Server closer to Oracle or to Postgres?
My advice is to separate them out - completely separate your calling functions - specify the db as a global variable or store it in a file somewhere and read it on programme start up.
The reasons ...
- 27.2k
2
votes
Is the SQL dialect in SQL Server closer to Oracle or to Postgres?
From a code maintenance perspective, I would say neither.
Let each query [class?] stand on its own.
If you start inheriting one from the other and something changes in the base dialect, you'll ...
- 6,593
1
vote
Applying conditional statement in Oracle DB
As you were already told, data model is wrong.
Anyway, here's what you might try to do.
Sample data:
SQL> with
2 people (id, name, adore) as
3 (select 1, 'Stone' , 'dog' from dual union ...
- 740
1
vote
Error when i use impdp for restore from backup in oracle
PLEASE STOP calling these things Backups!!
They're not.
expdp and impdp are Data Transfer tools, not Recovery tools.
To backup any Oracle database, you MUST use RMAN (if only for the sake of your ...
- 6,593
0
votes
In Oracle SQL 19c, how can I INSERT a new record into a supertype and its related subtypes in one statement?
The generated personID can be returned into a bind variable with the returning_clause. Example:
SQL> create table parent(id number generated always as identity not null primary key,
c1 ...
- 38.1k
1
vote
Applying conditional statement in Oracle DB
Depending on the result of a query from the 'People' table, how can I proceed with a different query to request data further either from 'Dogs' or 'Cats'.
You shouldn't.
The two table structure (...
- 6,593
0
votes
Printing comments or statements when executing script to a output file using TOAD/ORACLE
The answer from Hector is showing only the PL/SQL version and it's practically not was has been asked.
If you're looking for printing in pure SQL scripts, use the sqlplus command PROMPT.
PROMPT ...
-1
votes
How to check Oracle database connection encryption type
select a.sid,a.serial#,b.network_service_banner, a.sql_id,a.username, a.logon_time,a.machine,a.program,
a.osuser, a.status
from v$session a, v$session_connect_info b where
a.sid=b.sid
and a....
0
votes
Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?
You can revert both operations - DROP TABLE and TRUNCATE TABLE - in Oracle. Though both are autocommited.
Oracle uses different mechanisms in order to do that.
DROP TABLE is based on RECYCLEBIN ...
- 160
4
votes
Accepted
is my data compressed?
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> select dbms_compression.get_compression_type(user, 'T1', rowid) as compression_type,
count(*)
from t1 group by ...
- 38.1k
2
votes
is my data compressed?
You can run the DBMS_COMPRESSION.GET_COMPRESSION_RATIO system stored procedure to obtain the actual compression ratio for the table. It won't tell you whether someone has run alter table ... move, ...
- 21.6k
0
votes
SSIS Package is writing corrupt data, but only when run as a Job
After patching the SQL Server to 15.0.4223 it started to work 99%.
There is a string column that is inserted into a number column in the target database. While that worked before, this still produced ...
- 126
0
votes
How do I handle an I/O error when restoring an RMAN backup?
A restore command checks if it can skip the restore of a file that is on disk already. Meaning, your restore command first checks and validates the archivelog it believes to be there, but unable to do ...
- 38.1k
Top 50 recent answers are included