SQL - 3/7 - MemoSoup

Oracle: info about the corrupted block

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

Oracle: using recycling bin

Information about the objects in recycle bin:

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

show recyclebin

Restore

flashback TABLE mytable TO before DROP;

Clear recycle bin

purge recyclebin;

mysql: rownum functionality

Here are some examples how to use “rownum” functionality (similar to Oracle) in mysql

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 and other objects

The best method is to use dbms_metadata package:

SELECT dbms_metadata.get_ddl(UPPER(’&OBJ_TYPE’),UPPER(’&OBJ_NAME’),UPPER(’&OWNER’)) FROM dual;

Additional methods could be also used: 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 [...]

mySQL: size of the database

SELECT table_schema "Database", SUM( data_length + index_length ) / 1024 / 1024 "Size (MB)", SUM( data_free )/ 1024 / 1024 "Free (MB)" FROM information_schema.TABLES GROUP BY table_schema ;

This will work in mySQL 5.0.2 and newer. Use SHOW TABLE STATUS command for other versions.

– To save the flashback information for the last 30 minutes: ALTER SYSTEM SET UNDO_RETENTION = 1800; — SELECT * FROM some_table AS OF TIMESTAMP (’2008-10-08 06:31:58′, ‘YYYY-MM-DD HH24:MI:SSS’); SELECT * FROM some_table AS OF SCN 12345678; — dbms_flashback could be also used EXECUTE dbms_flashback.enable_at_time (’18-JAN-08 11:00:00′);

Oracle: information about ASM

Get the information about ASM disks:

SELECT GROUP_NUMBER, DISK_NUMBER, TOTAL_MB/1024 GB, NAME FROM v$asm_disk;

Get the information about ASM diskgroups (including used space and the free space):

SELECT GROUP_NUMBER, NAME, TOTAL_MB/1024 TOTAL_GB, FREE_MB/1024 FREE_GB FROM v$asm_diskgroup;

Oracle: file needs recovery (offline mode)

select d.file# f#, d.name, d.status, h.status from v$datafile d, v$datafile_header h where d.file# = h.file# and (d.status not in (’SYSTEM’,'ONLINE’) or h.status != ‘ONLINE’ );

If there are such files, the recovery is necessary:

restore the file from the backup recover datafile ‘&the_file_name’ ; alter database datafile ‘&the_file_name’ online;

Another possibility (if there are a [...]

MS SQL & Sybase: recreate master database

Sybase: before 12.5: buildmaster (bldmatr for NT)

buildmaster -d master_device -s size_in_2k_pages

12.5 and later: dataserver with some options (sqlsvr for NT)

dataserver -d master_device -b size -forcebuild -z page_size -Z size_of_master_db

MS SQL:

before MS 2005: rebuildm utility (GUI)

MS SQL 2005:

Start server in single-user mode (sqlserver.exe -m) setup.exe /qn INSTANCENAME=instance_name REINSTALL=SQL_Engine REBUILDDATABASE=1 [...]

Oracle: logon trigger

Create the table to save logon information:

CREATE TABLE logonaudittable ( event VARCHAR2(10), sid NUMBER, serial# NUMBER, TIMESTAMP DATE, username VARCHAR2(30), osuserid VARCHAR2(30), machinename VARCHAR2(64) ); [...]

MemoSoup