Author: Laurent Schneider

When you want to run a SQL script, you rather use sqlplus. If you want to run it within a PL/SQL stored procedure, you are screwed. You could redo the logic of sqlplus, this is what any decent IDE and code runnner does, and it’s a pile of work and you will get lot’s of […]

If you delete most of the rows in one table, you probably want to regain that space, to minimize IO and fragmentation. If you delete most of the rows in most of the tables, you probably also want to resize the datafile to reduce storage and backup space. In this case, you could move your […]

If your Oracle SID doesn’t match your instance name in init.ora, this is quite confusing. Check my previous post, what is sid in oracle In the instance_name column of the view v$instance, as well as in USERENV context, it matches the ORACLE_SID of the underlying operating system. SQL> var ORACLE_SID varchar2(9) SQL> set autoprint on […]

If you work with large databases, you often wait way to long for the clones. Typically you want to duplicate a 10TB database to production timestamp 9am, and you start at 9am and then you wait for hours. Is it possible to start the clone, let’s say, at midnight, and set until time 9am? No! […]

Happy new year 🙂 Today I had to import a subset of a database and the challenge was to restore a parent table without restoring its children. It took me some minutes to write the code, but it would have taken days to restore the whole database. CREATE TABLE t1( c1 NUMBER CONSTRAINT t1_pk PRIMARY […]

I just read the Enterprise Manager Licensing Information User Manual today. They are a lot of packs there, and you may not even know that autodiscovering targets is part of the lifecycle management pack or that blackouts are part of the diagnostic pack. Have a look

Oracle goes there ==> …/oracle-odbc-hello-world-with-powershell.html To test Sybase ODBC driver with Powershell, it’s not much different configure the DsN with odbcconf or %SYSTEMROOT%\SysWOW64\odbcconf for the 32bits drivers under Windows 64. cmd /c "%SYSTEMROOT%\SysWOW64\odbcconf.exe /a {configdsn ""Adaptive Server Enterprise"" ""DSN=helloworld|database=mydb|port=25000|server=srv01""}" The name of the driver is important. It is probably also called “Adaptive Server Enterprise” on […]

This is probably ambitious and I’ll start with a disclaimer, there is no such thing. But ok, we know that system/manager isn’t Apart from system/manager, there are hundreds of weak passwords that are commonly used and easy to guess. On your database server, after a few tries, the account is lock. And maybe the attacker […]

An easy way to generate a value string from the ssl is to use openssl Let’s take a random salt of ABCDEFGHIJ. The length of 10 is important. The hexadecimal representation is -41-42-43-44-45-46-47-48-49-4A- $ echo "SafePassw0rDABCDEFGHIJ\c" | openssl dgst -sha1 (stdin)= 47cc4102144d6e479ef3d776ccd9e0d0158842bb With this hash, I can construct my value SQL> create user testuser identified […]

In trace file, the time is recorded, this could be used to measure time between two timestamps But how do you convert 31796862227375 to a human format? This is how I proceeded : SQL> oradebug SETMYPID Statement processed. SQL> oradebug tracefile_name /u01/log/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_32440740.trc SQL> alter session set nls_date_format='YYYY-MM-DD_HH24:MI:SS' sql_trace=true; Session altered. SQL> select '&_DATE','Start' from dual; […]

exec dbms_scheduler.create_job(job_name=>null,job_type=>'PLSQL_BLOCK',job_action=>'BEGIN NULL; END;') ORA-27451: JOB_NAME cannot be NULL ORA-06512: at "SYS.DBMS_ISCHED", line 146 ORA-06512: at "SYS.DBMS_SCHEDULER", line 288 ORA-06512: at line 1 This sounds like a proper error message. A bit less obvious is the drop_job message SQL> exec dbms_scheduler.drop_job(job_name=>null) ORA-20001: comma-separated list invalid near ORA-06512: at "SYS.DBMS_UTILITY", line 236 ORA-06512: at "SYS.DBMS_UTILITY", line […]

Truncate is useful in some serial batch processing but it breaks the read-write consistency, generates stranges errors and results for running selects, and it needs DROP ANY TABLE when run over a table that you do not own. But also, DELETE is faster in the following test case. In 12c, you could have over one […]

Most permissions issues are due to a missing role or privilege. But in the following test case you need to revoke the right to get more privileges. create table tt(x number); create view v as select * from tt; create role rw; grant all on v to rw; I’ve created a read-write role on a […]

If you got a one-note document, you may want to automatically go to the last page. This is possible with powershell. First you create a ComObject. There are incredibly many ComObject that could be manipulated in powershell. $o = New-Object -ComObject OneNote.Application Now it get’s a bit confusing. First you open your document [ref]$x = […]

There are quite a few names to avoid in your scripts. Even if there are not reserved-words, keep away ! I’ll start with test cd $HOME/bin vi test echo hello world chmod +x test ./test hello world The problem is that it may break your other scripts $ ssh localhost test 1 = 2 && […]

I am quite familiar with the SYSMAN tables but this one required me some googling beyond the Oracle documentation. The list of targets in your Oracle Enterprise Manager is in SYSMAN.MGMT_TARGETS. Each database target is monitored by a database user, typically DBSNMP. To retrieve this information, you need some to hijack your database, read this […]

The syntax that you are looking for is docs.oracle.com/…/drop-table.html DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] … [RESTRICT | CASCADE] Wait, this does not work ! SQL> drop table if exists t; drop table if exists t * ERROR at line 1: ORA-00933: SQL command not properly ended Okay. It was the Oracle MySQL […]

