Author Archive « Previous Entries Next Entries »

Oracle: usage of the tablespaces (permanent and temporary)

Friday, March 28th, 2008

SET pagesize 10000
SET COLSEP ‘|’
SET VERIFY off
SET serveroutput ON SIZE 1000000
BREAK ON report
COLUMN tablespace_name format a30 heading ‘TABLESPACE’
COLUMN sizegb   format 9999999999D9 heading ‘SIZE-Gb’
COLUMN usedproc format 999D99 heading ‘USED-%’
COLUMN status format a10 heading ‘STATUS’
COMPUTE SUM LABEL ‘Total size:’ OF sizegb ON report
SELECT b.tablespace_name ,
       b.bytes/1024/1024/1024 AS sizegb ,
       NVL(100-((a.bytes/b.bytes)*100), 100) usedproc,
    [...]

AppleScript: rotate mov file in QuickTime Pro

Friday, March 28th, 2008

Some kind of life hack: it’s very easy to make mov file with digital camera, rotating it 90 degrees. However, it’s not so easy to convert the result file to the ‘visible’ form.
QuickTime Pro could do this, and I found the AppleScript script, which could make it even more easy.

tell application “QuickTime Player”
      [...]

Oracle: plan of the running query

Friday, March 28th, 2008

col object_name FOR a40
SELECT operation,     
 options,     
 object_name, 
 partition_id
FROM   v$sql_plan
WHERE address IN
 ( SELECT sql_address FROM v$session WHERE sid = &sid.)
ORDER BY id;

Oracle: using DBMS_METADATA for getting table structure

Monday, March 17th, 2008

SET LONG 5000
SELECT dbms_metadata.get_ddl(’TABLE’,'EMP’,'SCOTT’);

Oracle: using substitution variables in sqlplus

Monday, March 17th, 2008

Get the value:

ACCEPT my_password CHAR PROMPT ‘Password:  ‘ HIDE
ACCEPT birthday DATE FORMAT ‘dd/mm/yyyy’ DEFAULT ‘01/01/1950′ PROMPT ‘Enter birthday date:  ‘

Declaring the variable

DEFINE the_answer = 42

Undefine the variable

UNDEFINE the_answer

How to remember the result of the query

COLUMN the_date new_value the_rundate noprint;
SELECT TO_CHAR(SYSDATE, ‘DDMMYYYY_HH24MI’) the_date FROM dual;
SELECT ‘&the_rundate’ FROM dual ;

Save the variables to the file

store SET myvars.txt [...]

Oracle: disable all constraints referencing the table

Wednesday, March 5th, 2008

begin
FOR cur IN (SELECT fk.owner, fk.constraint_name , fk.table_name
        FROM all_constraints fk, all_constraints pk
        WHERE fk.CONSTRAINT_TYPE = ‘R’ AND
        pk.owner = ‘&which_owner’ AND
        fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
           AND pk.TABLE_NAME = ‘&which_table’
        ) loop
    [...]

Oracle: info about the corrupted block

Monday, February 25th, 2008

SELECT * FROM dba_extents
WHERE  file_id = &file_id
AND    &block_id BETWEEN block_id AND block_id + blocks - 1;

Oracle: using recycling bin

Monday, February 18th, 2008

Information about the objects in recycle bin:

SELECT object_name,
 original_name, type,
 can_undrop , can_purge ,
 droptime
FROM recyclebin

or
show recyclebin
Restore

flashback TABLE mytable TO before DROP;

Clear recycle bin
purge recyclebin;

mysql: rownum functionality

Thursday, January 31st, 2008

Select, update etc…

UPDATE mytable
SET col1 = ’somevalue’
ORDER BY col2
LIMIT 300

rownum analog:

SELECT @rownum:=@rownum+1 rownum, mytable.*
FROM (SELECT @rownum:=0) r, mytable;

Oracle: source text for the view, package etc

Friday, January 25th, 2008

Source text of the package

SELECT text
FROM dba_source
WHERE upper(name) LIKE upper(’&which_object’)
ORDER BY line ;

Use $ORACLE_HOME/bin/wrap utility to encrypt the package (there is no unwrap)
(Well, actually, there is unwrap - just look in the search machine for the words unwrap10 or rewrap…)
Source text of the views

SET long 5000
col text FOR a80
SELECT text FROM dba_views WHERE view_name = [...]

« Previous Entries Next Entries »
Keep on coding