startup mount;

Note: The controlfile has been read at this stage but the datafiles are not open for reading or writing.

startup open read only

Note: the database files are open for reading only

Note: database files will be open for reading and writing, this is the normal mode of operation.

startup force;

Note: this performs a 'shutdown abort' followed by a 'startup'

startup restrict;

# To change into restricted mode
alter system enable restricted session;
alter system disable restricted session;

Note: Users with restricted access can access the system only (normally DBA's)

# You must be in mount mode to alter the database, if not then shutdown the database first

alter database open;
alter database open read only;
alter database restricted;

# Display instance state in nomount mode v$database is unavailable.
# MODES:
#   started (nomount mode)
#   mounted (mount mode)
#   open (open mode)

select status from v$instance;

# Obtain the database operation mode, the database must be in mounted or open mode to access v$database
# MODES:
#   mounted (mount mode)
#   read write (open mode)
#   read only (open mode)

select open_mode from v$database;

# Is restricted session enabled
select logins from v$instance;

shutdown normal;
shutdown transactional;
shutdown immediate;
shutdown abort;

startup force;

Note: 'startup force' will run a 'shutdown abort' immediately followed by a 'startup'

alter system enable restricted session;
alter system disable restricted session;

select logins from v$instance;

select log_mode from v$database;
archive log list;

create spfile from pfile;

alter database datafile 'c:\oracle\test.dbf' offline;

Note: you must offline the tablespace first

drop tablespace test;
drop tablespace test including contents and datafiles; (removes the contents and the physical data files)

alter tablespace test rename to test99;
alter tablespace test [offline|online];
alter tablespace test [readonly|read write];
alter tablespace test [begin backup | end backup];

Note: use v$backup to see tablespace is in backup mode (see below)

alter tablespace temp02 tablespace group tempgrp;

Note: if no group exists oracle will create it

select property_value from database_properties where property_name = 'DEFAULT_TEMPORARY_TABLESPACE';
select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

shutdown database
rename file
startup database in mount mode
alter database rename file 'old name' to'new name'
open database
backup controlfile

alter database clear logfile group 3;
alter database clear unarchived logfile group 3;

Note: used the unarchived option when a loggroup has not ben archived

alter system checkpoint;

alter system switch logfile;
alter system archive log current;
alter system archive log all;

# Difference between them are
switch logfile - will switch logfile and return prompt immediately, archiving will take place in the background
log current - will switch logfile and return prompt only when logfile has been successfully archived
log all - will only archiving full log files

Note: I have discussed checkpoints

alter system set log_archive_dest_1 = 'location=c:\oracle\archive' scope=spfile;
alter system set log_archive_format = 'arch_%d_%t_%r_%s.log' scope=spfile;

shutdown database
startup database in mount mode
alter database archivelog;
startup database in open mode

Archive format options
%r - resetlogs ID (required parameter)
%s - log sequence number (required parameter)
%t - thread number (required parameter)
%d - database ID (not required)

select begin_time, undotsn, undoblks, activeblks, unexpiredblks, expiredblks from v$undostat;

select begin_time, undotsn, undoblks, activeblks, unexpiredblks, expiredblks from dba_hist_undostat;

NOTE: If your current undo_retention period is 6 days then the oldest undo data in dba_hist_undo should be 6 days old.

select * from v$recovery_file_dest;

Note: this details space_used, space_limit, space_reclaimable, # of files

rman> backup recovery area;
Note: includes backup sets, datafile and archive, controlfile excludes: flashback log, current controlfile, online redo logs

rman> backup recovery files;
Note: includes all files whether or not they are in the flash recovery area

select flashback_on from v$database;

Note: you should get a restore point only reply if using restore points

select * from employees as of timestamp
to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')
where last_name = valle;

select employee_id, name from hr.employee as of timestamp (systimestamp - interval '15' minute);

select * from employees as of scn 4542;

Note: using a scn will put you with 3 secs if you need to be dead accurate use timestamps instead.

grant execute on dbms_flashback to test01;

execute dbms_flashback.enable_at_time(to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS'));

execute dbms_flashback.disable;

insert into employees
select * from employees as of timestamp
to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')
where last_name = valle;

insert into employees select * from employees as of scn = 4542;

select begin_time, end_time, tuned_undoretention from v$undostat;

Note: the time is in seconds

select versions_xid as xid, versions_startscn as start_scn, versions_endscn as end_scn,   versions_operation as operation, empname
from employees
  versions between timestamp minvalue and maxvalue
order by versions_startscn;

select operation, undo_sql, table_name
from flashback_transaction_query
where start_timestamp >= to_timestamp ('04-12-2007 05:00:00', 'DD-MM-YYYY HH:MI:SS')
and commit_timestamp <= to_timestamp ('04-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')
and table_owner='VALLEP';

Note: this will give you the SQL to reserver the change that was applied to the database,just cut and paste to use.

flashback table employees to timestamp to_timestamp
('04-12-2007 05:00:00', 'DD-MM-YYYY HH:MI:SS')

flashback table employees to timestamp (systimestamp - interval '15' minute);

flashback table employess to timestamp (sysdate - 1);

flashback table employees, depts to timestamp (sysdate - 1);

flashback table employees to timestamp (sysdate -1) enable triggers;

Note: Oracle disables triggers by default when falshing back a table

flashback table <table_name> to before drop rename to <new name>;

Note: use if table name already exists

drop table <table_name> purge;

Note: table will not be in recyclebin

purge table <table_name>

Note: table will be gone from recyclebin

truncate table

Note: table will not be in the recyclebin

drop user test cascade

Note: will not store anything in the recyclebin

select * from "BIN$NwM/FEjuSUORrgxHUPR3WA==$0";

Note: the double quotes

flashback table <table_name> to before drop rename to <new name>;

Note: use if table name already exists

drop table <table_name> purge;

Note: table will not be in recyclebin

purge table <table_name>

Note: table will be gone from recyclebin

truncate table

Note: table will not be in the recyclebin

drop user test cascade

Note: will not store anything in the recyclebin

select * from "BIN$NwM/FEjuSUORrgxHUPR3WA==$0";

Note: the double quotes

grant resumable to vallep;

grant execute on dbms_resumable to vallep;

select user_id, session_id, name, timeout, start_time, suspend_time from dba_resumable;

select username, event from v$session where event like '%sus%';

resumable_timeout=7200;

Note: default is 0, time is in seconds

alter session enable resumable timeout 18000;

execute dbms_resumable.set_timeout(18000);

Note: time is in seconds

select dbms_resumable.get_timeout() from dual;

create user vallep identified by password;
create user vallep identified by password default tablespace users temporary tablespace temp quota 100m on users;

drop user vallep;
drop user vallep cascade;

Note: the cascade option will remove all the users objects as well.

alter user vallep identified by password;
alter user vallep identified externally;
alter user vallep identified globally as extname;

Note:
identified by - the password will be kept in the data dicitonary
identified externally - authenication will be performed by the O/S
idenitified globally as extname - authenication will be performed by external app i.e radius

a user is only allowed to change is his/her password

grant create session to vallep;

Note: this allows the user to connect to the database

drop profile user_profile cascade;

Note: any users using the dropped profile will be automatically assigned the default profile

grant create session to vallep;
grant create tablespace to vallep;
grant create user, alter user, drop user to vallep;

grant create session to vallep with admin option;

Note: now vallep can also grant this privilege

create role test_role identified by <password>;

Note: the password is optional, you can also use externally or globally authentication

grant dba to test_role;

Note: the dba is a very powerful role be careful giving this out to anyone

1. Register the default listener to the instance

   # alter system set instance_name = P01;
   # alter system set service_names = P01;

Note: port 1521 is assumed, you do not need to update the listener.ora file

1. Create the listener (use Net Manager)
2. Create the Net Service name (Use Net Configuration Assistant)
3. Register the service with the instance

   # alter system set local_listener = 'P01_1522';
   # alter system register;

Note: you need to update the listener.ora and tnsnames.ora files

current_listener
displaymode
inbound_connection_timeout
log_status
log_file
log_directory
trc_level
trc_file
trc_directory
password     (only set is available)

# alter system set dispatchers="(protocol=TCP)(dispatchers=2)";
# alter system set dispatchers="(protocol=IPC)(dispatchers=2)";
# alter system set dispatchers="(protocol=TCP)(dispatchers=2)(pool=on)(tick=1)(connections=500)(sessions=1000)";

# alter system set max_dispatchers = 10;

pool - provides connection pooling
tick - number of 10 minute intervals of inactivity for a connection to be considered idle
connections - maximum number of concurrent sessions
sessions - maximum number of sessions per dispatcher
listener - address of listener to which PMON sends info to when listener is non-local and the local_listener is not set

# alter system set shared_servers = 5;
# alter system set shared_server_sessions = 2;
# alter system set max_shared_server = 20;

shared_servers - the system will initially start this many (default = 1)
shared_server_sessions - maximum number of shared server sessions
max_shared_server - shared servers will grow to this limit

Note: If at anytime you get ORA-00018 errors (Logging in or EM reports this error) this is because "shared_server_sessions" has been set too low

select a.spid dedicated_server, b.process clientpid
from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv('sessionid');

Note: remove the last line for all dedicated processes

select count(*) "Shared Server processes"
from v$shared_server
where status != 'QUIT';