set long 5000
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT')
from dual;
Some other values of the type field for dbms_metadata.get_ddl function:
(See also “Oracle® Database PL/SQL Packages and Types Reference“)
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 set myvars.txt replace
store set myvars.txt append
Assign several values to the variable
DEFINE my_list = " 'the Life', 'the Universe', 'and Everything'"
select *
from Book
where answer in ( &my_list );
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||
' MODIFY CONSTRAINT '||cur.constraint_name||' DISABLE';
end loop;
end;
select * from dba_extents
where file_id = &file_id
and &block_id between block_id and block_id + blocks - 1;
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;
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;
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 text of the views
set long 5000
col text for a80
select text from dba_views where view_name = upper('&which_view');
select query from DBA_mviews where mview_name = upper('&which_view');
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='&which_view';
Source text of the synonym
select
TABLE_OWNER || '.' || TABLE_NAME
|| decode ( db_link , null , '' , '@' || db_link ) SYNONYM_OBJECT
from dba_synonyms
where SYNONYM_NAME = upper('&which_synonym');
Source text of the trigger
select
'create or replace trigger "' || trigger_name || '"'
|| chr(10)|| decode( substr( trigger_type, 1, 1 ), 'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' )
|| chr(10) || triggering_event || chr(10) || 'ON "' || table_owner || '"."' || table_name
|| '"' || chr(10) || decode( instr( trigger_type, 'EACH ROW' ), 0, null, 'FOR EACH ROW' )
|| chr(10) ,
trigger_body
from dba_triggers
where trigger_name = upper('&which_trigger') and owner=upper('&trigger_owner');
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');
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;