Current session timezone

-- Get
select sessiontimezone from dual;
--Db timezone
select dbtimezone from dual;
-- Set
set ora_sdtz=db_tz
or
alter session set time_zone=dbtimezone;
--change db time_zone
16:51:23 SQL> alter database set time_zone='+01:00';

Database altered.
16:55:11 SQL> create table t_lt (id timestamp with local time zone);

Table created.

Elapsed: 00:00:00.64
16:55:29 SQL> alter database set time_zone='+01:00';

Database altered.

Elapsed: 00:00:00.06
16:55:31 SQL> alter database set time_zone='+02:00';

Database altered.
16:56:16 SQL> insert into t_lt select systimestamp from dual;

1 row created.

Elapsed: 00:00:00.07
16:56:33 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
16:56:35 SQL> alter database set time_zone='+01:00';
alter database set time_zone='+01:00'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

dbms_scheduler using as dbms_job.submit

exec dbms_scheduler.create_job(job_name => DBMS_SCHEDULER.GENERATE_JOB_NAME, job_type => 'plsql_block', job_action => 'begin raise_application_error(-20000, ''error_in_alert''); end;', start_date => sysdate, enabled => true);

ora-04031

orafaq

set lines 300
select to_char(to_number(v.KSPPSTVL), '999999999') min_to_go_to_reserved, to_char(s.request_failures, '99999999') ora04031
, to_char(s.last_failure_size, '999999999999') failure_size, trunc(s.free_space/1024/1024) free_reserved_m, max_free_size largest
, to_char(stat.bytes, '999999999') shs_pool_free
from x$ksppi n,
x$ksppsv v,
v$shared_pool_reserved s
, v$sgastat stat
where n.indx = v.indx
and n.ksppinm = '_shared_pool_reserved_min_alloc'
and stat.pool = 'shared pool'
and stat.name = 'free memory'
;

Issue if REQUEST_FAILURES > 0:
Shared pool - fragmentation? or low memory
if LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
else Shared pool reserved -
fragmentation?
if max_free_size < failure_size and free_space > failure_size
or low memory

-- Free shared memory hist
select t.BEGIN_INTERVAL_TIME, s.bytes
from DBA_HIST_SGASTAT s,
dba_hist_snapshot t
where s.pool = 'shared pool' and s.name = 'free memory'
and t.snap_id = s.snap_id
order by 1;

iot mapping table

-- create
ALTER TABLE countries move MAPPING TABLE;
-- drop
ALTER TABLE countries move noMAPPING;

rman datafile offline

sql "alter database datafile ''C:\ORACLE\PRODUCT\10.2.0\DB_3\DATABASE\UNNAMED00004'' offline";

Statspack doc 9i

Statspack

Rman troubleshooting

rman