New answers tagged

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 ...
user avatar
  • 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
user avatar
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 &...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 (...
user avatar
  • 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 ...
user avatar
-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....
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 38.1k

Top 50 recent answers are included