OTN Appreciation Day : Partition your table online !

#ThanksOTN @oraclebase No, I am not talking about DBMS_REDEFINITION, where you get a kind of online feeling. No, I don’t want to rename my table, rename my foreign keys, my primary key, my not-null-constraints, recreate my referential integrity, recompile my triggers. I just want to partition a non-partitioned table. ALTER TABLE EMP   MODIFY   PARTITION BY […]

What is the instance name?

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 […]

What is SID in Oracle ?

In the doc you’ll find, it’s the Oracle system identifier. Okay, let’s imagine the following Environment: ORACLE_SID=ORA001 init.ora: DB_NAME=DB001 DB_UNIQUE_NAME=UNI001 INSTANCE_NAME=INS001 SERVICE_NAMES=SVC001,SVC002 DB_DOMAIN=EXAMPLE.COM GLOBAL_NAMES=false database: SQL> select * from GLOBAL_NAME; GLO001.example.com listener.ora: SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (GLOBAL_NAME=GLO001.EXAMPLE.COM)       (SID_NAME=ORA001)     )   ) What is my SID? Actually there is more than one correct answer. In […]

duplicate to a future date

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! […]

column width change in 12c

In 11g I used to have 30 characters width in my dictionary SQL> select table_name, column_name from user_tab_columns; TABLE_NAME                     COLUMN_NAME —————————— —————————— BONUS                          COMM BONUS                          SAL BONUS                          JOB BONUS                          ENAME DEPT                           LOC DEPT                           DNAME DEPT                           DEPTNO EMP                            DEPTNO EMP                            COMM EMP                            SAL EMP                            HIREDATE TABLE_NAME                     COLUMN_NAME —————————— —————————— EMP                            MGR EMP                            JOB EMP                            ENAME EMP                            EMPNO SALGRADE                       HISAL SALGRADE                       LOSAL SALGRADE                       GRADE 18 rows selected. Which was […]

powershell odbc sybase

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 […]

Generate 11g password hash

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 […]

delete all data

How do you delete all data? The simplistic approach would be to truncate all tables SQL> select table_name from user_tables; TABLE_NAME ———- T1 SQL> truncate table t1; Table truncated. You cannot truncate if you have referential integrity constraints. SQL> truncate table t2; ORA-02266: unique/primary keys in table   referenced by enabled foreign keys Ok, let’s disable […]

tracefile tim to readable date format

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; […]

job_name cannot be null

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 […]

Open last one-note page

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 = […]

Don’t call it test

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 && […]

list database monitoring users

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 […]

Drop table if exists

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 […]

Irrecoverable part III : a fix

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: […]

Irrecoverable full backup part II : reporting

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 […]

Can you restore from a full online backup ?

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 […]

🎂 10 years 🎂

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) […]

run sudo, ssh, password, su in simulated interactive mode

Some commands do not like non-interactive mode $ passwd <<EOF > oldpassword > newpassword > newpassword > EOF Changing password for user lsc. Current password for lsc@example.com: passwd: Authentication token manipulation error $ echo oraclepassword | su – oracle standard in must be a tty $ echo sudopassword | sudo su – oracle [sudo] password […]

bypass ora-20

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. […]

switch user in Oracle

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 […]

comment in external table

Depending the files, you may use different signs for comments, typically # hash // slash slash /* slash-star star-slash */ : column — dash dash The latest is used in sql and pl/sql, but : CREATE TABLE t (x NUMBER) ORGANIZATION EXTERNAL (   TYPE ORACLE_LOADER   DEFAULT DIRECTORY data_pump_dir   ACCESS PARAMETERS (     FIELDS TERMINATED BY ';'  — […]

How to convert Excel file to csv

#excel to #csv in #powershell (New-Object -ComObject Excel.Application).Workbooks.Open("c:x.xlsx").SaveAs("c:x.csv",6) — laurentsch (@laurentsch) February 13, 2015 One-liner to convert Excel to CSV (or to and from any other format). There is a bug 320369 if you have excel in English and your locale is not America. Just change your settings to us_en before conversion.