I wrote a while ago about my security concerns regarding xhost + xterm -display mypc:0 Way back then, I suggested ssh tunnel. SSH is pretty easy to set up, by enabling the X11Forwarding option. In OpenSSH 3.8 release note, 2004, there was a new default . ssh(1) now uses untrusted cookies for X11-Forwarding In the […]

After part I:the problem, Part II: two reports here is part III. We have backed up archivelog during a full and deleted them before the full completed. RMAN> list backup of archivelog all; List of Backup Sets =================== BS Size Type Elapsed Completion — —— —- ——– ———- 15 4.00K DISK 00:00:00 13:31:08 BP Key: 15 Status: […]

After my post Can you restore from a full online backup ?, I needed to come up with a report. Assuming that each backup goes in a different directory, I just wrote two reports. Report gaps in v$backup_redolog (or rc_backup_redolog if you use the catalog) DIR FIRST_CHANGE# NEXT_CHANGE# ——- ————- ———— /bck01/ 284891 285140 /bck01/ 285140 285178 […]

The question is not HOW TO DO IT but WHETHER YOU CAN DO IT ! A typical backup script would contains something like BACKUP DATABASE PLUS ARCHIVELOG: backup database format '/u99/backup/DB01/20150518/full_0_%d_s%s_p%p' plus archivelog format '/u99/backup/DB01/20150518/arc_%d_s%s_p%p'; Starting backup at 2015-05-18_18:27:55 current log archived input archived log thread=1 sequence=469 … piece handle= /u99/backup/DB01/20150518/arc_DB01_s86_p1 Finished backup at 2015-05-18_18:27:58 […]

Thanks to all my readers for being so faithful 🙂 I’ll post a new solution to calculate factorial. This is the one I posted 10 years ago : https://laurentschneider.com/2005/05/recursive-sql.html I also used it in the obfuscation contest with function f (x number) return number is begin return case x when 1 then x else x*f(x-1) […]

When you really need to run one script, at all cost, an annoying error is ORA-00020: maximum number of processes (40) exceeded, which can even occurs as sysdba. Test case (21 is a not something to do in real life): SQL> alter system set processes=21 scope=spfile; System altered. SQL> startup force quiet ORACLE instance started. […]

Almost a decade ago I wrote about su in sqlplus. This 10gR2 “new” feature allows delegation à la sudo. By checking the DBA_USERS in 12c I found PROXY_ONLY_CONNECT. According to Miguel Anjo, there is a secret syntax for allowing only the proxy user. SQL> ALTER USER app_user PROXY ONLY CONNECT; SQL> CONNECT app_user/xyz ERROR:ORA-28058: login […]