Oracle: using substitution variables in sqlplus

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 );

Oracle: disable all constraints referencing the table


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;

Oracle: flashback usage


-- 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: 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:

  1. restore the file from the backup
  2. recover datafile ‘&the_file_name’ ;
  3. alter database datafile ‘&the_file_name’ online;

Another possibility (if there are a lot of files):

  1. restore the files from the backup
  2. recover tablespace ‘&tbs_name’ ;
  3. alter tablespace ‘&tbs_name’ online;

Oracle: password function

Find the name of the current password function


SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';

Change the password function for the profile:


ALTER PROFILE &profile. LIMIT PASSWORD_VERIFY_FUNCTION &function_name.;

Example of the password function:


CREATE OR REPLACE FUNCTION dummy_func
(USERNAME VARCHAR2, PASSWORD VARCHAR2, OLD_PASSWORD VARCHAR2)
RETURN BOOLEAN IS
BEGIN
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password is the same as the username');
END IF;
RETURN(TRUE);
END;