M9K blog: заметки с тегом Oracle

Посмотреть тело активного SQL-запроса по SID-у

Wed, 19 Mar 2025 08:08:40 +0000

select 
  a.sid, 
  a.serial#,
  b.sql_text 
from 
  v$session a, 
  v$sqlarea b 
where 
  a.sql_address = b.address 
  and sid = $SID_NUMBER;

Oracle: Cloning scheme in entire DB

Tue, 06 Aug 2024 07:27:14 +0000

>CREATE USER <SCHEME_DEST> IDENTIFIED BY "<SCHEME_DEST_PASSWORD>";
>GRANT CONNECT TO <SCHEME_DEST>;
>GRANT CREATE SESSION TO <SCHEME_DEST>;
>ALTER USER <SCHEME_DEST> quota unlimited on <DEFAULT_USER_TABLESPACE>;
>expdp system/<SYSTEM PASSWORD>@<DB_SID> schemas=<SCHEME_SRC> directory=<DIRECTORY NAME> dumpfile=<DUMP NAME>.dmp logfile=explog.txt
>impdp system/<SYSTEM PASSWORD>@<DB_SID> directory=<DIRECTORY NAME> dumpfile=<DUMP NAME>.dmp logfile=implog.txt remap_schema=<SCHEME_SRC>:<SCHEME_DEST> transform=OID:N

Oracle — View Scheduler Jobs

Thu, 29 Dec 2022 08:03:30 +0000

SELECT owner, job_name, comments FROM dba_scheduler_jobs;

Oracle — Disable Jobs

Fri, 16 Dec 2022 03:22:18 +0000

BEGIN
DBMS_SCHEDULER.disable (NAME => 'JOB_NAME', FORCE => TRUE);
END;

SQLplus и кодировка

Thu, 10 Dec 2020 04:35:12 +0000

Фикс:

cmd
>set NLS_LANG=russian_cis.ru8pc866
>sqlplus

Процессы Oracle

Fri, 25 Sep 2020 04:51:14 +0000

Наличие запущенных листенеров в памяти

ps -ef | grep tnslsnr

Запущенные инстансы Oracle SQL

ps -ef | grep pmon | cut -s -f 3 -d "_"