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 CREATE
store [...]
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
EXECUTE IMMEDIATE ‘ALTER TABLE ‘||cur.owner||’.'||cur.table_name||
[...]
SELECT * FROM dba_extents
WHERE file_id = &file_id
AND &block_id BETWEEN block_id AND block_id + blocks [...]
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 [...]
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) [...]
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 the search machine for the words unwrap10 or rewrap…)
Source [...]
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 [...]
– 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 [...]
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 [...]
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 lot of files):
restore the files from the backup
recover tablespace [...]
