Monday, August 24, 2015

Be aware of the count=0 when using Python's re.sub() to find and replace text

I have an xml file and I want to remove a whole section of element without actually parsing the XML file. I wrote an Python script to do just that. I first tested it out like this:

import re
input_text = open('my.xml').read()
result = re.findall(r'<library-ref>.*</library-ref>', input_text, re.S)
print(result)


The result IS what I wanted, so I thought I can quickly replace it with something like this:

result = re.sub(r'<library-ref>.*</library-ref>', '', input_text, re.S)

And to my surprise this does not work. It took me a while to notice that I mis used the method according to the documentation, which has this signature:

re.sub(pattern, repl, string, count=0, flags=0)

At first glace, I thought that count is already default to zero and I do not need to set it, but because that is a positional parameter, you must include it! So you suppose to call it explicit like this:

result = re.sub(r'<library-ref>.*</library-ref>', '', input_text, 0, re.S)

Or, if you like, you can use the "flags" parameter name explicitly without the odd looking zero.

result = re.sub(r'<library-ref>.*</library-ref>', '', input_text, flags=re.S)

So here is an example of flexible dynamic typing of optional parameters can bite if you are not careful.

Saturday, August 22, 2015

View and terminate Oracle Database user sessions

Verify sessions:
SELECT USERNAME FROM V$SESSION;

SELECT USERNAME, SID, SERIAL#, STATUS
  FROM V$SESSION
  WHERE USERNAME like 'SCOTT%';

Generate SQL to terminate sessions:
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';'
  FROM V$SESSION
  WHERE USERNAME like 'SCOTT%';

Wednesday, August 19, 2015

How to import a Oracle database dump file

Here is an example on how to import a Oracle database dump file (a binary file that's exported from a Oracle database using the Oracle data pump utility). For this instruction example, let's say I was given a myapp.dmp.gz file.

NOTE: If you do not use the same db user/tablespace for export and import, then you need to know its names used during export to perform the remapping during import.

Step 1: If you don't have an Oracle database instance to work with, create one first. Or ask your DBA to create or allocate a instance for your import purpose.

Step 2: Create a new tablespace and a DB user in an Oracle database instance. (For this example, I will call my new user MYAPP08182015, password Password1, and tablespace named MY_TABLE_SPACE.)

create tablespace MY_TABLE_SPACE datafile
  '/opt/myapp_datafiles/MY_TABLE_SPACE.dat'
  size 500m autoextend on next 500m
  extent management local
  segment space management auto;

create user MYAPP08182015 identified by Password1
  default tablespace MY_TABLE_SPACE
  temporary tablespace TEMP
  quota unlimited on MYTABLESPACE;

grant create session, grant any privilege to MYAPP08182015;
grant IMP_FULL_DATABASE to 
MYAPP08182015;

create directory MY_DATADUMP_DIR as '/opt/myapp_datadump';

Note that user must have IMP_FULL_DATABASE privilege to be able to used by import tool. And if you don't want to use default import data directory, then you would need to create the directory object to be used by import as shown above.

Note also that your TEMP tablespace might not have enough disk space for large import. In this case, you may increase it like this:

alter tablespace temp add tempfile
  '/opt/myapp_datafiles/TEMP2.dat'
  size 500m autoextend on next 500m;

Step 3: Now you have a new db User (Schema) and password, you may start the data import using the command line prompt. Ssh into the database server where you can find the impdp command utility.

scp myapp.dmp.gz zemian@mydbserver:/opt/myapp_datadump
ssh zemian@mydbserver
cd /opt/myapp_datafiles
gunzip myapp.dmp.gz
/app/oracle/dbhome/bin/impdp MYAPP08182015/Password1 DIRECTORY=MY_DATADUMP_DIR REMAP_SCHEMA=MYAPP:MYAPP08182015 REMAP_TABLESPACE=USERS:MY_TABLE_SPACE LOGFILE=myapp-import.log DUMPFILE=myapp.dmp

References:
http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_overview.htm

Tuesday, August 18, 2015

How to check Oracle Database tablespace

When creating a new users in Oracle database (new schema), you need to verify the existing tablespace availability. This query will show you what's there and how much space are free to use.

SELECT df.tablespace_name "Tablespace",
  totalusedspace "Used MB",
  (df.totalspace - tu.totalusedspace) "Free MB",
  df.totalspace "Total MB",
  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace
  FROM dba_data_files
  GROUP BY tablespace_name
  ) df,
  (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
    tablespace_name
  FROM dba_segments
  GROUP BY tablespace_name
  ) tu
WHERE df.tablespace_name = tu.tablespace_name;

Also, this query will show where the tablespace file are located:

SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME
   FROM DBA_DATA_FILES;

Here are some references on how Oracle manages user, schema and tablespace.