Documents on Oracle support
ADR Different Methods to Create IPS Package [ID 738732.1]
set head on
set pagesize 10000
set linesize 300
col "Mo" format 999,999.99
select to_char(When, 'yyyy/mm/dd') "When",
sum(Mo) "Mo"
from
( select trunc(next_time) When,
sum((blocks+1)*block_size)/1024/1024 Mo
from
v$archived_log
where
creator='ARCH'
group by
trunc(next_time)
union all
select start_time + (level-1) When,
0 Mo
from
( select trunc(min(next_time)) start_time from v$archived_log where creator='ARCH' )
connect by start_time + (level-1) <= sysdate
)
group by When
order by 1;
set head on
set pagesize 10000
set linesize 300
col "Mo" format 999,999.99
select to_char(When, 'yyyy/mm/dd hh24:mi') "When",
sum(Mo) "Mo"
from
( select trunc(next_time, 'hh24') When,
sum((blocks+1)*block_size)/1024/1024 Mo
from
v$archived_log
where
creator='ARCH'
group by
trunc(next_time, 'hh24')
union all
select start_time + (level-1)/24 When,
0 Mo
from
( select trunc(min(next_time), 'hh24') start_time from v$archived_log where creator='ARCH' )
connect by start_time + (level-1)/24 <= sysdate
)
group by When
order by 1;
set head on
set pagesize 10000
set linesize 300
col "Mo" format 999,999.99
select "Day",
creator,
sum(bytes)/1024/1024 "Mo"
from
( select decode(creator, 'ARCH', 'Archiver process',
'FGRD', 'Foreground process',
'RMAN', 'Recovery Manager',
'SRMN', 'RMAN at standby',
'LGWR', 'Logwriter process',
creator) creator,
name,
to_char(next_time, 'yyyy/mm/dd') "Day",
(blocks+1)*block_size bytes
from
v$archived_log )
group by "Day", creator
order by 1;
set head on
set pagesize 10000
set linesize 300
col "Mo" format 999,999.99
select "Day",
creator,
sum(bytes)/1024/1024 "Mo"
from
( select decode(creator, 'ARCH', 'Archiver process',
'FGRD', 'Foreground process',
'RMAN', 'Recovery Manager',
'SRMN', 'RMAN at standby',
'LGWR', 'Logwriter process',
creator) creator,
name,
to_char(next_time, 'yyyy/mm/dd') "Day",
(blocks+1)*block_size bytes
from
v$archived_log )
group by "Day", creator
order by 1;
-- garde les archives logs a partir de la date
-- pour ignorer ce parametre : define before_date=""
define before_date="23/01/2012 00:00"
-- garde les archives logs dans les 'keep_last_hours' dernieres heures
-- pour ignorer ce parametre : define keep_last_hours=0
-- exemple : define keep_last_hours=48
define keep_last_hours=0
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
set head off
set pagesize 0
set linesize 300
set verify off
set feedback off
select 'rm '||name||' 2>/dev/null; #'||completion_time
from
v$archived_log
where
archived='YES'
and applied='YES'
and deleted='NO'
and status='A'
and ( completion_time < to_date('&before_date', 'dd/mm/yyyy hh24:mi') and length('&before_date') > 0
or completion_time < sysdate-&keep_last_hours/24 and &keep_last_hours > 0)
order by completion_time;
set pagesize 3000
set linesize 300
set feedback off
col name format a60
col thread# format 999999
col archived format a8
col applied format a7
col deleted format a7
col status format a11
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
select name,
sequence#,
thread#,
first_time,
next_time,
completion_time,
blocks,
archived,
applied,
deleted,
decode(status, 'A', 'Available', 'D', 'Deleted', 'U', 'Unavailable', 'X', 'Expired') status
from
v$archived_log
where
name is not null
and creator='ARCH'
order by 2;
set pagesize 3000
set linesize 300
set feedback off
col name format a60
col thread# format 999999
col deleted format a7
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
select name,
sequence#,
thread#,
first_time,
next_time,
completion_time,
blocks,
deleted
from
v$archived_log
where
name is not null
and creator='ARCH'
and applied='YES'
order by 2;
set pagesize 3000
set linesize 300
set feedback on
select thread#,
low_sequence#,
high_sequence#
from
v$archive_gap;
modifier les paramètres des snapshots@$ORACLE_HOME/rdbms/admin/awrrpt.sql @$ORACLE_HOME/rdbms/admin/awrrpti.sql
30 days <=> 30x24x60 = 43200outils Unix pour obtenir des mesures
val = (DEFAULT, MAXIMUM, N)
SQL> exec dbms_workload_repository.modify_snapshot_settings (interval => 10, retention => 43200, topnsql => 'val'); col snap_interval format a18 col retention format a18 SQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- ------------------ ------------------ ---------- 53643845 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT SQL> exec dbms_workload_repository.add_colored_sql('5rygsj4dbw6jt'); select * from wrm$_colored_sql;
Component UNIX Toolexporter/importer les AWRCPU sar, vmstat, mpstat, iostat Memory sar, vmstat Disk sar, iostat Network netstat
lister les snapshotsSQL> @?/rdbms/admin/awrextr.sql SQL> @?/rdbms/admin/awrload.sql
set pagesize 3000
set linesize 300
col begin_interval_time format a20
col end_interval_time format a20
select *
from
( select distinct snap_id,
to_char(begin_interval_time, 'yyyy/mm/dd hh24:mi') begin_interval_time,
to_char(end_interval_time, 'yyyy/mm/dd hh24:mi') end_interval_time
from
dba_hist_snapshot
)
order by 1;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- -------------------- --------------------
79555 2013/02/08 00:00 2013/02/08 00:30
79556 2013/02/08 00:30 2013/02/08 01:00
79557 2013/02/08 01:00 2013/02/08 01:30
79558 2013/02/08 01:30 2013/02/08 02:00
SGA - PGAset linesize 300 col db_unique_name format a18 col platform_name format a28 col open_mode format a12 select dbid, name, db_unique_name, platform_name, database_role, open_mode from v$database; DBID NAME DB_UNIQUE_NAME PLATFORM_NAME DATABASE_ROLE OPEN_MODE ---------- --------- -------------- ---------------------------- ---------------- ------------ 3447674908 XXX XXX AIX-Based Systems (64-bit) PRIMARY READ WRITE col force_logging format a13 select protection_mode, protection_level, force_logging, switchover_status, flashback_on from v$database; PROTECTION_MODE PROTECTION_LEVEL FORCE_LOGGING SWITCHOVER_STATUS FLASHBACK_ON -------------------- -------------------- ------------- -------------------- ------------------ MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE YES NOT ALLOWED NO col prior_resetlogs_time format a20 col controlfile_created format a19 alter session set nls_date_format='dd/mm/yyyy hh24:mi'; select controlfile_time, controlfile_created, resetlogs_time, prior_resetlogs_time, created, version_time from v$database; CONTROLFILE_TIME CONTROLFILE_CREATED RESETLOGS_TIME PRIOR_RESETLOGS_TIME CREATED VERSION_TIME ---------------- ------------------- ---------------- -------------------- ---------------- ---------------- 05/01/2012 09:46 25/11/2011 14:29 25/11/2011 14:29 25/11/2011 14:26 25/11/2011 14:29 25/11/2011 14:29 col "Max SCN" format 999999999999999 select to_char(scn_to_timestamp(max_scn), 'dd/mm/yyyy hh24:mi:ss') "SCN timestamp", max_scn "Max SCN", to_char(max_time_dp, 'dd/mm/yyyy hh24:mi:ss') "Max time_dp" from ( select max(time_dp) max_time_dp, max(scn) max_scn from smon_scn_time ); SCN timestamp Max SCN Max time_dp ------------------- ---------------- ------------------- 04/10/2012 10:08:55 106455707892 04/10/2012 08:08:55 set linesize 300 set pagesize 1000 set feedback off col properties format a60 select property_name||'='||property_value properties from database_properties order by 1; PROPERTIES ------------------------------------------------------------ DBTIMEZONE=+02:00 DEFAULT_EDITION=ORA$BASE DEFAULT_PERMANENT_TABLESPACE=SYSTEM DEFAULT_TBS_TYPE=SMALLFILE DEFAULT_TEMP_TABLESPACE=TEMPTAB DICT.BASE=2 ...
Distributed databasecol parallel format a8 col protection_level format a16 select parallel from v$instance; PARALLEL -------- YES select protection_level from v$database; PROTECTION_LEVEL ---------------- UNPROTECTED select * from dba_streams_administrator; no rows selected
SYSAUX occupantsset pagesize 300 set linesize 300 col occupant_name format a30 col schema_name format a20 col move_procedure format a40 col space_usage_mbytes format 999,999,999 col pct format 999.99 with A as ( select occupant_name, schema_name, move_procedure, space_usage_kbytes from v_$sysaux_occupants ) select occupant_name, schema_name, move_procedure, space_usage_kbytes/1024 space_usage_mbytes, space_usage_kbytes*100/B."_total_" pct from A, ( select sum(A.space_usage_kbytes) "_total_" from A ) B order by 4 desc; OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_MBYTES PCT ------------------------------ -------------------- ---------------------------------------- ------------------ ------- SM/AWR SYS 14,486 78.55 SM/OPTSTAT SYS 3,769 20.44 SM/ADVISOR SYS 148 .80 SM/OTHER SYS 15 .08 LOGMNR SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE 12 .07 SMON_SCN_TIME SYS 3 .02 SQL_MANAGEMENT_BASE SYS 2 .01 PL/SCOPE SYS 2 .01 AO SYS DBMS_AW.MOVE_AWMETA 1 .01 XSOQHIST SYS DBMS_XSOQ.OlapiMoveProc 1 .01 SQL> @?/rdbms/admin/awrinfo.sql
set head on
set pagesize 300
set linesize 300
col component format a40
col "Current size (Mo)" format 999,999.99
col "Min size (Mo)" format 999,999.99
col "Max size (Mo)" format 999,999.99
col last_oper_type format a16
select component,
current_size/1024/1024 "Current size (Mo)",
min_size/1024/1024 "Min size (Mo)",
max_size/1024/1024 "Max size (Mo)",
last_oper_type
from
v$sga_dynamic_components;
COMPONENT Current size (Mo) Min size (Mo) Max size (Mo) LAST_OPER_TYP
---------------------------------------------------------------- ----------------- ------------- ------------- -------------
shared pool 7,424.00 7,424.00 7,424.00 STATIC
large pool 512.00 512.00 512.00 STATIC
java pool 1,792.00 1,792.00 1,792.00 STATIC
streams pool .00 .00 .00 STATIC
DEFAULT buffer cache 21,504.00 21,504.00 21,504.00 INITIALIZING
KEEP buffer cache .00 .00 .00 STATIC
RECYCLE buffer cache .00 .00 .00 STATIC
DEFAULT 2K buffer cache .00 .00 .00 STATIC
DEFAULT 4K buffer cache .00 .00 .00 STATIC
DEFAULT 8K buffer cache .00 .00 .00 STATIC
DEFAULT 16K buffer cache .00 .00 .00 STATIC
DEFAULT 32K buffer cache .00 .00 .00 STATIC
Shared IO Pool .00 .00 .00 STATIC
ASM Buffer Cache .00 .00 .00 STATIC
select name, value from v$pgastat where name in ('maximum PGA allocated', 'total PGA allocated');
le registrealter session set nls_date_format='dd/mm/yyyy hh24:mi'; col value format 999999999999999 select 'CURRENT_SCN: ' name, CURRENT_SCN value, null when from v$database union all select 'RESETLOGS_CHANGE#: ' name, RESETLOGS_CHANGE# value, RESETLOGS_TIME when from v$database union all select 'PRIOR_RESETLOGS_CHANGE#: ' name, PRIOR_RESETLOGS_CHANGE# value, PRIOR_RESETLOGS_TIME when from v$database union all select 'CHECKPOINT_CHANGE#: ' name, CHECKPOINT_CHANGE# value, null when from v$database union all select 'ARCHIVE_CHANGE#: ' name, ARCHIVE_CHANGE# value, null when from v$database order by 2;
recréer le orainventory avec le OUIset pagesize 10000 set linesize 300 col comp_id format a10 col comp_name format a35 col version format a10 col status format a7 col modified format a20 col namespace format a10 col control format a10 col schema format a10 col procedure format a40 col startup format a10 col parent_id format a10 col other_schemas format a50 select comp_id, comp_name, version, status, modified, namespace, control, schema from dba_registry order by comp_id; COMP_ID COMP_NAME VERSION STATUS MODIFIED NAMESPACE CONTROL SCHEMA ---------- ----------------------------------- ---------- ------- -------------------- ---------- ---------- ---------- CATALOG Oracle Database Catalog Views 11.2.0.2.0 VALID 07-APR-2011 17:52:09 SERVER SYS SYS CATPROC Oracle Database Packages and Types 11.2.0.2.0 VALID 07-APR-2011 17:52:09 SERVER SYS SYS RAC Oracle Real Application Clusters 11.2.0.2.0 INVALID 12-APR-2011 15:48:10 SERVER SYS SYS select procedure, startup, parent_id, other_schemas from dba_registry order by comp_id; PROCEDURE STARTUP PARENT_ID OTHER_SCHEMAS ---------------------------------------- ---------- ---------- -------------------------------------------------- DBMS_REGISTRY_SYS.VALIDATE_CATALOG DBMS_REGISTRY_SYS.VALIDATE_CATPROC APPQOSSYS,DBSNMP,DIP,ORACLE_OCM,OUTLN,SYSTEM DBMS_CLUSTDB.VALIDATE set lines 180 pages 200 col action_time for a30 col action for a15 col namespace format a10 col version format a10 col bundle_series format a10 col comments for a55 select * from dba_registry_history order by action_time; ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SER COMMENTS ------------------------------ --------------- ---------- ---------- ---------- ---------- ------------------------------------------------------- [...] 15-JUN-13 05.47.27.054661 PM APPLY SERVER 11.2.0.3 4 PSU PSU 11.2.0.3.4 15-JUN-13 05.47.27.661828 PM APPLY SERVER 11.2.0.3 201301 SBP SBP 11.2.0.3.4 201301 containing CPUOct2012 $ cat $ORACLE_HOME/sapbundle/version.txt SBP 11.2.x.x.x 201yyy for GI
RAC :liste de tous les fichiers d'une basenode1=lune node2=jupiter grep NAME= `grep '^inventory_loc' /etc/oraInst.loc | sed "s/.*=//"`/ContentsXML/inventory.xml | \ sed "s/.*HOME NAME=//" | \ sed "s/ .*//" | read ORACLE_HOME_NAME echo "ORACLE_HOME_NAME=$ORACLE_HOME_NAME" $ORACLE_HOME/oui/bin/runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/ogi/grid/grid_oh" ORACLE_HOME_NAME="Oracle_Home_CRS" \ LOCAL_NODE="$node1" CLUSTER_NODES={$node1,$node2} CRS=true $ORACLE_HOME/oui/bin/runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=$ORACLE_HOME_NAME \ LOCAL_NODE="$node1" CLUSTER_NODES={$node1,$node2} $ORACLE_HOME/OPatch/opatch lsinventorynon RAC :grep NAME= `grep '^inventory_loc' /etc/oraInst.loc | sed "s/.*=//"`/ContentsXML/inventory.xml | \ sed "s/.*HOME NAME=//" | \ sed "s/ .*//" | read ORACLE_HOME_NAME echo "ORACLE_HOME_NAME=$ORACLE_HOME_NAME" $ORACLE_HOME/OPatch/opatch lsinventory $ORACLE_HOME/oui/bin/runInstaller -silent -ignoreSysPrereqs -detachHome ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=$ORACLE_HOME_NAME $ORACLE_HOME/oui/bin/runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=$ORACLE_HOME_NAME "CLUSTER_NODES={}" $ORACLE_HOME/OPatch/opatch lsinventoryRegénérer l’inventaire Oracle
segments rollbackset head off set pagesize 0 set linesize 300 set feedback off select A.file_name from (select name file_name from v$datafile union select name file_name from v$tempfile union select name file_name from v$controlfile union select member file_name from v$logfile) A union all select '$ORACLE_HOME/dbs/init$ORACLE_SID.ora' from dual union all select '$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora' from dual order by 1;
set pagesize 300
set linesize 300
col Mo format 999,999.99
select R.segment_name,
R.owner,
R.tablespace_name,
R.status,
S.bytes/1024/1024 Mo
from
dba_rollback_segs R,
dba_segments S
where
R.segment_name=S.segment_name;
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS MO
------------------------------ ------ ------------------------------ ---------------- -----------
SYSTEM SYS SYSTEM ONLINE .41
_SYSSMU1$ PUBLIC UNDO ONLINE 1.13
_SYSSMU2$ PUBLIC UNDO ONLINE .13
_SYSSMU3$ PUBLIC UNDO ONLINE 2.31
_SYSSMU4$ PUBLIC UNDO ONLINE .25
_SYSSMU5$ PUBLIC UNDO ONLINE .25
_SYSSMU6$ PUBLIC UNDO ONLINE .13
_SYSSMU7$ PUBLIC UNDO ONLINE .19
_SYSSMU8$ PUBLIC UNDO ONLINE .13
_SYSSMU9$ PUBLIC UNDO ONLINE .25
_SYSSMU10$ PUBLIC UNDO ONLINE .38
set linesize 300
column name format a20
column Mo format 999,999.99
break on report
compute sum of Mo on report
select name, sum(bytes)/1024/1024 Mo
from ( select 'DB Buffer Cache' name, bytes
from v$sgastat
where pool is null and name='db_block_buffers'
--
union all
--
select 'Shared Pool' name, bytes
from v$sgastat
where pool='shared pool'
--
union all
--
select 'Large Pool' name, bytes
from v$sgastat
where pool='large pool'
--
union all
--
select 'Java Pool' name, bytes
from v$sgastat
where pool='java pool'
--
union all
--
select 'Redo Log Buffer' name, bytes
from v$sgastat
where pool is null and name='log_buffer'
--
union all
--
select 'Fixed SGA' name, bytes
from v$sgastat
where pool is null and name='fixed_sga'
)
group by name;
NAME MO
-------------------- -----------
Shared Pool 600.00
Redo Log Buffer 6.05
Fixed SGA 1.94
-----------
sum 608.00
column area format a20 heading 'Shared Pool Areas'
prompt ----------------------------
prompt -- Detail du Shared Pool --
prompt ----------------------------
select 'Shared Pool' area, name, sum(bytes)
from
v$sgastat
where
pool='shared pool'
and name in ('library cache','dictionary cache','free memory','sql area')
group by name
--
union all
--
select 'Shared Pool' area, 'miscellaneous', sum(bytes)
from
v$sgastat
where
pool='shared pool'
and name not in ('library cache','dictionary cache','free memory','sql area')
group by pool
order by 3 desc;
Shared Pool miscellaneous 1,577,893,288
Shared Pool free memory 99,828,312
----------------
sum 1,677,721,600
define view_name=V$SESSION
set head off
set pagesize 0
set linesize 300
set verify off
select * from v$fixed_view_definition where view_name=upper('&view_name');
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
set pagesize 10000
set linesize 300
col username format a18
col account_status format a16
col default_tablespace format a18
col temp_tablespace format a15
col profile format a20
col external_name format a13
select username,
created,
account_status,
lock_date,
expiry_date,
default_tablespace,
temporary_tablespace temp_tablespace,
profile,
external_name
from
dba_users
order by 1;
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
set pagesize 10000
set linesize 300
select username,
max(logon_time) last_logon_time
from
gv$session
where
username is not null
group by username order by 1";
mot de passe expiré (EXPIRED)set head off set pagesize 0 set linesize 300 select 'alter user '||username||' account unlock;' from dba_users where account_status like '%LOCKED%';
les commandes de création d'un userset head off set pagesize 0 set linesize 300 select '-- '||username from dba_users where account_status like '%EXPIRED%' union all select distinct 'alter profile '||profile||' limit password_life_time unlimited;' from dba_users where account_status like '%EXPIRED%';
define schema=SAPR3
set head off
set pagesize 0
set long 128000
set verify off
set feedback off
prompt prompt creation du profile
select dbms_metadata.get_ddl('PROFILE', profile)||';' from ( select profile from dba_users where username=upper('&schema') );
prompt -- =============================================
prompt prompt creation du schema
select dbms_metadata.get_ddl('USER', upper('&schema'))||';' from dual;
prompt -- =============================================
prompt
prompt prompt les quotas
select 'alter user &schema quota '||Q.maxblocks*TN.block_size||' on '||T.name||';'
from dba_users U, tsq$ Q, v$tablespace T, dba_tablespaces TN
where
U.username=upper('&schema')
and U.user_id=Q.user#
and Q.ts#=T.ts#
and T.name=TN.tablespace_name
and Q.maxblocks > 0;
prompt -- =============================================
prompt
define sql_roles_to_schema="select granted_role from dba_role_privs where grantee=upper('&schema')"
define sql_roles_to_roles="select granted_role from role_role_privs where role in ( &sql_roles_to_schema )"
prompt prompt creation des roles pour le schema '&schema'
select dbms_metadata.get_ddl('ROLE', granted_role)||';' from ( &sql_roles_to_schema );
prompt -- =============================================
prompt
prompt prompt creation des roles lies aux roles pour le schema '&schema'
select dbms_metadata.get_ddl('ROLE', granted_role)||';' from ( &sql_roles_to_roles );
prompt -- =============================================
prompt
-- prompt prompt affectation des roles au schema
-- select 'grant '||granted_role||' to '||grantee||forward||';' from ( select grantee, granted_role, decode(admin_option, 'YES', ' with admin option') forward
from dba_role_privs where grantee=upper('&schema') );
-- prompt prompt affectation des roles lies aux roles pour le schema '&schema'
-- select 'grant '||granted_role||' to '||role||forward||';' from ( select role, granted_role, decode(admin_option, 'YES', ' with admin option') forward
from role_role_privs where role in ( &sql_roles_to_schema ) );
prompt prompt les privileges donnes au roles
prompt prompt les privileges donnes au roles des roles du schema
with A as (
select grantee,
granted_role privilege,
decode(admin_option, 'YES', ' with admin option') forward
from
dba_role_privs
union all
select grantee,
privilege privilege,
decode(admin_option, 'YES', ' with admin option') forward
from
dba_sys_privs
union all
select grantee,
privilege||' on "'||owner||'"."'||table_name||'"' privilege,
decode(grantable, 'YES', ' with grant option') forward
from
dba_tab_privs
union all
select grantee,
privilege||' on "'||owner||'"."'||table_name||'" ('||column_name||')' privilege,
decode(grantable, 'YES', ' with grant option') forward
from
dba_col_privs
)
select 'grant '||privilege||' to '||grantee||forward||';' from A
where
A.grantee in ( &sql_roles_to_schema )
or A.grantee in ( &sql_roles_to_roles )
or A.grantee = upper('&schema');
prompt -- =============================================
prompt
define schema=TOTO
break on grantee skip 1 noduplicates
set linesize 300
set pagesize 10000
set feedback off
set verify off
col grantee format a20
col privilege format a50
col grantable format a9
col other format a12
select grantee||(select ' [role]' from dba_roles where role=grantee) grantee,
privilege,
grantable,
other
from
( select grantee,
granted_role||' [role]' privilege,
admin_option grantable,
decode(default_role, 'YES', 'default role', null) other
from
dba_role_privs where grantee=upper('&schema')
union all
select grantee,
privilege||' [sys]' privilege,
admin_option grantable,
null other
from
dba_sys_privs where grantee=upper('&schema')
union all
select grantee,
privilege||' on "'||owner||'"."'||table_name||'"' privilege,
grantable,
decode(hierarchy, 'YES', 'hierarchy', null) other
from
dba_tab_privs where grantee=upper('&schema')
union all
select grantee,
privilege||' on "'||owner||'"."'||table_name||'" ('||column_name||')' privilege,
grantable,
null other
from
dba_col_privs where grantee=upper('&schema')
)
order by 1, 2;
set head off
set pagesize 10000
set linesize 300
col grantee format a30
col privilege format a60
col grantable format a9
col other format a12
select grantee||(select ' [role]' from dba_roles where role=grantee) grantee,
privilege,
grantable,
other
from
( select grantee,
granted_role||' [role]' privilege,
admin_option grantable,
decode(default_role, 'YES', 'default role', null) other
from
dba_role_privs
union all
select grantee,
privilege||' [sys]' privilege,
admin_option grantable,
null other
from
dba_sys_privs
union all
select grantee,
privilege||' on "'||owner||'"."'||table_name||'"' privilege,
grantable,
decode(hierarchy, 'YES', 'hierarchy', null) other
from
dba_tab_privs
union all
select grantee,
privilege||' on "'||owner||'"."'||table_name||'" ('||column_name||')' privilege,
grantable,
null other
from
dba_col_privs
)
order by 1, 2;
define obj_owner=USRMOH
define grantee=SAPR3
set pagesize 10000
set linesize 300
set verify off
col grantee format a30
col target format a40
col privilege format a10
select upper('&grantee') grantee,
owner||'.'||obj target,
op.priv||(select ' (-)' from dual where not exists ( select 1 from dba_tab_privs
where
grantee=upper('&grantee')
and owner=upper('&obj_owner')
and table_name=target.obj
and privilege=op.priv) ) privilege
from
( select owner, table_name obj from dba_tables where owner='&obj_owner'
union all
select owner, view_name obj from dba_views where owner='&obj_owner'
) target,
( select 'SELECT' priv from dual
union all
select 'INSERT' priv from dual
union all
select 'DELETE' priv from dual
union all
select 'UPDATE' priv from dual
) op
order by 1, 2, 3;
define synonym_owner=SAPR3
define table_owner=USER1
set pagesize 10000
set linesize 300
set verify off
col src format a40
col dest format a40
col db_link format a20
select owner||'.'||synonym_name src,
table_owner||'.'||table_name dest,
'DB_LINK='||db_link db_link
from
dba_synonyms
where
owner='&synonym_owner'
and table_owner='&table_owner'
union all
select '&synonym_owner'||'.'||obj src,
'&table_owner'||'.'||obj||' (-)' dest,
null db_link
from
( select owner, table_name obj from dba_tables where owner='&table_owner'
union all
select owner, view_name obj from dba_views where owner='&table_owner'
) target
where
not exists (select 1 from dba_synonyms where owner='&synonym_owner' and table_owner=target.owner and table_name=target.obj)
order by 1;
revoke RESOURCE pour un schéma en particulier :quotasdefine schema=SAPR3 set head off set pagesize 0 set verify off set feedback off select 'grant '||privilege||' to &schema;' from dba_sys_privs where grantee='RESOURCE' union all select 'revoke RESOURCE from &schema;'||chr(10)||'grant UNLIMITED TABLESPACE to &schema;' from dual;
revoke RESOURCE pour tous les schémas :set head off set pagesize 0 set feedback off select regexp_replace(cmd, 'XXXXXXXX', B.grantee) from ( select 'grant '||privilege||' to XXXXXXXX;' cmd from dba_sys_privs where grantee='RESOURCE' union all select 'revoke RESOURCE from XXXXXXXX;'||chr(10)||'grant UNLIMITED TABLESPACE to XXXXXXXX;' cmd from dual union all select '-- ========================================' from dual order by 1 ) A, ( select grantee from dba_role_privs where granted_role='RESOURCE' ) B;
set pagesize 300
set linesize 300
col username format a20
col name format a32
col blocks format 999,999,999
col maxblocks format a30
select U.username,
T.name,
Q.blocks,
decode(sign(Q.maxblocks), -1, 'UNLIMITED', to_char(Q.maxblocks, '999,999,999')) maxblocks
from
dba_users U,
tsq$ Q,
v$tablespace T
where
U.user_id=Q.user#
and Q.ts#=T.ts#
order by 1, 2, 3, 4;
USERNAME NAME BLOCKS MAXBLOCKS
--------------- ------------------------------ ---------- ----------
ADMIN_SECU TOOLS 136 12800
APPQOSSYS SYSAUX 0 -1
DBSNMP SYSAUX 328 0
OPS$HDSADM SYSTEM 8 0
OUTLN SYSTEM 72 0
RT USERS 0 -1
rem ================================================================================
set pagesize 300
set linesize 300
col username format a20
col tablespace_name format a32
col Mo format 999,999
col "Max Mo" format a10
col dropped format a7
select username,
tablespace_name,
bytes/1024/1024 Mo,
decode(sign(max_bytes), -1, 'UNLIMITED', to_char(max_bytes/1024/1024, '999,999')) "Max Mo",
dropped
from
dba_ts_quotas
order by 1;
USERNAME TABLESPACE_NAME MO Max Mo DROPPED
-------------------- ---------------------------------------- -------- --------- -------
ADMIN_SECU TOOLS 1 100 NO
APPQOSSYS SYSAUX 0 UNLIMITED NO
RT USERS 10 UNLIMITED NO
SAPSR3 PSAPSR3USR 1,811 UNLIMITED NO
SMDCHK USERS 0 UNLIMITED NO
remettre un mot de passe avec la valeur hachéeselect * from v$pwfile_users; USERNAME SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS TRUE TRUE FALSE
define schema=USER1
set head off
set pagesize 0
set linesize 300
set verify off
set feedback off
select 'alter user '||name||' identified by values '''||password||''';'
from
user$ where name=upper('&schema');
alter user USER1 identified by values 'B27E2DDEA7B03C2B';
define schema=SAPEDF
define to_schema=ARPEGE
set head off
set pagesize 0
set verify off
set feedback off
select 'grant select on '||owner||'."'||table_name||'" to &to_schema;'
from
dba_tables
where
owner=upper('&schema')
order by 1;
set head off
set pagesize 0
set linesize 300
col sid format a20
col sql_id format a13
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
select sid||','||serial#, sql_id, sql_text, 'username:'||username,
'logon:'||logon_time, 'sql_exec_start:'||sql_exec_start, 'sql_duration:'||sql_duration, 'status:'||status, 'server:'||server,
'os process:'||spid, 'machine:'||machine, 'terminal:'||terminal, 'program:'||program
from
( with S as ( select S0.sid, S0.serial#, S0.username, S0.logon_time, S0.sql_exec_start, S0.status, S0.server, S0.machine,
S0.terminal, S0.program, S0.paddr, P0.spid, S0.sql_address
from
v$session S0, v$process P0
where
S0.username is not null
and S0.paddr=P0.addr
and S0.sid != sys_context('USERENV','SID')),
A as ( select sql_id, sql_text, address
from
v$sql
where
address in ( select distinct sql_address from v$session ))
select distinct S.sid, S.serial#, A.sql_id, A.sql_text, S.username, S.logon_time, S.sql_exec_start, (B.now-S.sql_exec_start)*24*3600 sql_duration,
S.status, S.server, S.spid, S.machine, S.terminal, S.program
from
S, A, ( select sysdate now from dual ) B
where
A.address=S.sql_address
)
order by sql_duration;
set head off
set pagesize 0
set linesize 300
col sid format a20
col sql_id format a13
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
select sid||','||serial#, sql_id, sql_text, 'username:'||username,
'logon:'||logon_time, 'sql_exec_start:'||sql_exec_start, 'sql_duration:'||sql_duration, 'status:'||status, 'server:'||server,
'os process:'||spid, 'machine:'||machine, 'terminal:'||terminal, 'program:'||program
from
( with S as ( select S0.sid, S0.serial#, S0.username, S0.logon_time, S0.sql_exec_start, S0.status, S0.server, S0.machine,
S0.terminal, S0.program, S0.paddr, P0.spid, S0.sql_address
from
v$session S0, v$process P0
where
S0.username is not null
and S0.status='ACTIVE'
and S0.paddr=P0.addr
and S0.sid != sys_context('USERENV','SID')),
A as ( select sql_id, sql_text, address
from
v$sql
where
address in ( select distinct sql_address from v$session ))
select distinct S.sid, S.serial#, A.sql_id, A.sql_text, S.username, S.logon_time, S.sql_exec_start, (B.now-S.sql_exec_start)*24*3600 sql_duration,
S.status, S.server, S.spid, S.machine, S.terminal, S.program
from
S, A, ( select sysdate now from dual ) B
where
A.address=S.sql_address
)
order by sql_duration;
set head off
set pagesize 0
select 'sid: '||sid||','||serial#, sql_id, 'username:'||username,
'logon:'||to_char(logon_time, 'dd/mm/yyyy hh24:mi:ss'), 'status:'||status, 'server:'||server,
'os process:'||spid, 'machine:'||machine, 'terminal:'||terminal, 'program:'||program
from
( with S as ( select S0.sid, S0.serial#, S0.username, S0.logon_time, S0.status, S0.server, S0.machine,
S0.terminal, S0.program, S0.paddr, P0.spid, S0.sql_address
from
v$session S0, v$process P0
where
S0.username is not null
and S0.paddr=P0.addr
and S0.sid != sys_context('USERENV','SID')),
A as ( select sql_id,
address
from
v$sql
where
address in (select distinct sql_address from v$session ) )
select distinct s.sid, s.serial#, a.sql_id, s.username, s.logon_time, s.status, s.server,
s.spid, s.machine, s.terminal, s.program
from
S, A
where
A.address=S.sql_address
)
order by logon_time;
set head off
set pagesize 0
select 'sid: '||sid||','||serial#, sql_id, 'username:'||username,
'logon:'||to_char(logon_time, 'dd/mm/yyyy hh24:mi:ss'), 'status:'||status, 'server:'||server,
'os process:'||spid, 'machine:'||machine, 'terminal:'||terminal, 'program:'||program
from
( with S as ( select S0.sid, S0.serial#, S0.username, S0.logon_time, S0.status, S0.server, S0.machine,
S0.terminal, S0.program, S0.paddr, P0.spid, S0.sql_address
from
v$session S0, v$process P0
where
S0.username is not null
and S0.status!='INACTIVE'
and S0.paddr=P0.addr
and S0.sid != sys_context('USERENV','SID')),
A as ( select sql_id,
address
from
v$sql
where
address in (select distinct sql_address from v$session ) )
select distinct s.sid, s.serial#, a.sql_id, s.username, s.logon_time, s.status, s.server,
s.spid, s.machine, s.terminal, s.program
from
S, A
where
A.address=S.sql_address
)
order by logon_time;
define day="2011/02/16"
set pagesize 10000
set linesize 3000
set verify off
col sess format a14
col blocking_sess format a14
col event format a30
col wait_class format a30
col program format a40
select to_char(S.end_interval_time, 'yyyy/mm/dd hh24:mi') end_interval_time,
A.session_id||','||A.session_serial# sess,
A.sql_id,
A.blocking_session||','||A.blocking_session_serial# blocking_sess,
A.blocking_session_status status,
A.event,
A.wait_class,
A.wait_time,
A.time_waited,
A.current_obj#,
A.program,
A.module
from
dba_hist_snapshot S,
dba_hist_active_sess_history A
where
A.snap_id=S.snap_id
and to_char(S.end_interval_time, 'yyyy/mm/dd hh24:mi') like '&day%'
and A.wait_class in ('Application', 'Concurrency')
order by 1;
define day="2011/02/16"
define session_id=203
define session_serial=380
set pagesize 10000
set linesize 3000
set verify off
col sess format a14
col blocking_sess format a14
col event format a30
col wait_class format a30
col program format a40
select to_char(S.end_interval_time, 'yyyy/mm/dd hh24:mi') end_interval_time,
A.session_id||','||A.session_serial# sess,
A.sql_id,
A.blocking_session||','||A.blocking_session_serial# blocking_sess,
A.blocking_session_status status,
A.event,
A.wait_class,
A.wait_time,
A.time_waited,
A.current_obj#,
A.program,
A.module
from
dba_hist_snapshot S,
dba_hist_active_sess_history A
where
A.snap_id=S.snap_id
and to_char(S.end_interval_time, 'yyyy/mm/dd hh24:mi') like '&day%'
and ( (A.session_id=&session_id and A.session_serial#=&session_serial)
or (A.blocking_session=&session_id and A.blocking_session_serial#=&session_serial))
order by 1;
define list_users="'USER1', 'USER2'"
set pagesize 10000
set linesize 300
set verify off
col sample_time format a25
col sid_serial format a10
col program format a25
col module format a25
col username format a14
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
select A.sample_time,
A.session_id||','||A.session_serial# sid_serial,
U.username,
A.program,
A.module
from
dba_hist_active_sess_history A,
dba_users U
where
U.username in (&list_users)
and U.user_id=A.user_id
order by sample_time;
set pagesize 300
set linesize 300
col sid_serial format a10
col "Size (Mo)" format 999,999.99
select B.tablespace,
B.blocks*P.value/1024/1024 "Size (Mo)",
A.sid||','||A.serial# sid_serial,
A.username,
A.program
from
v$session A,
v$sort_usage B,
v$parameter P
where
P.name = 'db_block_size'
and A.saddr = B.session_addr
order by 1, 2;
set linesize 300
set pagesize 1000
col username format a10
col event format a40
col total_waits format 999,999,999
col total_timeouts format 999,999,999
col time_waited format a11
col average_wait format a12
col max_wait format a8
col program format a38
select b.sid,
b.username,
a.event,
a.total_waits,
a.total_timeouts,
to_char(to_date('01-01-2000', 'dd-mm-yyyy')+a.time_waited/100/3600/24, 'hh24:mi:ss') time_waited,
to_char(to_date('01-01-2000', 'dd-mm-yyyy')+a.average_wait/100/3600/24, 'hh24:mi:ss') average_wait,
to_char(to_date('01-01-2000', 'dd-mm-yyyy')+a.max_wait/100/3600/24, 'hh24:mi:ss') max_wait,
b.program
from
v$session_event a,
v$session b
where
B.sid = A.sid + 1
and B.username is not null
and A.wait_class != 'Idle'
order by A.time_waited, 1;
SID USERNAME EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT PROGRAM
---------- ---------- ---------------------------------------- ------------ -------------- ----------- ------------ -------- --------------------------------------
4 SYS Disk file operations I/O 4 0 00:00:00 00:00:00 00:00:00 oraagent.bin@saturne (TNS V1-V3)
232 SYS latch: row cache objects 10 0 00:00:00 00:00:00 00:00:00 sqlplus@saturne (TNS V1-V3)
4 SYS events in waitclass Other 2 0 00:00:00 00:00:00 00:00:00 oraagent.bin@saturne (TNS V1-V3)
232 SYS libcache interrupt action by LCK 440,324 0 00:00:00 00:00:00 00:00:00 sqlplus@saturne (TNS V1-V3)
232 SYS events in waitclass Other 1,108,657 404,551 00:00:13 00:00:00 00:00:00 sqlplus@saturne (TNS V1-V3)
set linesize 300
set pagesize 300
col event format a30
col wait_class format a20
select sid,
event,
wait_class,
wait_time,
seconds_in_wait,
state
from
v$session_wait
where
wait_class != 'Idle'
order by seconds_in_wait;
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ------------------------------ -------------------- ---------- --------------- -------------------
232 SQL*Net message to client Network -1 0 WAITED SHORT TIME
tracer la session couranteSQL> alter session set timed_statistics=true; SQL> alter session set max_dump_file_size=unlimited; SQL> alter session set events '10046 trace name context forever, level 8';désactiver la trace de la session couranteSQL> alter session set events '10046 trace name context off';tracer une autre sessionSQL> exec sys.dbms_system.set_bool_param_in_session(<sid>, <serial#>, 'timed_statistics', true); SQL> exec sys.dbms_system.set_int_param_in_session(sid, serial#, 'max_dump_file_size', 2147483647); SQL> exec sys.dbms_system.set_ev(sid, serial#, 10046, 8, '');désactiver la trace de la session remoteSQL> exec sys.dbms_system.set_ev(sid, serial#, 10046, 0, '');
set pagesize 10000
set feedback off
set linesize 300
col "Tablespace" format a20
col "Total Go" format 9,999.99
col "Free Go" format 9,999.99
col "% full" format 999.99
col "Status" format a7
col "Extent management" format a17
col "Segment space management" format a24
col "Allocation type" format a15
select df.tablespace_name "Tablespace",
df.total/1024/1024/1024 "Total Go",
decode(ds.free, NULL, 0, ds.free/1024/1024/1024) "Free Go",
100*(1-ds.free/df.total) "% full",
df.cnt "Count",
t.status "Status",
t.extent_management "Extent management",
t.segment_space_management "Segment space management",
t.allocation_type "Allocation type"
from
(select tablespace_name, sum(bytes) total, count(*) cnt from dba_data_files group by tablespace_name) df,
(select tablespace_name, sum(nvl(bytes, 0)) free from dba_free_space group by tablespace_name) ds,
dba_tablespaces t
where
t.tablespace_name=df.tablespace_name
and df.tablespace_name=ds.tablespace_name (+)
--
union all
--
-- Temporary tablespace
-----------------------
select tmp.tablespace_name||' (T)' "Tablespace",
tmp.total/1024/1024/1024 "Total Go",
tmp.free/1024/1024/1024 "Free Go",
100*(1-free/total) "% full",
tfiles.cnt "Count",
t.status "Status",
t.extent_management "Extent management",
t.segment_space_management "Segment space management",
t.allocation_type "Allocation type"
from
( select tablespace_name,
sum(bytes_used+bytes_free) total,
sum(bytes_free) free
from
v$temp_space_header group by tablespace_name ) tmp,
dba_tablespaces t,
( select tablespace_name, count(*) cnt from dba_temp_files group by tablespace_name ) tfiles
where
t.tablespace_name=tmp.tablespace_name
and t.tablespace_name=tfiles.tablespace_name
order by 1;
define motif=""
define factor=1/1024/1024/1024
define unit=Go
set head on
set pagesize 10000
set linesize 300
set feedback off
set verify off
col tablespace_name format a22
col file_name format a62
col "Size &unit" format 9,999.99
col "Max size &unit" format 9,999.99
col "Remains &unit" format 9,999.99
break on tablespace_name skip 1 noduplicates
select tablespace_name,
file_name,
go "Size &unit",
autoextensible,
decode(autoextensible, 'YES', max_go, null) "Max size &unit",
decode(autoextensible, 'YES', max_go-go, null) "Remains &unit"
from
( select tablespace_name,
file_name,
autoextensible,
bytes*&factor go,
decode(autoextensible, 'YES', case when maxbytes > bytes then maxbytes else bytes end, bytes)*&factor max_go,
regexp_replace(file_name, '^/oracle/.../sapdata\d{1,2}/', '') short_file_name
from
dba_data_files
where
file_name like '%&motif%'
union all
select tablespace_name||' (T)',
file_name,
autoextensible,
bytes*&factor go,
decode(autoextensible, 'YES', case when maxbytes > bytes then maxbytes else bytes end, bytes)*&factor max_go,
regexp_replace(file_name, '^/oracle/.../sapdata\d{1,2}/', '') short_file_name
from
dba_temp_files
where
file_name like '%&motif%' )
order by 1, short_file_name;
// -----------------------------------------------------------------------------------------
// -----------------------------------------------------------------------------------------
set linesize 300
set pagesize 10000
col "Tablespace" format a30
col "Block size" format 99,999
col "Status" format a7
col "Contents" format a9
col "Force logging" format a13
col "Extent management" format a17
col "Allocation type" format a15
col "Segment space management" format a24
select tablespace_name "Tablespace",
block_size "Block size",
status "Status",
contents "Contents",
logging "Logging",
force_logging "Force logging",
extent_management "Extent management",
allocation_type "Allocation type",
segment_space_management "Segment space management"
from
dba_tablespaces
order by 1;
// -----------------------------------------------------------------------------------------
// -----------------------------------------------------------------------------------------
set linesize 300
set pagesize 10000
col "Tablespace" format a30
col "Initial extent Mo" format 999,999.99
col "Next extent Mo" format 999,999.99
col "Max extents" format 999,999,999,999
col "Min extlen" format 999,999,999,999
col "Min extlen Mo" format 999,999.99
select tablespace_name "Tablespace",
initial_extent/1024/1024 "Initial extent Mo",
next_extent/1024/1024 "Next extent Mo",
min_extents "Min extents",
max_extents "Max extents",
pct_increase "% increase",
min_extlen/1024/1024 "Min extlen Mo"
from
dba_tablespaces
order by 1;
define from_date="12/07/2011 15:00"
define to_date="19/07/2011 18:00"
define tablespaces="'TEMPTAB','PSAPSTABD'"
alter session set nls_date_format='dd/mm/yyyy hh24:mi';
set head off
set pagesize 0
set linesize 300
set verify off
set feedback off
select 'Tablespace;Date;Used size Mo;Size Mo;Max size Mo;% full;% full (max size)' from dual
union all
select regexp_replace(line, '\.', ',')
from
( select T.name
||';'||S.end_interval_time
||';'||trunc(H.tablespace_usedsize*TN.block_size/1024/1024, 2)
||';'||trunc(H.tablespace_size*TN.block_size/1024/1024, 2)
||';'||trunc(H.tablespace_maxsize*TN.block_size/1024/1024, 2)
||';'||trunc(H.tablespace_usedsize/H.tablespace_size*100, 2)
||';'||trunc(H.tablespace_usedsize/H.tablespace_maxsize*100, 2) line
from
( select snap_id,
to_date(to_char(end_interval_time, 'yyyy/mon/dd hh24:mi'), 'yyyy/mon/dd hh24:mi') end_interval_time
from
dba_hist_snapshot
where
end_interval_time >= to_timestamp('&from_date', 'dd/mm/yyyyhh24:mi')
and end_interval_time <= to_timestamp('&to_date', 'dd/mm/yyyyhh24:mi') ) S,
dba_hist_tbspc_space_usage H,
( select ts#, name from v$tablespace where name in (&tablespaces) ) T,
( select block_size, tablespace_name from dba_tablespaces where tablespace_name in (&tablespaces) ) TN
where
S.snap_id=H.snap_id
and H.tablespace_id=T.ts#
and T.name=TN.tablespace_name
order by T.name, H.snap_id );
define day="20/10/2011 10:00"
col "DB size (Mo)" format 999,999,999.99
col "DB used size (Mo)" format 999,999,999.99
col "DB free size (Mo)" format 999,999,999.99
set verify off
set pagesize 10000
select end_interval_time,
"DB size (Mo)",
"DB used size (Mo)",
"DB free size (Mo)"
from
( select end_interval_time,
snap_id,
sum(tablespace_size) * 8192/1024/1024/1024 "DB size (Mo)",
sum(tablespace_usedsize) * 8192/1024/1024/1024 "DB used size (Mo)",
sum(tablespace_size-tablespace_usedsize) * 8192/1024/1024/1024 "DB free size (Mo)"
from
( select D.snap_id,
D.tablespace_size,
D.tablespace_usedsize,
to_char(S.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') end_interval_time
from
dba_hist_tbspc_space_usage D,
dba_hist_snapshot S
where
D.snap_id = S.snap_id )
where
end_interval_time like '&day%'
group by end_interval_time, snap_id
order by 2
);
define day="20/10/2011 10:00"
define tablespace=PSAPUNDO
define div=1/1024/1024
set verify off
set pagesize 10000
set linesize 300
col "size (Mo)" format 999,999,999.99
col "used size (Mo)" format 999,999,999.99
col "max size (Mo)" format 999,999,999.99
col "free size (Mo)" format 999,999,999.99
with A as ( select name tablespace_name,
ts# tablespace_id
from v$tablespace
where name=upper('&tablespace') ),
B as ( select tablespace_name,
block_size*&div factor
from dba_tablespaces
where tablespace_name=upper('&tablespace') ),
C as ( select snap_id,
to_char(end_interval_time, 'dd/mm/yyyy hh24:mi:ss') end_interval_time
from dba_hist_snapshot
where to_char(end_interval_time, 'dd/mm/yyyy hh24:mi:ss') like '&day%' )
select B.tablespace_name,
C.end_interval_time "end interval time",
D.tablespace_size *factor "size (Mo)",
D.tablespace_usedsize *factor "used size (Mo)",
(D.tablespace_size-D.tablespace_usedsize)*factor "free size (Mo)",
D.tablespace_maxsize *factor "max size (Mo)"
from
A, B, C, dba_hist_tbspc_space_usage D
where
D.snap_id = C.snap_id
and D.tablespace_id = A.tablespace_id
and A.tablespace_name = B.tablespace_name;
define when="20/10/2011 10:00"
define div=1/1024/1024
col "size (Mo)" format 999,999,999.99
col "used size (Mo)" format 999,999,999.99
col "max size (Mo)" format 999,999,999.99
col "free size (Mo)" format 999,999,999.99
set verify off
set pagesize 10000
set linesize 300
with A as ( select name tablespace_name,
ts# tablespace_id
from v$tablespace ),
B as ( select tablespace_name,
block_size*&div factor
from dba_tablespaces ),
C as ( select snap_id,
to_char(end_interval_time, 'dd/mm/yyyy hh24:mi:ss') end_interval_time
from dba_hist_snapshot
where to_char(end_interval_time, 'dd/mm/yyyy hh24:mi:ss') like '&when%' )
select C.end_interval_time "end interval time",
B.tablespace_name,
D.tablespace_size *factor "size (Mo)",
D.tablespace_usedsize *factor "used size (Mo)",
(D.tablespace_size-D.tablespace_usedsize)*factor "free size (Mo)",
D.tablespace_maxsize *factor "max size (Mo)"
from
A, B, C, dba_hist_tbspc_space_usage D
where
D.snap_id = C.snap_id
and D.tablespace_id = A.tablespace_id
and A.tablespace_name = B.tablespace_name
order by 3;
set head off
set pagesize 0
set linesize 300
set verify off
spool shrink.sql
prompt spool shrink.log
select 'alter database datafile '''||file_name||''' resize '||ceil(nvl(hwm,1)*block_size/1024/1024)||'M;' || chr(10) ||
'-- '||tablespace_name||' '||file_name||' '||ceil(bytes/1024/1024)||'M'
from
dba_data_files A,
( select file_id,
max(block_id+blocks-1) hwm
from
dba_extents
group by file_id ) B,
( select value block_size from v$parameter where name = 'db_block_size' ) C
where
A.tablespace_name not in ('SYSTEM', 'SYSAUX', 'TOOLS', 'STATSPACK')
and A.tablespace_name not like '%TEMP%'
and A.tablespace_name not like '%UNDO%'
and A.file_id = B.file_id (+)
and ceil(blocks*block_size/1024/1024) - ceil(nvl(hwm,1)*block_size/1024/1024) > 0;
prompt spool off
spool off
set pagesize 10000
set linesize 300
col file_name format a60
select tablespace_name,
file_name,
A.cnt
from
dba_data_files,
(select regexp_substr(file_name,'[^/]+[^/]$') fname,
count(*) cnt
from
dba_data_files
group by regexp_substr(file_name,'[^/]+[^/]$')
having count(*) > 1) A
where
file_name like '/%/'||A.fname
order by 1, 2;
define from_date="13/01/2012 12:00"
define to_date="31/01/2012 23:00"
define tablespace_name=PSAPUNDO
alter session set nls_date_format='dd/mm/yyyy hh24:mi';
set head off
set pagesize 0
set linesize 300
set verify off
set feedback off
col "Mo" format 999,999,999.99
prompt &tablespace_name
select end_interval_time,
tablespace_size/1024/1024 "Mo"
from
( select end_interval_time,
tablespace_size,
lag(tablespace_size, 1) over (order by end_interval_time) previous_tablespace_size
from
( select to_date(to_char(end_interval_time, 'yyyy/mon/dd hh24:mi'), 'yyyy/mon/dd hh24:mi') end_interval_time,
tablespace_size*8192 tablespace_size
from
dba_hist_snapshot S,
dba_hist_tbspc_space_usage T
where
S.end_interval_time >= to_timestamp('&from_date', 'dd/mm/yyyyhh24:mi')
and S.end_interval_time <= to_timestamp('&to_date', 'dd/mm/yyyyhh24:mi')
and S.snap_id=T.snap_id
and T.tablespace_id=(select ts# from v$tablespace where name=upper('&tablespace_name'))
order by 1
)
)
where
tablespace_size != previous_tablespace_size
or previous_tablespace_size is null;
define block_size=8192
set head off
set pagesize 0
set linesize 300
set feedback off
set verify off
select 'dbv file='||F.name||' blocksize=&block_size logfile=dbv_'||F.file#||'_'||T.name||'.log'
from
( select case
when file# < 10 then '000'||file#
when file# < 100 then '00'||file#
when file# < 1000 then '0'||file#
else ''||file#
end file#,
name,
ts#
from
v$datafile ) F,
v$tablespace T,
v$database DB
where
T.ts#=F.ts#
order by F.file#;
exemple de création de triggerdefine owner=xxxx set verify off select 'alter trigger '||owner||'.'||TRIGGER_NAME||' disable;' from dba_triggers where OWNER='&owner';
create or replace trigger scott.trig_e1
after insert or update on scott.table1
for each row
declare iexists integer;
begin
if inserting then
insert into scott.zfkkvkp_sensible (vkont,gpart,erdat,mgrup,gsber) values
(:new.vkont,:new.gpart,:new.erdat,:new.mgrup,:new.gsber);
end if;
if updating then
if :new.mgrup <> :old.mgrup then
select count(*) into iexists from scott.zfkkvkp_sensible where vkont =:new.vkont and gpart =:new.gpart;
if iexists = 0 then
insert into scott.zfkkvkp_sensible (vkont,gpart,erdat,mgrup,gsber) values
(:new.vkont,:new.gpart,:new.erdat,:new.mgrup,:new.gsber);
else
update scott.zfkkvkp_sensible set erdat=:new.erdat,mgrup=:new.mgrup,gsber=:new.gsber
where
gpart=:new.gpart
and vkont=:new.vkont;
end if;
end if;
end if;
end;
set pagesize 10000
set linesize 300
break on profile skip 1
col profile format a20
col limit format a20
select profile,
resource_name,
resource_type,
limit
from
dba_profiles
order by 1, 2;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter system set db_flashback_retention_target = 1440; (minutes)
SQL> alter system set db_recovery_file_dest_size=2147483648 (octets);
SQL> alter system set db_recovery_file_dest='/oracle/SID/oraarch/flashBack';
SQL> alter database flashback on / off;
SQL> select flashback_on from v$database;
YES
SQL> alter tablespace example flashback on / off;
SQL> select flashback_on from v$tablespace where name='example';
YES
SQL> flashback table employee to timestamp(to_timestamp('09-NOV-11 21:30', 'DD-MON-YY HH24:MI'));
SQL> flashback table employee to before drop;
SQL> flashback database to timestamp sysdate-(1/24);
Flashback New Features and Enhancements in Oracle Database 10g
How far back can we flashback to time ?utilisationselect to_char(oldest_flashback_time, 'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time" from v$flashback_database_log; Oldest Flashback Time --------------------- 05-jul-2009 22:53:07How far back can we flashback to SCN ?col oldest_flashback_scn format 99999999999999999999999999 select oldest_flashback_scn "Oldest Flashback SCN" from v$flashback_database_log; OLDEST_FLASHBACK_SCN -------------------- 15321928761Flashback area usageselect * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES --------- ------------------ ------------------------- --------------- CONTROLFILE 0col "Flashback location" format a40 col "Space limit (Mo)" format 999,999.99 col "Space used (Mo)" format 999,999.99 select name "Flashback location", space_limit/1024/1024 "Space limit (Mo)", space_used/1024/1024 "Space used (Mo)" from v$recovery_file_dest;
delete from emp; select * from emp as of timestamp(sysdate - 5/1440);drop table emp; flashback table emp to before drop;
set head on
set pagesize 10000
set linesize 300
col "Job" format a35
col "State" format a9
col "Enabled" format a7
col "Runs" format 9,999
col "Failures" format 9,999
col "Last run duration" format a17
col "Next run date" format a19
col "Program name" format a40
select owner||'.'||job_name "Job",
state "State",
enabled "Enabled",
run_count "Runs",
failure_count "Failures",
case when to_number(extract(day from last_run_duration)) = 0 or last_run_duration is null
then to_char(to_date(trunc(to_number(extract(second from last_run_duration)) +
to_number(extract(minute from last_run_duration))*60 +
to_number(extract(hour from last_run_duration))*60*60 +
to_number(extract(day from last_run_duration))*60*60*24), 'sssss'), 'hh24:mi:ss')
else '+'||extract(day from last_run_duration)||' '||to_char(to_date(trunc(
to_number(extract(second from last_run_duration)) +
to_number(extract(minute from last_run_duration))*60 +
to_number(extract(hour from last_run_duration))*60*60 +
to_number(extract(day from last_run_duration))*60*60*24), 'sssss'), 'hh24:mi:ss')
end "Last run duration",
to_char(to_date(to_char(next_run_date, 'yyyy/mon/dd hh24:mi'), 'yyyy/mon/dd hh24:mi'), 'dd/mm/yyyy hh24:mi:ss') "Next run date",
program_name "Program name"
from
dba_scheduler_jobs
order by 1;
define job=BSLN_MAINTAIN_STATS_JOB
set head on
set verify off
set linesize 300
col "Job" format a35
col "Start date" format a21
col "End date" format a21
col "Repeat interval" format a19
col "Restartable" format a11
col "Program" format a30
col "Schedule name" format a30
col "Schedule type" format a13
col "Schedule limit" format a14
col "Job action" format a14
select owner||'.'||job_name "Job",
to_char(start_date, 'dd/mm/yyyy hh24:mi:ss') "Start date",
to_char(end_date, 'dd/mm/yyyy hh24:mi:ss') "End date",
to_char(last_start_date, 'dd/mm/yyyy hh24:mi:ss') "Last start date",
to_char(next_run_date, 'dd/mm/yyyy hh24:mi:ss') "Next run date",
repeat_interval "Repeat interval",
restartable "Restartable"
from
dba_scheduler_jobs
where
job_name='&job';
select owner||'.'||job_name "Job",
decode(program_name, null, '', program_owner||'.'||program_name) "Program",
schedule_name "Schedule name",
schedule_type "Schedule type",
to_char(schedule_limit) "Schedule limit",
job_type "Job type",
job_action "Job action"
from
dba_scheduler_jobs
where
job_name='&job';
define job=BSLN_MAINTAIN_STATS_JOB
set head on
set verify off
set pagesize 300
set linesize 300
col job_name format a30
select job_name,
to_char(log_date, 'yyyy/mm/dd hh24:mi') log_date,
status
from
dba_scheduler_job_log
where
job_name='&job'
order by log_date;
define program=BSLN_MAINTAIN_STATS_PROG set verify off set head off set linesize 300 col job_name format a30 select program_action||chr(10)||'/' from dba_scheduler_programs where program_name='&program';
set pagesize 10000
col object format a30
col cnt format 999,999,999
select O.owner||'.'||O.object_name object,
count(*) cnt
from
gv$locked_object L,
dba_objects O
where
L.object_id = O.object_id
group by O.owner||'.'||O.object_name
order by 2;
set head on
set pagesize 10000
set linesize 300
col "XIDUSN XIDSLOT XIDSQN" format a22
col type format a22
col lmode format a14
col request format a14
select username,
L.sid,
'('||trunc(id1/power(2,16))||', '||bitand(id1,to_number('ffff','xxxx'))||', '||id2||')' "XIDUSN XIDSLOT XIDSQN",
decode(L.type, 'TM', 'DML enqueue', 'TX', 'Transaction enqueue', 'UL', 'User supplied', L.type) type,
decode(lmode, 0, 'none', 1, 'null (NULL)', 2, 'row-S (SS)', 3, 'row-X (SX)', 4, 'share (S)', 5, 'S/Row-X (SSX)', 6, 'exclusive (X)', lmode) lmode,
decode(request, 0, 'none', 1, 'null (NULL)', 2, 'row-S (SS)', 3, 'row-X (SX)', 4, 'share (S)', 5, 'S/Row-X (SSX)', 6, 'exclusive (X)', request) request
from
v$lock L, v$session S
where
L.type = 'TX'
and L.sid = S.sid
and username is not null
order by trunc(id1/power(2,16)), bitand(id1,to_number('ffff','xxxx'))+0, id2, L.sid;
set head on
set pagesize 10000
set linesize 300
col "XIDUSN XIDSLOT XIDSQN" format a22
col type format a22
col lmode format a14
col request format a14
select username,
L.sid,
'('||trunc(id1/power(2,16))||', '||bitand(id1,to_number('ffff','xxxx'))||', '||id2||')' "XIDUSN XIDSLOT XIDSQN",
decode(L.type, 'TM', 'DML enqueue', 'TX', 'Transaction enqueue', 'UL', 'User supplied', L.type) type,
decode(lmode, 0, 'none', 1, 'null (NULL)', 2, 'row-S (SS)', 3, 'row-X (SX)', 4, 'share (S)', 5, 'S/Row-X (SSX)', 6, 'exclusive (X)', lmode) lmode,
decode(request, 0, 'none', 1, 'null (NULL)', 2, 'row-S (SS)', 3, 'row-X (SX)', 4, 'share (S)', 5, 'S/Row-X (SSX)', 6, 'exclusive (X)', request) request
from
v$lock L, v$session S
where
L.sid = S.sid
and username is not null
order by trunc(id1/power(2,16)), bitand(id1,to_number('ffff','xxxx'))+0, id2, L.sid;
set pagesize 10000
set linesize 300
col "Session blocante" format a16
col "Session bloquee" format a16
col "Lock mode" format a13
col "Object" format a30
col "ctime" format a14
select L1.sid||' - '||L1.inst_id "Session blocante",
decode(L1.lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
to_char(L1.lmode)) "Lock mode",
(select O.owner||'.'||O.object_name from gv$locked_object LOBJ, dba_objects O where LOBJ.object_id=O.object_id and L1.sid=LOBJ.session_id and rownum<2) "Object",
case when L1.ctime < 86400
then to_char(to_date(L1.ctime, 'sssss'), 'hh24:mi:ss')
else '+'||trunc(L1.ctime/86400)||' '||to_char(to_date(mod(L1.ctime, 86400), 'sssss'), 'hh24:mi:ss')
end "ctime",
L2.sid||' - '||L2.inst_id "Session bloquee",
decode(L2.lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
to_char(L2.lmode)) "Lock mode",
(select O.owner||'.'||O.object_name from gv$locked_object LOBJ, dba_objects O where LOBJ.object_id=O.object_id and L2.sid=LOBJ.session_id and rownum<2) "Object",
case when L2.ctime < 86400
then to_char(to_date(L2.ctime, 'sssss'), 'hh24:mi:ss')
else '+'||trunc(L2.ctime/86400)||' '||to_char(to_date(mod(L2.ctime, 86400), 'sssss'), 'hh24:mi:ss')
end "ctime"
from
gv$lock L1,
gv$lock L2
where
L1.block=1
and L2.request>0
and L1.id1=L2.id1
and L1.id2=L2.id2
order by L1.ctime;
set head off
set pagesize 10000
set linesize 300
select distinct 'sid:'||s.sid, 'serial#:'||s.serial#, a.sql_text, 'username:'||s.username,
'logon:'||to_char(s.logon_time, 'dd/mm/yyyy hh24:mi:ss'), 'status:'||s.status, 'server:'||s.server,
'os process:'||p.spid, 'machine:'||s.machine, 'terminal:'||s.terminal, 'program:'||s.program
from
v$session s, v$sql a, v$process p, v$lock L1, v$lock L2
where
L1.block =1
and L2.request > 0
and L1.id1=L2.id1
and L1.id2=L2.id2
and (s.sid=L1.sid or s.sid=L2.sid)
and a.address=s.sql_address
and s.paddr=p.addr;
set pagesize 3000
set linesize 300
col object_name format a20
select c.sid,
c.username,
a.owner||'.'||a.object_name object_name,
c.program,
b.ctime,
decode(b.type, 'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
b.type) lock_type,
decode(b.lmode, 0, 'None', /* Mon Lock equivalent */
1, 'Null', /* NOT */
2, 'Row-SELECT (SS)', /* LIKE */
3, 'Row-X (SX)', /* R */
4, 'Share', /* SELECT */
5, 'SELECT/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(b.lmode)) mode_held,
decode(b.request, 0, 'None', /* Mon Lock equivalent */
1, 'Null', /* NOT */
2, 'Row-SELECT (SS)', /* LIKE */
3, 'Row-X (SX)', /* R */
4, 'Share', /* SELECT */
5, 'SELECT/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(b.request)) mode_requested
from
dba_objects a, v$lock b, v$session c
where
a.object_id=b.id1
and c.username is not null
and b.sid=c.sid
order by 5;
set pagesize 10000
set linesize 300
select addr,
status,
start_scnb,
start_time,
log_io,
phy_io,
cr_get,
cr_change,
start_scn
from
v$transaction
order by start_time;
ADDR STATUS START_SCNB START_TIME LOG_IO PHY_IO CR_GET CR_CHANGE START_SCN
---------------- ---------------- ---------- -------------------- ---------- ---------- ---------- ---------- ----------
070000075114F038 ACTIVE 2913970858 12/15/11 12:45:08 10 35 133 0 7.1633E+10
0700000758DD0F20 ACTIVE 2913970956 12/15/11 12:45:08 207 0 11 0 7.1633E+10
set pagesize 300
set linesize 300
col sid_serial format a10
col "Undo (Mo)" format 999,999.99
select S.sid||','||S.serial# sid_serial,
nvl(S.username, 'None') orauser,
S.program,
R.name undoseg,
T.used_ublk * to_number(X.value)/1024/1024 "Undo (Mo)"
from v$rollname R,
v$session S,
v$transaction T,
v$parameter X
where
S.taddr = T.addr
and R.usn = T.xidusn (+)
and X.name = 'db_block_size';
SID_SERIAL ORAUSER PROGRAM UNDOSEG Undo (Mo)
-------------------- ------------------------------ ------------------------------------------------ ------------------------------ -----------
326,11 SAPR3 dw.sapSID_D30@romvnm1adj (TNS V1-V3) _SYSSMU109_3077522030$ .01
333,11 SAPR3 dw.sapSID_D90@romvnk2adj (TNS V1-V3) _SYSSMU73_2102408645$ .01
set pagesize 300
set linesize 300
col inst format 9999
col sid_serial format a10
col obj format a30
select T.inst_id inst,
S.username,
S.sid||','||S.serial# sid_serial,
RN.name rollname,
RS.extents,
RS.status,
T.used_ublk "undo blocks",
T.used_urec "undo records",
DO.owner||'.'||DO.object_name obj
from
gv$transaction T,
gv$session S,
v$rollname RN,
gv$rollstat RS,
gv$locked_object LO,
dba_objects DO
where
t.inst_id=s.inst_id
and t.inst_id=rs.inst_id
and t.inst_id=lo.inst_id
and t.addr=s.taddr
and t.xidusn=rn.usn
and rn.usn=rs.usn
and t.xidusn=lo.xidusn (+)
and do.object_id=lo.object_id;
INST USERNAME SID_SERIAL ROLLNAME EXTENTS STATUS undo blocks used undo records used OBJ
----- -------------- ---------- ------------------------------ ---------- --------------- ---------------- ----------------- ------------------------------
1 USERSAP 651,11 _SYSSMU1148_2452184808$ 41 ONLINE 2 2 USERSAP.CRMD_ACTIVITY_H
1 USERSAP 651,11 _SYSSMU1148_2452184808$ 41 ONLINE 2 2 USERSAP.SMW3_BDOC7
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
set pagesize 300
set linesize 300
select usn,
state,
undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime, 0, 'unknown', sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 3600)) "Hours to complete"
from
v$fast_start_transactions;
documentationset pagesize 10000 set linesize 300 col group# format 99999 col thread# format 999999 col member format a56 col "Mo" format 9,999.99 col archived format a8 col status format a12 break on group# select LF.group#, L.thread#, LF.status, LF.type, LF.member, L.sequence#, L.bytes/1024/1024 "Mo", L.members, L.archived, L.status from v$log L, v$logfile LF where L.group#=LF.group# union all select LF.group#, L.thread#, LF.status, LF.type, LF.member, L.sequence#, L.bytes/1024/1024 "Mo", null, L.archived, L.status from v$standby_log L, v$logfile LF where L.group#=LF.group# order by 1, 2;
Oracle log_buffer sizing tips
set head off
set pagesize 0
set linesize 300
select distinct 'alter table '||C.owner||'.'||C.table_name||' disable constraint '||C.constraint_name||';'
from
dba_constraints C, dba_tables T
where
T.owner='OSGE'
and T.owner=C.owner
and T.table_name=C.table_name
and C.r_constraint_name is not null
and C.status='ENABLED'
order by 1;
define owner=OSGE
set head off
set pagesize 0
set linesize 300
select distinct 'alter table '||C.owner||'.'||C.table_name||' disable constraint '||C.constraint_name||';'
from
dba_constraints C, dba_tables T
where
T.owner='&owner'
and T.owner=C.owner
and T.table_name=C.table_name
and C.r_constraint_name is not null
and C.status='ENABLED'
order by 1;
define owner=PWRLINE
define table=CONSOMMATION
set pagesize 10000
set linesize 300
set verify off
col request format a80
select 'alter table '||P.owner||'.'||P.table_name||' disable constraint '||P.constraint_name||';' request
from
dba_constraints P,
dba_constraints R
where
R.owner='&owner'
and R.table_name='&table'
and R.constraint_type='P'
and R.constraint_name=P.r_constraint_name
and P.constraint_type='R'
and P.constraint='ENABLED';
define owner=SAPSR3DB
define table=ARFCRSTATE
set pagesize 300
set verify off
col table_name format a30
col last_analyzed format a19
col stale_stats format a11
col stattype_locked format a15
select T.owner||'.'||T.table_name table_name,
to_char(T.last_analyzed, 'yyyy/mm/dd hh24:mi:ss') last_analyzed,
S.stale_stats,
S.stattype_locked
from
dba_tables T,
dba_tab_statistics S
where
T.owner=upper('&owner')
and T.table_name=upper('&table')
and T.owner=S.owner
and T.table_name=S.table_name;
déverrouiller les statistiques des tables du schéma 'owner'exporter / importer les statistiques
define owner=SAPR3 set verify off exec dbms_stats.unlock_schema_stats('&owner');déverrouiller les statistiques pour une table
define owner=SAPR3 define table_name=toto set verify off exec dbms_stats.unlock_table_stats(upper('&owner'), upper('&table_name'));
define owner=USER1 define table=ZRELEVE_AD set head off set pagesize 0 set linesize 300 set verify off set feedback off select 'exec dbms_stats.set_table_stats(ownname=>'''||S.owner||''', tabname=>'''||S.table_name||''', numrows=>'||S.num_rows||');' from dba_tables S where S.owner=upper('&owner') and S.table_name=upper('&table'); select 'exec dbms_stats.set_table_stats(ownname=>'''||S.owner||''', tabname=>'''||S.table_name||''', numrows=>'||S.num_rows||', numblks=>'||S.blocks||', avgrlen=>'||S.avg_col_len||');' from dba_tab_columns C, dba_tab_col_statistics S where C.owner=upper('&owner') and C.table_name=upper('&table') and S.owner=C.owner and S.table_name=C.table_name and C.column_name=S.column_name;Remarque :
* partname : name of the table partition in which to store the statistics. If the table is partitioned and partname is NULL, then the statistics are stored at the global table level.
* numblks : number of blocks the table (partition) occupies
* avgrlen : average row length for the table (partition)
Creating User-defined Statistic tabledbms_stats.create_stat_table (ownname => 'USER', stattab => 'CASE_HDR_STAT_TAB', tblspace => 'LLMDATA');Using dbms_stats.export_table_stats proceduredbms_stats.export_table_stats (ownname => 'USER', tabname => 'CASE_HDR', stattab => 'CASE_HDR_STAT_TAB', statid => 1, cascade => true);Using dbms_stats.export_schema_stats proceduredbms_stats.export_schema_stats (ownname => 'USER', stattab => 'CASE_HDR_STAT_TAB');Importing Statistics from User-defined table into Dictionarydbms_stats.import_table_stats (ownname => 'USER', tabname => 'CASE_HDR', stattab => 'CASE_HDR_STAT_TAB', statid => 1, cascade => true, no_invalidate => true);
créer des synonymes sur le schéma 'synonym_owner' vers toutes les tables et vues du schéma 'object_owner'
define synonym_owner=USRMOH
define object_owner=SAPR3
set pagesize 0
set linesize 300
set feedback off
set verify off
with tables as (
select table_name table_name from dba_tables where owner=upper('&object_owner')
union all
select view_name table_name from dba_views where owner=upper('&object_owner')
)
select 'create synonym '||upper('&synonym_owner')||'."'||table_name||'" for '||upper('&object_owner')||'."'||table_name||'";'
from
tables
minus
select 'create synonym '||owner||'."'||synonym_name||'" for '||table_owner||'."'||table_name||'";'
from
dba_synonyms
where
owner=upper('&synonym_owner')
and table_owner=upper('&object_owner');
-------------------------------------
-- suppression des synonyms obsolètes
with tables as (
select table_name table_name from dba_tables where owner=upper('&object_owner')
union all
select view_name table_name from dba_views where owner=upper('&object_owner')
)
select 'drop synonym '||owner||'."'||synonym_name||'";'
from
dba_synonyms
where
owner=upper('&synonym_owner')
and table_owner=upper('&object_owner')
minus
select 'drop synonym '||upper('&synonym_owner')||'."'||table_name||'";'
from
tables;
set linesize 300
col object_name format a30
col "Warn operator" format a13
col "Warn value" format a10
col "Crit operator" format a13
col "Crit value" format a10
select T.instance_name,
T.object_name,
T.status,
decode(T.warning_operator, 'GE', '>=', 'LE', '<=', 'GT', '>', 'LT', '<', T.warning_operator) "Warn operator",
T.warning_value "Warn value",
decode(T.critical_operator, 'GE', '>=', 'LE', '<=', 'GT', '>', 'LT', '<', T.critical_operator) "Crit operator",
T.critical_value "Crit value",
T.observation_period "Period",
T.consecutive_occurrences "Occurrences"
from
dba_thresholds T,
v$instance I
where
T.object_type='TABLESPACE'
-- and I.instance_name=T.instance_name
order by 1;
INSTANCE_NAME OBJECT_NAME STATUS Warn operator Warn value Crit operator Crit value Period Occurrences
---------------- ------------------------------ ------- ------------- ---------- ------------- ---------- ---------- -----------
database_wide VALID >= 85 >= 97 1 1
database_wide VALID <= 0 <= 0 1 1
historique des modifications d'un paramètreset pagesize 3000 set linesize 300 col name format a40 col value format a36 col sid format a6 col display_value format a36 set head off select name||' : '||value value from v$parameter where name='spfile'; set head on select name, nvl(value, '<null>') value, sid, type, nvl(display_value, '<null>') display_value from v$spparameter where isspecified='TRUE' order by name, ordinal;
define my_param=db_file_multiblock_read_count
set pagesize 10000
set linesize 300
set verify off
set feedback off
col end_interval_time format a25
col parameter_name format a40
col value format a20
col changed format a7
select to_char(S.end_interval_time, 'yyyy/mm/dd hh24:mi') end_interval_time,
A.instance_number,
A.parameter_name,
A.value,
case when A.value != A.p_value then '*' end changed
from
( select snap_id,
instance_number,
parameter_name,
to_char(value) value,
to_char(lag(value, 1) over (order by instance_number, snap_id)) p_value
from
dba_hist_parameter
where
parameter_name='&my_param'
) A,
dba_hist_snapshot S
where
A.instance_number=S.instance_number
and A.snap_id=S.snap_id
order by 2, 1;
les paramètres obsolètescol name format a40 col value format a60 set lines 120 set pagesize 10000 select name, value from v$parameter where isdeprecated='TRUE' and value is not null order by 1; select 'alter system reset '||name||' scope=both;' from v$parameter where isdeprecated='TRUE' and value is not null order by 1; select 'grep -i '||name||' $spfile' from v$parameter where isdeprecated='TRUE' and value is not null order by 1; alter system reset undo_tablespace scope=spfile sid='*';
regénérer le init.oraselect 'grep -i '||name||' $ORACLE_HOME/dbs/init$ORACLE_SID.ora' from v$obsolete_parameter;
set head off
set pagesize 0
set linesize 1000
set feedback off
col line format a1000
with A as (
select i.inst_id,
i.instance_name,
p.name,
nvl(p.value, '') value,
case when p.value != p.display_value then p.display_value end display_value
from
gv$system_parameter p,
gv$instance i
where
p.isdefault='FALSE'
and p.inst_id=i.inst_id
)
select '*'||';'||l1.name||';'||l1.value||';'||l1.display_value
from
A l1, A l2
where
l1.inst_id=1
and l2.inst_id=2
and l1.name=l2.name
and l1.value=l2.value
union all
select l1.instance_name||';'||l1.name||';'||l1.value||';'||l1.display_value
from
A l1, A l2
where
l1.inst_id=1
and l2.inst_id=2
and l1.name=l2.name
and l1.value!=l2.value
union all
select l2.instance_name||';'||l2.name||';'||l2.value||';'||l2.display_value
from
A l1, A l2
where
l1.inst_id=1
and l2.inst_id=2
and l1.name=l2.name
and l1.value!=l2.value
order by 1;
recompiler les objets invalidesset pagesize 10000 set linesize 300 col name format a40 select owner||'.'||object_name name, object_type, status from dba_objects where status != 'VALID' order by 1;
set head off
set pagesize 0
set feedback off
set echo off
set linesize 300
select 'alter '||decode(object_type, 'PACKAGE BODY', 'PACKAGE', 'TYPE BODY', 'TYPE', object_type)||
' '||owner||'."'||object_name||'" compile '||decode(object_type, 'PACKAGE BODY', 'BODY', 'PACKAGE', 'PACKAGE', 'TYPE BODY', 'BODY')||';'
from
dba_objects obj
where
status != 'VALID'
and object_type in ('PACKAGE BODY','PACKAGE','FUNCTION','PROCEDURE','TRIGGER','VIEW','TYPE','TYPE BODY','JAVA CLASS','JAVA SOURCE')
order by 1;
define owner=SAPR3
define segment_name=DFKKOP
set linesize 300
col tablespace_name format a25
col obj format a30
col file_name format a55
col "Mo" format 999,999.99
select N.owner||'.'||N.segment_name obj,
E.segment_type,
E.tablespace_name,
D.file_name,
sum(E.bytes)/1024/1024 "Mo"
from
(select upper('&owner') owner, upper('&segment_name') segment_name from dual) N,
dba_extents E,
dba_data_files D
where
E.owner=N.owner
and E.segment_name=N.segment_name
and E.file_id=D.file_id
group by N.owner||'.'||N.segment_name,
E.segment_type,
E.tablespace_name,
D.file_name;
OBJ SEGMENT_TYPE TABLESPACE_NAME FILE_NAME Mo
------------------------------ ------------------ ------------------------- ------------------------------------------------------- -----------
SAPR3.CDCLS TABLE PSAPCLUD /oracle/SID/sapdata13/clud_104/clud.data104 57.13
SAPR3.CDCLS TABLE PSAPCLUD /oracle/SID/sapdata13/clud_101/clud.data101 17.13
SAPR3.CDCLS TABLE PSAPCLUD /oracle/SID/sapdata13/clud_107/clud.data107 97.19
SAPR3.CDCLS TABLE PSAPCLUD /oracle/SID/sapdata13/clud_106/clud.data106 73.00
SAPR3.CDCLS TABLE PSAPCLUD /oracle/SID/sapdata13/clud_105/clud.data105 57.19
SAPR3.CDCLS TABLE PSAPCLUD /oracle/SID/sapdata13/clud_103/clud.data103 49.19
SAPR3.CDCLS TABLE PSAPCLUD /oracle/SID/sapdata13/clud_102/clud.data102 25.19
define dbf=/appli/oradata03/REF_DATA/REF_DATA_reference_03.dbf
set pagesize 300
set linesize 300
set verify off
col obj format a40
col "Size (Mo)" format 9,999,999.99
select owner||'.'||segment_name obj,
segment_type,
count(*),
sum(bytes)/1024/1024 "Size (Mo)"
from
dba_extents
where
file_id in ( select file_id
from
dba_data_files
where
file_name like '&dbf' )
group by owner||'.'||segment_name, segment_type
order by 1;
define obj_name=ZSTOCK_FLUX
set linesize 300
set pagesize 300
set verify off
col object_name format a40
col status format a7
col object_type format a20
col created format a19
col last_ddl_time format a19
select owner||'.'||object_name object_name,
status,
object_type,
to_char(created, 'dd/mm/yyyy hh24:mi:ss') created,
to_char(last_ddl_time, 'dd/mm/yyyy hh24:mi:ss') last_ddl_time
from
dba_objects
where
object_name=upper('&obj_name')
order by 1;
define owner=SAPR3
define object_type=table
define size=100
set pagesize 10000
set verify off
col "Size" format 9,999.99
col "Table" format a40
select owner||'.'||segment_name "&object_type",
bytes/1024/1024 "Size"
from
dba_segments
where
segment_type=upper('&object_type')
and owner=upper('&owner')
and bytes/1024/1024 > &size
order by 2;
define owner=SAPR3
define object_type=table
define size=100
define data_type=NUMBER
set pagesize 10000
set verify off
col "Size" format 9,999.99
col "Table" format a40
select owner||'.'||segment_name "&object_type",
bytes/1024/1024 "Size"
from
dba_segments
where
segment_type=upper('&object_type')
and owner=upper('&owner')
and bytes/1024/1024 > &size
and exists (select 1 from dba_tab_columns C where C.data_type=upper('&data_type') and C.owner=S.owner and C.table_name=S.segment_name)
order by 2;
define owner=SAPR3
define table_name=XXX
set pagesize 300
set linesize 300
set verify off
set feedback off
col table_name format a28
col index_name format a28
col "Size Mo" format 9,999,999.99
select owner||'.'||segment_name table_name, sum(bytes)/1024/1024 "Size Mo"
from
dba_segments
where
segment_name='&table_name' and owner='&owner'
group by owner||'.'||segment_name;
with A as (
select S.owner||'.'||S.segment_name index_name, sum(S.bytes)/1024/1024 "Size Mo"
from
dba_segments S,
dba_indexes I
where
I.table_name='&table_name' and I.table_owner='&owner'
and S.owner=I.owner and S.segment_name=I.index_name
group by S.owner||'.'||S.segment_name )
select * from A
union all
select 'total:', sum("Size Mo") from A
order by 1;
define owner=SAPR3
define n=10
set pagesize 300
set linesize 300
set verify off
set feedback off
col segment_name format a28
col Mo format 9,999,999.99
select * from ( select segment_name,
sum(bytes)/1024/1024 Mo
from
dba_segments
where
owner='&owner'
group by segment_name order by 2 desc
)
where rownum < &n+1;
define table_name=CHRIS.TABLE
define condition="date_creation_dem_rec < TO_DATE('31/12/2012','DD/MM/YYYY')"
define commit_period=100000
set verify off
set serveroutput on
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
declare
commit_count number;
cursor c is select rowid from &table_name where &condition;
begin
commit_count := 0;
dbms_output.put_line('start at '||sysdate);
for rid in c
loop
delete from &table_name where rowid=rid.rowid;
commit_count := commit_count + 1;
if mod(commit_count, &commit_period) = 0 then
commit;
dbms_output.put_line('commit at '||sysdate);
end if;
end loop;
if commit_count > 0 then
commit;
dbms_output.put_line('commit at '||sysdate);
end if;
dbms_output.put_line(commit_count||' rows deleted.');
dbms_output.put_line('end at '||sysdate);
end;
/
tables non partitionneesset pagesize 3000 set linesize 300 break on table_name noduplicates col table_name format a40 col frag_pct format 999.99 col mo1 format 999999999 col mo2 format 999999999 select * from ( select T.owner||'.'||T.table_name table_name, T.tablespace_name, T.num_rows, (T.blocks*TAB.block_size - T.num_rows*T.avg_row_len) / (T.blocks*TAB.block_size) * 100 frag_pct, T.blocks*TAB.block_size/1024/1024 mo1, T.num_rows*T.avg_row_len/1024/1024 mo2 from dba_tables T, dba_objects O, dba_tablespaces TAB where T.partitioned='NO' and T.table_owner='MY_OWNER' -- and T.table_name like 'MY_TABLE%' and T.blocks > 100 and T.owner=O.owner and T.table_name=O.object_name and O.object_type='TABLE' and T.tablespace_name=TAB.tablespace_name ) where frag_pct > 10 and mo1 > 100 order by 1;tables partitionneesset pagesize 3000 set linesize 300 break on table_name noduplicates col table_name format a40 col frag_pct format 999.99 col mo1 format 999999999 col mo2 format 999999999 select * from ( select T.table_owner||'.'||T.table_name table_name, T.partition_name, T.tablespace_name, T.num_rows, (T.blocks*TAB.block_size - T.num_rows*T.avg_row_len) / (T.blocks*TAB.block_size) * 100 frag_pct, T.blocks*TAB.block_size/1024/1024 mo1, T.num_rows*T.avg_row_len/1024/1024 mo2 from dba_tab_partitions T, dba_objects O, dba_tablespaces TAB where T.table_owner='MY_OWNER' -- and T.table_name like 'MY_TABLE%' and T.blocks > 100 and T.table_owner=O.owner and T.table_name=O.object_name and T.partition_name=O.subobject_name and O.object_type='TABLE PARTITION' and T.tablespace_name=TAB.tablespace_name ) where frag_pct > 10 and mo1 > 100 order by 1;
define sql_id=40h86y4a0fx0g
set pagesize 3000
set linesize 300
select distinct to_char(S.end_interval_time, 'yyyy-mm-dd hh24:mi') end_interval_time,
'&sql_id' sql_id,
A.cnt
from
( select snap_id, count(*) cnt from dba_hist_sqlstat where sql_id='&sql_id' group by snap_id ) A,
dba_hist_snapshot S
where
A.snap_id=S.snap_id
order by 1;
select n, m,
lag(m, 1) over (order by n) "Previous M"
from lag_exp;
N M Previous M
---------- ---------- ----------
1 6
2 12 6
3 5 12
4 11 5
5 4 11
6 10 4
7 3 10
8 9 3
create table t ( son number, dad number);
insert into t values ( 1,3);
insert into t values ( 2,3);
insert into t values ( 10,3);
insert into t values ( 3,4);
insert into t values ( 4,50);
insert into t values ( 5,80);
select connect_by_root son as son,
dad as ancestor
from t
where
connect_by_isleaf = 1
connect by son = prior dad;
son ancestor
--- --------
1 50
2 50
3 50
4 50
5 80
10 50
set serveroutput on
declare
CURSOR cur IS
select UIDCHANNELCUT
from pwrline.lschannelcutdata;
CDATA1 NUMBER := 0;
CDATA2 NUMBER := 0;
CDATA3 NUMBER := 0;
CDATA4 NUMBER := 0;
CDATA5 NUMBER := 0;
CDATA6 NUMBER := 0;
CDATA7 NUMBER := 0;
begin
dbms_output.enable (1000000);
dbms_output.put_line('Debut : ' || to_char(sysdate, 'HH24:MI:SS') );
FOR rec IN cur LOOP
IF(rec.uidchannelcut < 3500000) THEN
CDATA1 := CDATA1 + 1;
ELSIF(rec.uidchannelcut >= 3500000 AND rec.uidchannelcut < 6500000) THEN
CDATA2 := CDATA2 + 1;
ELSIF(rec.uidchannelcut >= 6500000 AND rec.uidchannelcut < 9500000) THEN
CDATA3 := CDATA3 + 1;
ELSIF(rec.uidchannelcut >= 9500000 AND rec.uidchannelcut < 10750000) THEN
CDATA4 := CDATA4 + 1;
ELSIF(rec.uidchannelcut >= 10750000 AND rec.uidchannelcut < 12000000) THEN
CDATA5 := CDATA5 + 1;
ELSIF(rec.uidchannelcut >= 12000000 AND rec.uidchannelcut < 13628000) THEN
CDATA6 := CDATA6 + 1;
ELSE
CDATA7 := CDATA7 + 1;
END IF;
END LOOP;
dbms_output.put_line ( ' '|| 'CDATA1=' || CDATA1 || ' soit '|| CDATA1*100/2091010 ||'%');
dbms_output.put_line (' ' || 'CDATA2=' || CDATA2 || ' soit '|| CDATA2*100/1941883 ||'%');
dbms_output.put_line (' ' || 'CDATA3=' || CDATA3 || ' soit '|| CDATA3*100/2017139 ||'%');
dbms_output.put_line (' ' || 'CDATA4=' || CDATA4 || ' soit '|| CDATA4*100/994810 ||'%');
dbms_output.put_line (' ' || 'CDATA5=' || CDATA5 || ' soit '|| CDATA5*100/1228973 ||'%');
dbms_output.put_line (' ' || 'CDATA6=' || CDATA6 || ' soit '|| CDATA6*100/1300008 ||'%');
dbms_output.put_line (' ' || 'CDATA7=' || CDATA7 || ' soit '|| CDATA7*100/1665914 ||'%');
dbms_output.put_line ('Fin : ' || to_char(sysdate, 'HH24:MI:SS') );
end;
begin
for i in 1..100 loop
execute immediate 'insert into ccc values(sysdate-:i)' using i;
end loop;
commit;
end;
set pagesize 10000 set linesize 300 col obj format a30 break on obj skip 1 noduplicate select owner||'.'||name||' '||type obj, 'line: '||line||' position: '||position||' '||attribute||': '||text from dba_errors order by sequence;
define owner=SAPR3
define obj=EVER
set head off
set pagesize 0
set long 5000000
set linesize 1000
set verify off
set feedback off
col cmd format a1000
select dbms_metadata.get_ddl('TABLE', upper('&obj'), upper('&owner'))||';' cmd from dual;
define owner=SAPR3
define obj=EVER~0
set head off
set pagesize 0
set long 5000000
set linesize 1000
set verify off
set feedback off
col cmd format a1000
select dbms_metadata.get_ddl('INDEX', upper('&obj'), upper('&owner'))||';' cmd from dual;
define type=CLUSTER
define type=DIMENSION
define type=FUNCTION
define type=JAVA SOURCE
define type=JAVA CLASS
define type=JAVA DATA
define type=MATERIALIZED_VIEW
define type=PACKAGE
define type=PACKAGE BODY
define type=PROCEDURE
define type=PROFILE
define type=SEQUENCE
define type=SYNONYM
define type=TRIGGER
define type=TYPE
define type=TYPE BODY
define type=VIEW
define owner=SAPR3
define obj=EVER~0
set head off
set pagesize 0
set long 5000000
set linesize 1000
set verify off
set feedback off
col cmd format a1000
select dbms_metadata.get_ddl(upper('&type'), upper('&obj'), upper('&owner'))||';' cmd from dual;
define obj=SYSTEM
set head off
set pagesize 0
set long 128000
set verify off
select dbms_metadata.get_ddl('TABLESPACE', '&obj')||';' from dual;
define obj=CHRIS
set head off
set pagesize 0
set long 128000
set verify off
select dbms_metadata.get_ddl('USER', '&obj')||';' from dual;
set pagesize 300
set linesize 300
set verify off
define col01="SQL Id"
define col02="Executions"
define col03="Buffer gets"
define col04="Buffer gets/exec"
define col05="Buffer gets Go"
define col06="Buffer gets Mo/exec"
define col07="Buffer gets/row"
define col08="Buffer gets Mo/row"
define col09="Disk reads"
define col10="Disk reads/exec"
define col11="Rows"
define col12="Rows/exec"
define col13="Sorts"
define col14="Sorts/exec"
define col15="Elapsed time"
define col16="Elapsed time/exec"
define col17="CPU time"
define col18="CPU time/exec"
define col19="Wait time"
define col20="Wait time/exec"
define col21="App wait time"
define col22="App wait time/exec"
define col23="Concurr wait time"
define col24="Concurr wait time/exec"
define col25="User IO wait time"
define col26="User IO wait time/exec"
define col27="First load time"
define col28="Last active time"
define col29="Buffer gets pct"
define col30="Disk reads pct"
define day=1000000/3600/24
define my_columns=C01,C02,C03,C04,C05,C06,C07,C08,C29,C09,C10,C30,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,C27,C28
define my_nulls=null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
define execs=decode(executions,0,null,executions)
define nrows=decode(rows_processed,0,null,rows_processed)
define to_go=block_size/1024/1024/1024
define to_mo=block_size/1024/1024
col "&col02" format 999,999,999
col "&col03" format 9,999,999,999
col "&col04" format 999,999,999
col "&col05" format 99,999,999
col "&col06" format 999,999,999
col "&col07" format 999,999,999
col "&col08" format 999,999,999
col "&col09" format 999,999,999
col "&col10" format 999,999,999
col "&col11" format 9,999,999,999
col "&col12" format 999,999,999
col "&col13" format 999,999,999
col "&col14" format 999,999,999
col "&col16" format 9,999,999.99
col "&col18" format 9,999,999.99
col "&col20" format 9,999,999.99
col "&col22" format 9,999,999.99
col "&col24" format 9,999,999.99
col "&col25" format a17
col "&col26" format 9,999,999.99
col "&col27" format a19
col "&col29" format 999.99
col "&col30" format 999.99
alter session set nls_date_format='hh24:mi:ss';
with T as (
select sql_id C01,
executions C02,
buffer_gets C03,
buffer_gets/&execs C04,
buffer_gets*&to_go C05,
buffer_gets*&to_mo/&execs C06,
buffer_gets/&nrows C07,
buffer_gets*&to_mo/&nrows C08,
disk_reads C09,
disk_reads/&execs C10,
rows_processed C11,
rows_processed/&execs C12,
sorts C13,
sorts/&execs C14,
date2000+elapsed_time/&day C15,
elapsed_time/1000000/&execs C16,
date2000+cpu_time/&day C17,
cpu_time/1000000/&execs C18,
date2000+(elapsed_time-cpu_time)/&day C19,
(elapsed_time-cpu_time)/1000000/&execs C20,
date2000+application_wait_time/&day C21,
application_wait_time/1000000/&execs C22,
date2000+concurrency_wait_time/&day C23,
concurrency_wait_time/1000000/&execs C24,
date2000+user_io_wait_time/&day C25,
user_io_wait_time/1000000/&execs C26,
first_load_time C27,
to_char(last_active_time, 'yyyy/mm/dd hh24:mi:ss') C28,
buffer_gets*100/t_buffer_gets C29,
disk_reads*100/t_disk_reads C30
from
gv$sqlarea,
( select value block_size,
to_date('01-01-2000', 'dd-mm-yyyy') date2000
from
v$parameter
where
name = 'db_block_size' ) A,
( select sum(buffer_gets) t_buffer_gets,
sum(disk_reads) t_disk_reads
from
gv$sqlarea ) B
)
select to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') "&col01",
null "&col02",
null "&col03",
null "&col04",
null "&col05",
null "&col06",
null "&col07",
null "&col08",
null "&col29",
null "&col09",
null "&col10",
null "&col30",
null "&col11",
null "&col12",
null "&col13",
null "&col14",
null "&col15",
null "&col16",
null "&col17",
null "&col18",
null "&col19",
null "&col20",
null "&col21",
null "&col22",
null "&col23",
null "&col24",
null "&col25",
null "&col26",
null "&col27",
null "&col28" from gv$instance
union all select instance_name,&my_nulls from gv$instance
union all select null,&my_nulls from dual
--
union all select 'TOP buffer gets',&my_nulls from dual
union all select * from (select &my_columns from T order by 3 desc) where rownum < 6
union all select null,&my_nulls from dual
--
union all select 'TOP disk reads',&my_nulls from dual
union all select * from (select &my_columns from T order by 10 desc) where rownum < 6
union all select null,&my_nulls from dual
--
union all select 'TOP buffer gets/exec',&my_nulls from dual
union all select * from (select &my_columns from T where C02 > 0 order by 4 desc) where rownum < 6
union all select null,&my_nulls from dual
--
union all select 'TOP disk reads/exec',&my_nulls from dual
union all select * from (select &my_columns from T where C02 > 0 order by 11 desc) where rownum < 6
union all select null,&my_nulls from dual
--
union all select 'TOP elapsed time/exec',&my_nulls from dual
union all select * from (select &my_columns from T where C02 > 0 order by 18 desc) where rownum < 6
union all select null,&my_nulls from dual
--
union all select 'TOP CPU time/exec',&my_nulls from dual
union all select * from (select &my_columns from T where C02 > 0 order by 20 desc) where rownum < 6
union all select null,&my_nulls from dual
--
union all select 'TOP wait time/exec',&my_nulls from dual
union all select * from (select &my_columns from T where C02 > 0 order by 22 desc) where rownum < 6;
define sql_id=62yu4j839cydy
set pagesize 0
set linesize 300
set verify off
set long 128000
select * from table(dbms_xplan.display_awr('&sql_id', null, null, 'ADVANCED'));
select dbms_lob.substr(sql_text, 4000, 1) from dba_hist_sqltext where sql_id='&sql_id';
prompt objects used with the PLAN :
select distinct regexp_replace(plan_table_output, '^\| *\d+ *\| *([^\|]+) *\| *([^\|]+) *\|.*', '\1 => \2') "Accessed objects"
from table(dbms_xplan.display_awr('&sql_id', null, null, 'ADVANCED'))
where
regexp_like(plan_table_output, '^\| *\d+ *\| *[^\|]+ *\| *\w[^\|]+ *\|');
define sql_id=62yu4j839cydy
set head on
set pagesize 300
set linesize 300
set verify off
define col01="SQL Id"
define col02="Executions"
define col03="Buffer gets"
define col04="Buffer gets/exec"
define col05="Buffer gets Go"
define col06="Buffer gets Mo/exec"
define col07="Buffer gets/row"
define col08="Buffer gets Mo/row"
define col09="Disk reads"
define col10="Disk reads/exec"
define col11="Rows"
define col12="Rows/exec"
define col13="Sorts"
define col14="Sorts/exec"
define col15="Elapsed time"
define col16="Elapsed time/exec"
define col17="CPU time"
define col18="CPU time/exec"
define col19="Wait time"
define col20="Wait time/exec"
define col21="App wait time"
define col22="App wait time/exec"
define col23="Concurr wait time"
define col24="Concurr wait time/exec"
define col25="User IO wait time"
define col26="User IO wait time/exec"
define col27="First load time"
define col28="Last active time"
define col29="Buffer gets pct"
define col30="Disk reads pct"
define col31="Plan hash value"
define day=1000000/3600/24
define my_columns=C01,C02,C03,C04,C05,C06,C07,C08,C29,C09,C10,C30,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,C27,C28,C31
define my_nulls=null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
define execs=decode(executions,0,null,executions)
define nrows=decode(rows_processed,0,null,rows_processed)
define to_go=block_size/1024/1024/1024
define to_mo=block_size/1024/1024
col "&col02" format 999,999,999
col "&col03" format 9,999,999,999
col "&col04" format 999,999,999
col "&col05" format 99,999,999
col "&col06" format 999,999,999
col "&col07" format 999,999,999
col "&col08" format 999,999,999
col "&col09" format 999,999,999
col "&col10" format 999,999,999
col "&col11" format 9,999,999,999
col "&col12" format 999,999,999
col "&col13" format 999,999,999
col "&col14" format 999,999,999
col "&col16" format 9,999,999.99
col "&col18" format 9,999,999.99
col "&col20" format 9,999,999.99
col "&col22" format 9,999,999.99
col "&col24" format 9,999,999.99
col "&col25" format a17
col "&col26" format 9,999,999.99
col "&col27" format a19
col "&col29" format 999.99
col "&col30" format 999.99
alter session set nls_date_format='hh24:mi:ss';
with T as (
select sql_id C01,
executions C02,
buffer_gets C03,
buffer_gets/&execs C04,
buffer_gets*&to_go C05,
buffer_gets*&to_mo/&execs C06,
buffer_gets/&nrows C07,
buffer_gets*&to_mo/&nrows C08,
disk_reads C09,
disk_reads/&execs C10,
rows_processed C11,
rows_processed/&execs C12,
sorts C13,
sorts/&execs C14,
date2000+elapsed_time/&day C15,
elapsed_time/1000000/&execs C16,
date2000+cpu_time/&day C17,
cpu_time/1000000/&execs C18,
date2000+(elapsed_time-cpu_time)/&day C19,
(elapsed_time-cpu_time)/1000000/&execs C20,
date2000+application_wait_time/&day C21,
application_wait_time/1000000/&execs C22,
date2000+concurrency_wait_time/&day C23,
concurrency_wait_time/1000000/&execs C24,
date2000+user_io_wait_time/&day C25,
user_io_wait_time/1000000/&execs C26,
first_load_time C27,
to_char(last_active_time, 'yyyy/mm/dd hh24:mi:ss') C28,
buffer_gets*100/t_buffer_gets C29,
disk_reads*100/t_disk_reads C30,
plan_hash_value C31
from
gv$sqlarea,
( select value block_size,
to_date('01-01-2000', 'dd-mm-yyyy') date2000
from
v$parameter
where
name = 'db_block_size' ) A,
( select sum(buffer_gets) t_buffer_gets,
sum(disk_reads) t_disk_reads
from
gv$sqlarea ) B
where sql_id='&sql_id'
)
select to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') "&col01",
null "&col02",
null "&col03",
null "&col04",
null "&col05",
null "&col06",
null "&col07",
null "&col08",
null "&col29",
null "&col09",
null "&col10",
null "&col30",
null "&col11",
null "&col12",
null "&col13",
null "&col14",
null "&col15",
null "&col16",
null "&col17",
null "&col18",
null "&col19",
null "&col20",
null "&col21",
null "&col22",
null "&col23",
null "&col24",
null "&col25",
null "&col26",
null "&col27",
null "&col28",
null "&col31" from gv$instance
union all select instance_name,&my_nulls from gv$instance
union all select &my_columns from T;
hintsdefine sql_id=62yu4j839cydy set pagesize 300 set linesize 300 set verify off col value_string format a30 select name, datatype, value_string from dba_hist_sqlbind where sql_id='&sql_id' order by snap_id, name;
oradebugselect /*+ index_ss(owner.table_name "index_name") */ col01, col02 ...; select /*+ index(owner.table_name "index_name") */ col01, col02 ...; select /*+ FULL */ count(*)*100 from <table> sample block (1); select /*+ FULL */ count(*)*100 from <table> sample (1);
historique des évènementsoradebug setmypid oradebug unlimit oradebug event 10046 trace name context forever,level 12 select ... ; oradebug tracefile_name /oracle/SID/saptrace/diag/rdbms/sid/SID/trace/SID_ora_2019536.trc
define events="'checkpoint completed', 'log file switch (checkpoint incomplete)'"
set linesize 300
set pagesize 300
set verify off
col end_interval_time format a25
select B.end_interval_time, A.event_name, A.total_timeouts
from
dba_hist_system_event A,
dba_hist_snapshot B
where
A.event_name in (&events)
and A.snap_id=B.snap_id
order by A.snap_id;
END_INTERVAL_TIME EVENT_NAME TOTAL_TIMEOUTS
------------------------- ---------------------------------------------------------------- --------------
09-DEC-11 08.30.40.361 PM log file switch (checkpoint incomplete) 0
09-DEC-11 09.00.58.000 PM log file switch (checkpoint incomplete) 0
09-DEC-11 09.30.09.105 PM log file switch (checkpoint incomplete) 0
09-DEC-11 10.00.16.044 PM log file switch (checkpoint incomplete) 0
09-DEC-11 10.30.16.893 PM log file switch (checkpoint incomplete) 0
09-DEC-11 11.00.24.216 PM log file switch (checkpoint incomplete) 0
autotrace traceonly - explain planset head on set pagesize 300 set linesize 300 col resource_name format a24 col initial_allocation format a18 col limit_value format a12 select * from v$resource_limit order by resource_name; RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE ------------------------ ------------------- --------------- ------------------ ------------ branches 0 0 5596 UNLIMITED cmtcallbk 1 17 5596 UNLIMITED dml_locks 1 1 22384 UNLIMITED enqueue_locks 2131 3053 58900 58900 enqueue_resources 781 1635 24404 UNLIMITED gcs_resources 0 0 0 0 gcs_shadows 0 0 0 0 ges_big_msgs 0 0 0 UNLIMITED ges_cache_ress 0 0 0 UNLIMITED ges_locks 0 0 0 UNLIMITED ges_procs 0 0 0 0 ges_reg_msgs 0 0 0 UNLIMITED ges_ress 0 0 0 UNLIMITED ges_rsv_msgs 0 0 0 0 k2q_locks 0 0 10176 UNLIMITED max_rollback_segments 383 383 5596 65535 max_shared_servers 0 0 UNLIMITED UNLIMITED parallel_max_servers 0 500 500 3600 processes 1391 1907 2500 2500 sessions 1401 1980 5088 5088 sort_segment_locks 3 10 UNLIMITED UNLIMITED temporary_table_locks 0 92 UNLIMITED UNLIMITED transactions 2 2 5596 UNLIMITED
system eventsset pagesize 300 set linesize 300 SQL> set autotrace traceonly explain SQL> select ................; SQL> set autotrace off explain plan for select ................; select * from table(dbms_xplan.display);
set linesize 300
set pagesize 300
col event for a60
col wait_class for a20
col total_waits for 999,999,999
col total_timeouts for 999,999,999
col time_waited for a10
col "average wait (s)" for 999.99
select a.event,
a.wait_class,
a.total_waits,
a.total_timeouts,
to_char(to_date('01-01-2000', 'dd-mm-yyyy')+a.time_waited/100/3600/24, 'hh24:mi:ss') time_waited,
a.average_wait/100 "average wait (s)"
from
v$system_event a, v$event_name b
where
a.event = b.name
order by a.time_waited;
EVENT WAIT_CLASS TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITE average wait (s)
------------------------------------------------------------ -------------------- ------------ -------------- ---------- ----------------
db file parallel write System I/O 22,792 0 00:00:20 .00
log file parallel write System I/O 19,322 0 00:00:21 .00
db file scattered read User I/O 7,209 0 00:01:11 .01
jobq slave wait Idle 30 30 00:01:28 2.93
control file parallel write System I/O 66,323 0 00:01:59 .00
control file sequential read System I/O 311,039 0 00:04:45 .00
db file sequential read User I/O 59,919 0 00:05:44 .01
PX Idle Wait Idle 308 302 00:09:52 1.92
Streams AQ: waiting for time management or cleanup tasks Idle 165 164 01:47:03 562.57
smon timer Idle 760 622 01:22:25 233.87
Streams AQ: qmn slave idle wait Idle 6,728 0 02:55:50 27.25
Streams AQ: qmn coordinator idle wait Idle 13,474 6,746 02:55:53 13.61
pmon timer Idle 63,141 63,136 03:05:55 2.91
SQL*Net message from client Idle 550,307 0 15:48:17 1.05
rdbms ipc message Idle 601,156 585,300 11:23:05 3.09
define motif="%SELECT /*+ PARALLEL ( WRK_PAYEUR_REFERENTIEL, 20) */%WRK_PAYEUR_REFERENTIEL.STR_SUPPL1,%"
set linesize 300
set pagesize 1000
set verify off
col sql_text format a2000
select distinct sql_id,
substr(to_char(sql_text), 1, 2000) sql_text
from
dba_hist_sqltext
where
substr(sql_text, 1, 2000) like '&motif';
fonctions analytiquesdrop table bbb; create table bbb (a1 number, a2 varchar2(30)); create index bbb_1 on bbb(a1,a2); begin for i in 1..1000000 loop insert into bbb values(i,i); end loop; end;
overgénérer des dates successivesdefine owner=usersap define table=BALHDR define columns=OBJECT set pagesize 300 set verify off select distinct &columns, count(*) over (partition by &columns) cnt, round(((count(*) over (partition by &columns) * 100) / count(*) over (partition by 1)), 2) pct from &owner.."&table" order by 3;équivalent "over"define owner=usersap define table=BALHDR define columns=OBJECT set pagesize 300 set verify off col pct format 999.99 with A as ( select &columns, count(*) cnt from &owner.."&table" group by &columns ) select &columns, cnt, cnt*100/B."_total_" pct from A, (select sum(A.cnt) "_total_" from A) B order by 2, 1;dbms_randomselect trunc(dbms_random.value(1, 13)) month_no from all_objects where rownum <= 1200;
select the_date
from
(select (sysdate - 1) + level/24/2 the_date
from
dual
connect by level <= 48);
select sal, case when sal < 2000 then 'category 1'
when sal < 3000 then 'category 2'
when sal < 4000 then 'category 3'
else 'category 4'
end
from emp;
to_timestamp('12/07/2011 15:00', 'dd/mm/yyyyhh24:mi')
to_date(to_char(end_interval_time, 'yyyy/mon/dd hh24:mi'), 'yyyy/mon/dd hh24:mi')
diverscd $ORACLE_HOME/bin relink all
alter table TABLENAME allocate extent storage (datafile 'filename');
alter tablespace TABLESPACEOLDNAME rename to TABLESPACENEWNAME;
execute dbms_space_admin.tablespace_migrate_to_local('TABLESPACENAME');
alter database default tablespace TABLESPACE_NAME;
Pour tracer l'erreur ORA-6502 :
SQL> alter session set events '6502 trace name errorstack level 3';
SQL> begin if prvt_advisor.is_pack_enabled('DIAGNOSTIC') then dbsnmp.bsln_internal.maintain_statistics; end if; end;
/
écrire un message dans l'alert.log
exec sys.dbms_system.ksdwrt(2, 'message to print ...'|| to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
set pagesize 300
set linesize 300
set feedback off
col "Used (Mo)" format 999,999.99
col "Free (Mo)" format 999,999.99
select tablespace_name,
sum(bytes_used)/1024/1024 "Used (Mo)",
sum(bytes_free)/1024/1024 "Free (Mo)"
from
v$temp_space_header
group by tablespace_name;
TABLESPACE_NAME Used (Mo) Free (Mo)
------------------------------ ----------- -----------
TEMPTAB 55,344.81 13,155.19
col "Total (Mo)" format 999,999.99
col "Used (Mo)" format 999,999.99
col "Free (Mo)" format 999,999.99
select tablespace_name,
bytes_total/1024/1024 "Total (Mo)",
bytes_used/1024/1024 "Used (Mo)",
(bytes_total-bytes_used)/1024/1024 "Free (Mo)"
from
( select A.tablespace_name,
D.bytes_total,
sum(A.used_blocks*D.block_size) bytes_used
from
v$sort_segment A,
( select B.name,
C.block_size,
sum(C.bytes) bytes_total
from
v$tablespace B,
v$tempfile C
where
B.ts#=C.ts#
group by B.name, C.block_size
) D
where
A.tablespace_name=D.name
group by A.tablespace_name, D.bytes_total
);
TABLESPACE_NAME Total (Mo) Used (Mo) Free (Mo)
------------------------------- ----------- ----------- -----------
TEMPTAB 68,500.00 .00 68,500.00
set head off
set pagesize 0
with S as ( select S0.sid,
S0.serial#,
S0.username,
S0.logon_time,
S0.status,
S0.server,
S0.machine,
S0.terminal,
S0.program,
S0.paddr,
P0.spid,
S0.sql_address
from
v$session S0, v$process P0, v$sort_usage U0
where
S0.username is not null
and S0.paddr=P0.addr
and S0.saddr=U0.session_addr ),
A as ( select sql_id,
sql_text,
address
from
v$sql
where
address in (select distinct sql_address from v$session ) )
select 'sid:'||s.sid, 'serial#:'||s.serial#, a.sql_id, a.sql_text, 'username:'||s.username,
'logon:'||to_char(s.logon_time, 'dd/mm/yyyy hh24:mi:ss'), 'status:'||s.status, 'server:'||s.server,
'os process:'||s.spid, 'machine:'||s.machine, 'terminal:'||s.terminal, 'program:'||s.program
from
S, A
where
A.address=S.sql_address;
option RACcd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk rac_on ioracle 2>&1 | tee -a /tmp/rac_on.log
SQL> select value from v$option where parameter = 'Real Application Clusters'; VALUE ---------------------------------------------------------------- TRUE
Démarrage de ASMmmdf /FS mmchfs /dev/FS -F nRAC en Standard Edition => ASM
RAC en Enterprise Edition => ASM ou classic
Arrêt de ASMexport RAC_SID=+ASM sqlplus '/ as sysdba' startupoustartup nomount alter diskgroup mount all;oustartup mount restrict;
Interface ASMshutdown immediate
Démarrer RACasmcmd
Ordre de démarragesrvctl> start instance -d RAC -i RAC1 -n server1 CLUSTER_DATABASE = true / false (si Oracle doit considérer la base comme un cluster)
1) couche clusteradd and drop disks
2) listeners
3) ASM
4) databse / instances
# srvctl config service -d SID DVEBMGS20_ROMVND2ADJ PREF: SID1 AVAIL: SID2 D90_romvnd4adj PREF: SID1 AVAIL: SID2 D90_romvnd5adj PREF: SID2 AVAIL: SID1
exemplesSQL> alter diskgroup group1 add disk '/dev/raw/raw9', '/dev/raw/raw10'; SQL> alter diskgroup group2 drop disk group2_0003;
mount disk groupsSQL> create tablespace data datafile '+group1/data'; SQL> alter diskgroup group1 drop file '+group1/data'; SQL> set db_create_file_dest=+group1; SQL> create tablespace books;
vuesSQL> alter diskgroup group1 mount;(mount tous les diskgroups définis dans v$asm_diskgroup)SQL> alter diskgroup all mount;
documentationv$asm_template v$asm_operation
asmcmd FAQASM diskgroup
ASMCMD Instance Management Commands
Repairing or restoring an inconsistent OCR in RAC
set pagesize 300
set linesize 300
select group_number,
name,
block_size,
state,
type,
free_mb,
total_mb,
offline_disks
from
v$asm_diskgroup;
GROUP_NUMBER NAME BLOCK_SIZE STATE TYPE FREE_MB TOTAL_MB OFFLINE_DISKS
------------ ------------------------------ ---------- ----------- ------ ---------- ---------- -------------
1 OCLCPSSID01_ASM_GIDG 4096 MOUNTED NORMAL 1308 2100 0
set pagesize 300
set linesize 300
col path format a40
col header_status format a13
select name, group_number, disk_number, header_status, path from v$asm_disk order by 2, 1;
NAME GROUP_NUMBER DISK_NUMBER HEADER_STATUS PATH
------------------------------ ------------ ----------- ------------- ----------------------------------------
CRS_C3_1G_20_79063_0113 1 0 MEMBER /dev/oracle/crs_c3_1G_20_79063_0113
CRS_C5_1G_20_29666_0113 1 1 MEMBER /dev/oracle/crs_c5_1G_20_29666_0113
0 1 FORMER /dev/oracle/crs_c3_1G_21_79063_0116
0 0 MEMBER /voting_disk/vote_quorum
0 3 FORMER /dev/oracle/crs_c5_1G_21_29666_0116
set pagesize 300
set linesize 300
col mount_status format a12
col mode_status format a11
select name,
group_number,
disk_number,
mount_status,
mode_status,
state,
free_mb,
total_mb,
os_mb
from v$asm_disk order by 1;
NAME GROUP_NUMBER DISK_NUMBER MOUNT_STATUS MODE_STATUS STATE FREE_MB TOTAL_MB OS_MB
------------------------------ ------------ ----------- ------------ ----------- -------- ---------- ---------- ----------
CRS_C3_1G_20_79063_0113 1 0 CACHED ONLINE NORMAL 654 1050 1050
CRS_C5_1G_20_29666_0113 1 1 CACHED ONLINE NORMAL 654 1050 1050
0 1 CLOSED ONLINE NORMAL 0 0 1050
0 0 CLOSED ONLINE NORMAL 0 0 500
0 3 CLOSED ONLINE NORMAL 0 0 1050
alter session set nls_date_format='dd/mm/yyyy hh24:mi';
set pagesize 300
set linesize 300
col name format a26
col group_num format 999999999
col file_num format 99999999
col "Size (Mo)" format 9,999.99
col type format a24
col blocks format 999999
col perm format a9
select A.name,
F.group_number group_num,
F.file_number file_num,
F.block_size,
F.blocks,
F.bytes/1024/1024 "Size (Mo)",
F.space,
F.type,
F.creation_date,
F.modification_date,
F.permissions perm
from
v$asm_alias A,
v$asm_file F
where
F.file_number=A.file_number;
NAME GROUP_NUM FILE_NUM BLOCK_SIZE BLOCKS Size (Mo) SPACE TYPE CREATION_DATE MODIFICATION_DAT PERM
-------------------------- ---------- --------- ---------- ------- --------- ---------- ------------------------ ---------------- ---------------- ---------
REGISTRY.253.769360221 1 253 512 3 .00 2097152 ASMPARAMETERFILE 08/12/2011 15:10 08/12/2011 15:00 rw-rw-rw-
REGISTRY.255.769360223 1 255 4096 66591 260.12 550502400 OCRFILE 08/12/2011 15:10 17/12/2011 10:00 rw-rw-rw-
set pagesize 300
set linesize 300
col instance_name format a13
col software_version format a16
col compatible_version format a10
select * from v$asm_client;
GROUP_NUMBER INSTANCE_NAME DB_NAME STATUS SOFTWARE_VERSION COMPATIBLE
------------ ------------- -------- ------------ ---------------- ----------
1 +ASM2 +ASM CONNECTED 11.2.0.2.0 11.2.0.2.0
Oracle Cluster Registry (OCR) stores the cluster configuration information. It is a shared disk component, that must be accessible to all nodes in the cluster. The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.
commandes diverses
$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 1048296
Used space (kbytes) : 5116
Available space (kbytes) : 1043180
ID : 834229908
Device/File Name : /dev/vx/rdsk/ocrdg/ocrvol
Device/File integrity check succeeded
Device/File Name : /dev/vx/rdsk/ocrdg/ocrvol2
Device/File integrity check succeeded
$ ocrcheck -local -config
Oracle Local Registry configuration is :
Device/File Name : /ogi/grid/grid_oh/cdata/hugo.olr
$ cat /etc/oracle/ocr.loc
ocrconfig_loc=/dev/vx/rdsk/ocrdg/ocrvol
ocrmirrorconfig_loc=/dev/vx/rdsk/ocrdg/ocrvol2
local_only=FALSE
$ ls -l $ORACLE_HOME/srvm/admin/ocrlog.ini
-rw-r--r-- 1 oracle oinstall 564 Nov 24 2010 /ogi/grid/grid_oh/srvm/admin/ocrlog.ini
liste des commandes crsctl# crsctl check crs CRS-4638: Oracle High Availability Services is online CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online # srvctl config nodeapps Network exists: 1/10.122.25.0/255.255.255.0/en1, type static VIP exists: /saturne-vip/10.122.25.25/10.122.25.0/255.255.255.0/en1, hosting node saturne GSD exists ONS exists: Local port 6100, remote port 6200, EM port 2016 # srvctl status nodeapps VIP saturne-vip is enabled VIP saturne-vip is running on node: saturne Network is enabled Network is running on node: saturne GSD is disabled GSD is not running on node: saturne ONS is enabled ONS daemon is running on node: saturne # oifcfg getif en1 10.122.25.0 global public en4 150.150.150.0 global cluster_interconnect # crsctl status res # crsctl status res -t -init
logs CRScrsctl add crs administrator crsctl add css votedisk crsctl add serverpool crsctl check cluster crsctl check crs crsctl check resource crsctl check ctss crsctl config crs crsctl delete crs administrator crsctl delete css votedisk crsctl delete node crsctl delete serverpool crsctl disable crs crsctl enable crs crsctl get css crsctl get css ipmiaddr crsctl get nodename crsctl getperm serverpool crsctl lsmodules crsctl modify serverpool crsctl pin css crsctl query crs administrator crsctl query crs activeversion crsctl query crs releaseversion crsctl query crs softwareversion crsctl query css ipmidevice crsctl query css votedisk crsctl relocate resource crsctl relocate server crsctl replace discoverystring crsctl replace votedisk crsctl set css crsctl set css ipmiaddr crsctl set css ipmiadmin crsctl setperm serverpool crsctl start cluster crsctl start crs crsctl status server crsctl status serverpool crsctl stop cluster crsctl stop crs crsctl unpin css crsctl unset css
ressources$GRID_HOME/bin/diagcollection.pl –collect –crshome $GRID_HOME
recréer une ressource# crsctl status resource -p NAME=ora.scan2.vip TYPE=ora.scan_vip.type ACL=owner:root:rwx,pgrp:system:r-x,other::r--,group:oinstall:r-x,user:oracle:r-x ACTION_FAILURE_TEMPLATE= ACTION_SCRIPT= ... # crsctl status resource <resource> -p NAME=ora.scan2.vip TYPE=ora.scan_vip.type ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r-- ACTION_FAILURE_TEMPLATE= ACTION_SCRIPT= ... # crsctl status resource -t -init -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.asm 1 ONLINE ONLINE saturne Started ora.cluster_interconnect.haip 1 ONLINE ONLINE saturne ora.crsd 1 ONLINE ONLINE saturne ora.cssd 1 ONLINE ONLINE saturne ora.cssdmonitor 1 ONLINE ONLINE saturne ora.ctssd 1 ONLINE ONLINE saturne OBSERVER ora.diskmon 1 ONLINE ONLINE saturne ora.drivers.acfs 1 ONLINE ONLINE saturne ora.evmd 1 ONLINE ONLINE saturne ora.gipcd 1 ONLINE ONLINE saturne ora.gpnpd 1 ONLINE ONLINE saturne ora.mdnsd 1 ONLINE ONLINE saturne
crsctl stat res $res -p | sort > $res.txt
_type_=`grep '^TYPE=' $res.txt | sed "s/[^=]*=//"`
_server_pools_=`grep '^SERVER_POOLS=' $res.txt | sed "s/[^=]*=//"`
_stop_timeout_=`grep '^STOP_TIMEOUT=' $res.txt | sed "s/[^=]*=//"`
echo "## crsctl delete resource $res ##"
echo "crsctl add resource $res -type $_type_ -attr \"SERVER_POOLS=$_server_pools_, STOP_TIMEOUT=$_stop_timeout_\""
crsctl stat res $res -p | sort > $res.new.txt
diff $res.txt $res.new.txt | grep '< ' | sed "s/..//" | while read line
do
_param_=`echo "$line" | awk -F= '{print $1}'`
_value_=`echo "$line" | sed "s/[^=]*=//"`
[ "$_param_" != "" ] && [ "$_param_" != "ACL" ] && [ "$_param_" != "TYPE" ] && [ "$_param_" != "SERVER_POOLS" ] \
&& [ "$_param_" != "STOP_TIMEOUT" ] && echo "crsctl modify resource $res -attr \"$_param_='$_value_'\""
done
_acl_=`grep ACL= $res.txt | sed "s/ACL=//"`
echo $_acl_ | awk -F, '{print $1}' | awk -F: -v x=$res '{print "crsctl setperm resource " x " -o " $2}'
echo $_acl_ | awk -F, '{print $2}' | awk -F: -v x=$res '{print "crsctl setperm resource " x " -g " $2}'
echo $_acl_ | awk -F, -v x=$res '{print "crsctl setperm resource " x " -u " $1}'
echo $_acl_ | awk -F, -v x=$res '{print "crsctl setperm resource " x " -u " $2}'
echo $_acl_ | awk -F, -v x=$res '{print "crsctl setperm resource " x " -u " $3}'
echo; echo "## executer les commandes generees ci-dessus ##"; echo
crsctl stat res $res -p | sort > $res.new.txt
echo "differences :"
diff $res.txt $res.new.txt
modifier un serverpool# cat /tmp/servpool_attr.txt IMPORTANCE=1 MIN_SIZE=1 MAX_SIZE=2 SERVER_NAMES=server1 server2 PARENT_POOLS=Generic EXCLUSIVE_POOLS=testsp ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r-- # crsctl add serverpool <name> -file /tmp/servpool_attr.txt
le statutsrvctl modify serverpool -g <name> -n "<node1>,<node2>" -f crsctl modify serverpool <name> -attr "SERVER_NAMES='<node1>,<node2>'"
fichiers associés# crsctl status serverpool ora.SID_AS_204 -p NAME=ora.SID_AS_204 IMPORTANCE=999 MIN_SIZE=1 MAX_SIZE=2 SERVER_NAMES=(server1 server2) PARENT_POOLS= EXCLUSIVE_POOLS= ACL=owner:oracle:rwx,pgrp:oinstall:r-x,other::r--
check_crs_config.ksh
backup_resource.ksh
backup_serverpool.ksh
create_resource.ksh
create_serverpool.ksh
define thread=1
set head off
set linesize 300
set verify off
select 'alter database add logfile thread &thread group &thread'||next_number||' (
''/oracle/'||D.name||'/origlog'||L.next_letter||'/log_g&thread'||next_number||'m1.dbf'',
''/oracle/'||D.name||'/mirrlog'||L.next_letter||'/log_g&thread'||next_number||'m2.dbf''
)
size '||L.bytes||';'
from
v$database D,
( select bytes,
M.last_number+1 next_number,
decode(mod(M.last_number+1, 2), 1, 'A', 'B') next_letter
from
v$log,
( select max(group#) last_group,
regexp_replace(max(group#), '^.', '') last_number
from
v$log
where
thread#=&thread ) M
where
thread#=&thread
and group#=M.last_group ) L;
alter database add logfile thread 1 group 19 (
'/oracle/SID/origlogA/log_g19m1.dbf',
'/oracle/SID/mirrlogA/log_g19m2.dbf'
)
size 1258291200;
define count=8
define thread=1
define bytes=1000
set head off
set pagesize 0
set linesize 300
set verify off
select 'alter database add logfile thread &thread group &thread'||n||' (''/oracle/'||name||'/origlog'||letter||'/log_g&thread'||n||'m1.dbf'',
''/oracle/'||name||'/mirrlog'||letter||'/log_g&thread'||n||'m2.dbf'') size &bytes;'
from
( select level n,
decode(mod(level, 2), 1, 'A', 'B') letter
from
dual
connect by level <= &count ),
( select name from v$database );
set pagesize 10000
set linesize 300
col group# format a6
col thread# format 9999999
col member format a40
col "Mo" format 9,999.99
col expected_member format a40
col expected_group# format 999999
select TAB.thread#,
lpad((case when TAB.group#=TAB.expected_group# then null else '* ' end || TAB.group#), 6) group#,
TAB.expected_group#,
(case when TAB.member=TAB.expected_member then null else '* ' end || TAB.member) member,
TAB.expected_member,
TAB."Mo"
from (
select LOG.*,
to_number(LOG.thread#||trunc((rownum-1)/2+1)) expected_group#,
'/oracle/'||(select name from v$database)||'/'||decode(mod(rownum-1, 2), 0, 'origlog', 'mirrlog')||decode(mod(trunc((rownum-1)/2), 2), 0, 'A', 'B')
||'/log_g'||LOG.thread#||trunc((rownum-1)/2+1)||'m'||decode(mod(rownum-1, 2), 0, 1, 2)||'.dbf' expected_member
from (
select LF.group#,
L.thread#,
LF.member member,
L.bytes/1024/1024 "Mo"
from
v$log L,
v$logfile LF
where
L.group#=LF.group#
order by 1, 3 desc
) LOG
) TAB;
set pagesize 10000
set linesize 300
col name format a40
col tablespace_name format a40
col tbs format a20
col file_name format a60
break on tablespace_name skip 1
select tablespace_name,
(case when regexp_like(file_name, '^/oracle/'||(select name from v$database)||'/sapdata\d{1,2}/'||tbs||'_(\d{1,})/'||tbs||'\.data\1')
then ' ' else '* ' end)||file_name file_name
from
( select tablespace_name,
lower(regexp_replace(tablespace_name, '^PSAP', '')) tbs,
file_name,
regexp_replace(file_name, '^/oracle/'||(select name from v$database)||'/sapdata\d{1,2}/', '') short_file_name
from
dba_data_files
)
order by 1, short_file_name;
define new_size=2G
set head off
set pagesize 0
set linesize 300
set feedback off
set verify off
select 'alter database drop logfile group '||L.group#||';'||chr(10)||
'alter database add logfile thread '||L.thread#||' group '||L.group#||' ('||chr(10)||
' '''||LF1.member||''','||chr(10)||
' '''||LF2.member||''') size &new_size reuse;'
from
v$log L,
v$logfile LF1,
v$logfile LF2
where
L.group#=LF1.group#
and L.group#=LF2.group#
and LF1.member > LF2.member
order by L.thread#, L.group#;
set head off
set pagesize 0
set linesize 300
select 'alter database add logfile thread 2 group '||group#||' (''/oracle/'||name||'/mirrlog'||letter||'/log_g'||group#||'m1.dbf'',
''/oracle/'||name||'/mirrlog'||letter||'/log_g'||group#||'m2.dbf'') size '||bytes||';'
from
( select thread#,
regexp_replace(group#, '^.', '2') group#,
bytes,
decode(mod(group#, 2), 1, 'A', 'B') letter
from v$log ),
v$database
where
thread#=1;
set head off
set pagesize 0
set linesize 300
set verify off
col maxbytes format 999999999999999999999
select '!mkdir '||directory||chr(10)||
decode (rownum, 1, 'create undo', 'alter')||' tablespace '||undo_tablespace||'2',
decode (rownum, 1, '', ' add')||' datafile '||cmd_datafile
from (
select directory,
''''||directory||'/'||short_name||'2.data'||rownum||''' size '||bytes||' '||decode(autoextensible, 'YES', ' autoextend on maxsize '||maxbytes)||';' cmd_datafile,
undo_tablespace
from
( select undo_tablespace,
regexp_replace(file_name, '\/[^\/]+\/[^\/]+$', '')||'/'||short_name||'2_'||rownum directory,
short_name,
autoextensible,
bytes,
maxbytes
from
dba_data_files,
( select value undo_tablespace,
lower(regexp_replace(value, 'PSAP', '')) short_name
from
v$parameter
where
name = 'undo_tablespace' )
where
tablespace_name=undo_tablespace ) );
define sid1=SID1 define sid2=SID2 set verify off -- alter system set local_listener=LISTENER_PRDA1 scope=spfile sid='&sid1'; -- alter system set local_listener=LISTENER_PRDA2 scope=spfile sid='&sid2'; alter system set cluster_database=true scope=spfile; alter system set cluster_database_instances=2 scope=spfile; alter system set undo_tablespace=PSAPUNDO scope=spfile sid='&sid1'; alter system set undo_tablespace=PSAPUNDO2 scope=spfile sid='&sid2'; alter system set thread=1 scope=spfile sid='&sid1'; alter system set thread=2 scope=spfile sid='&sid2'; alter system set instance_number=1 scope=spfile sid='&sid1'; alter system set instance_number=2 scope=spfile sid='&sid2'; alter database enable public thread 2;
la version du catalogueSQL> alter database enable block change tracking using file 'xxxxxx.bct'; SQL> alter database disable block change tracking; -- efface le fichier précédemment créé
SQL> select * from rman.rcver; VERSION ------------ 11.01.00
décataloguer (UNCATALOG)$ rman target / RMAN> startup nomount; RMAN> restore spfile from 'xxxxxxx.bkp'; RMAN> startup force nomount; RMAN> restore controlfile from 'xxxxxxx.bkp'; RMAN> startup force mount; RMAN> catalog start with 'xxxxxxx.bkp'; RMAN> restore database; RMAN> recover database; RMAN> alter database open resetlogs;
RMAN backup statusRMAN> change archivelog all uncatalog; RMAN> change backup of tablespace users uncatalog; RMAN> change backuppiece '/oradata2/oft7qq' uncatalog;
set pagesize 3000
set linesize 300
col operation format a36
col object_type format a30
col gbytes_processed format 999,999.99
col input_gbytes format 999,999.99
col output_gbytes format 999,999.99
alter session set nls_date_format='yyyy/mm/dd_hh24';
select db_name,
start_time start_time,
end_time end_time,
object_type,
sum(mbytes_processed)/1024 gbytes_processed,
sum(input_bytes)/1024/1024/1024 input_gbytes,
sum(output_bytes)/1024/1024/1024 output_gbytes
from
rman.rc_rman_status
where
operation='BACKUP'
group by db_name, start_time, end_time, object_type
order by 1, 2;
deleteRMAN> crosscheck archivelog all; RMAN> crosscheck backup; RMAN> crosscheck backup of archivelog all spfile; RMAN> crosscheck backup of tablespace <tbs>; RMAN> crosscheck backup of tablespace <tbs> completed before 'sysdate-14'; RMAN> crosscheck backup of datafile '<datafile>'; RMAN> crosscheck backup of datafile '<datafile>' completed after 'sysdate-14'; RMAN> crosscheck backupset; RMAN> crosscheck backupset 1338, 1339, 1340; RMAN> crosscheck copy of database; RMAN> crosscheck backuppiece tag = 'nightly_backup'; RMAN> crosscheck controlfilecopy '/tmp/control01.ctl'; RMAN> crosscheck datafilecopy 113, 114, 115;
backupRMAN> delete archivelog all; RMAN> delete archivelog all backed up <n> times to device type tape; RMAN> delete archivelog all backed up <n> times to device type disk; RMAN> delete expired archivelog all; RMAN> delete expired backup of tablespace <tbs> completed before 'sysdate-14'; RMAN> delete backup of database like '/tmp%'; RMAN> delete backupset 101, 102, 103; RMAN> delete backup of spfile tablespace <tbs>; RMAN> delete controlfilecopy '/tmp/cntrlfile.copy';
listRMAN> backup archivelog all; RMAN> backup archivelog all not backed up; RMAN> backup archivelog from sequence 288 until sequence 388 thread 1 delete input; RMAN> backup archivelog all from time 'sysdate-10' delete all input; RMAN> backup as copy database; RMAN> backup database;
RMAN> list archivelog all; RMAN> list archivelog all backed up <n> times; RMAN> list archivelog all backed up <n> times to device type tape; RMAN> list archivelog all backed up <n> times to device type disk; RMAN> list backup [summary]; RMAN> list backup [by file]; RMAN> list backup of archivelog all; RMAN> list backup of archivelog from sequence 1437; RMAN> list backup of controlfile; RMAN> list backup of database; RMAN> list backup of datafile 1 [summary]; RMAN> list backup of spfile; RMAN> list backup of tablespace <tbs> [summary]; RMAN> list backup summary; RMAN> list backupset; RMAN> list backupset of datafile 1; RMAN> list copy of database archivelog all; RMAN> list copy of datafile 1, 2, 3; RMAN> list controlfilecopy "/tmp/cntrlfile.copy"; RMAN> list expired archivelog all; RMAN> list expired backup [of archivelog all] [summary]; RMAN> list expired backup of datafile 10; RMAN> list expired backupset; RMAN> list incarnation; RMAN> list failure; RMAN> list failure 641231 detail; RMAN> advise failure; RMAN> list recoverable backup;
lsvg | while read vg
do
echo $vg
echo -----------------------
lsvg $vg 2>/dev/null | grep PPs:
echo
lsvg -l $vg 2>/dev/null | while read line
do
fs=`echo "$line" | awk '{print $NF}'`
df_gp=`df -gP $fs 2>/dev/null | grep '^/dev/' | grep '%' | sed "s/^[^ ][^ ]*//" | sed "s/ *[^ ][^ ]*$//"`
echo "$line" | grep "LV NAME" >/dev/null
if [ $? -eq 0 ] ; then
echo "$line GB blocks Used Available Capacity"
else
printf "%s" "$line"
n=`printf "%s" "$line" | wc -c`
n=`expr 92 - $n`
printf "%${n}s" " "
printf " %6s %6s %6s %6s\n" $df_gp
fi
done
echo
echo =======================================================================
done
# lslv lvSIDsd1 LOGICAL VOLUME: lvSIDsd1 VOLUME GROUP: yx91vg_app02 LV IDENTIFIER: 00f6679a00004c000000013340250f73.1 PERMISSION: read/write VG STATE: active/complete LV STATE: opened/syncd TYPE: jfs2 WRITE VERIFY: off MAX LPs: 512 PP SIZE: 128 megabyte(s) COPIES: 1 SCHED POLICY: parallel LPs: 76 PPs: 76 STALE PPs: 0 BB POLICY: relocatable INTER-POLICY: maximum RELOCATABLE: yes INTRA-POLICY: middle UPPER BOUND: 128 MOUNT POINT: /oracle/SID/sapdata1 LABEL: /oracle/SID/sapdata1 MIRROR WRITE CONSISTENCY: on/ACTIVE EACH LP COPY ON A SEPARATE PV ?: yes Serialize IO ?: NO # df -gP /oracle/SID/sapdata1 Filesystem GB blocks Used Available Capacity Mounted on /dev/lvSIDsd1 9.50 4.88 4.62 52% /oracle/SID/sapdata1rem : LPs * PPs / 1024# echo "scale=2; 76*128/1024" | bc 9.50Changer le nombre de LPs :# chlv -x 700 lvSIDsd1
modifier un file system# mklv -e x -t jfs2 -y <lv> <vg> 3000m # crfs -v jfs2 -d <lv> -m /mnt -A yes -p rw # mount /mntavec block size 512 :# mklv -ex -t jfs2 -y <lv> <vg> 4G # crfs -v jfs2 -d <lv> -m /mnt -Ayes -prw -a agblksize=512 # mount /mntPour Oracle 10g :# mklv -e x -t jfs2 -y <lv> <vg> 5632m # crfs -v jfs2 -d <lv> -m /mnt # mklv -e x -t jfs2 -y <lv> <vg> 10000m # crfs -v jfs2 -d <lv> -m /mnt -A yes -p rw
augmenter un FSsupprimer un file system# chfs -a size=+$((50*2048)) /mnt # augmente le /mnt de 50 Mo # chfs -a size=+50M /mnt # augmente le /mnt de 50 Mo # chfs -a size=50M /mnt : augmente le /mnt pour un final de 50 Mochanger le point de montage /mnt vers /mnt2# umount /mnt # chfs -m /mnt2 /mnt # mount /mnt2
# umount /mnt # rmfs /mnt # rmfs -r /mnt # suprimer également le point de montage
connaître l'état des noeuds# mmlsconfig Configuration data for cluster gclcpssid01.romvn01abc: ---------------------------------------------------- clusterName XXXXXXXX clusterId 754943471146491640 autoload no minReleaseLevel 3.3.0.2 dmapiFileHandleSize 32 leaseDuration 10 prefetchThreads 72 [node1,node2] prefetchThreads 488 [common] pagepool 10G maxMBpS 3000 worker1Threads 48 [node1,node2] worker1Threads 880 [common] maxFilesToCache 7500 maxStatCache 3000 [mynode1,mynode2] nsdThreadsPerDisk 16 nsdMaxWorkerThreads 132 maxReceiverThreads 128 seqDiscardThreshhold 104857600 [common] adminMode allToAll File systems in cluster gclcpssid01.romvn01abc: --------------------------------------------- /dev/xxxx1 /dev/xxxx2 /dev/xxxx3 /dev/xxxx4
# mmgetstate -a
Node number Node name GPFS state
------------------------------------------
1 node1 active
2 node2 active
démarrer le GPFS sur tous les noeuds# mmstartup
monter tous les file systems GPFS sur le noeud courant# mmstartup -a
monter tous les file systems GPFS sur tous les noeuds# mmmount all
afficher les montages GPFS# mmmount all -a
check des file systems GPFS# mmlsmount all
démonter tous les file systems GPFS sur le noeud courant# mmfsck /dev/xxxx_yyyy
démonter tous les file systems GPFS sur tous les noeuds# mmumount all
arrêter le GPFS sur le noeud courant# mmumount all -a
arrêter le GPFS sur tous les noeuds# mmshutdown all
# mmshutdown all -a
création du compte :groups# mkuser -R files id='3402' admin='true' pgrp='grd' groups='dba' admgroups='grd' sugroups='grd,dba' home='/gridagt' shell='/usr/bin/ksh' gecos='Grid Agent Administrator' pwdwarntime='-1' minlen='6' fsize='-1' cpu='-1' data='-1' fsize='-1' rss='-1' rss_hard='-1' stack='-1' stack_hard='-1' SYSTEM='compat' registry='files' grdachanger les paramètres d'un compte AIX :# pwdadm -c orahw0 # chuser umask=022 root
création du groupe :# mkgroup -'a' id='1400' grd
désinstaller un package :informations système# installp -u nompackagelister les packages installés :# lslpp -l | grep nompackage
les attributs AIX :# lsattr -l -E sys0 # chdev -l sys0 -a maxuproc=2600 # lslpp -ha | grep motif motif.Ucanop001 motif.Uenvmep02 # installp -u motif.Ucanop001nombre de processeurs :# lsdev -Cc processor -S available | wc -l 3fréquence :# lsdev -Cc processor -S available -F name | xargs -n1 lsattr -a frequency -F value -El 1499952520 1499952520 1499952520mémoire :# lsattr -El mem0 -F value -a size 8192 # lsattr -E -l sys0 -a realmem | awk '($1=="realmem"){printf("%d\n",$2/1024/1024)}'processeurs :# lparstat -i # lsdev -C | grep proc | grep Available | wc -l # vmstat | head -2 # lsdev -Cc processor | wc -l # lscfg -vpl proc01swap :# lsps -s | grep MB | cut -d 'MB' -f 1 | awk '{printf("%d\n",$1/1024)}'liste de APARs (Authorized Problem Analysis Reports) :IY70159: KRTL relocation problem IY66513: Parsing of LDR_CNTRL value fails IY68989: write to mmapped space hangs /usr/sbin/instfix -ik IY70159 /usr/sbin/instfix -ik IY66513 /usr/sbin/instfix -ik IY68989 All filesets for IY70159 were found. All filesets for IY66513 were found. All filesets for IY68989 were founddiagnostiquer les erreurs :ADAPTATEUR ERROR SOFTWARE PROGRAM ERROR # errpt | grep SOFTWARE B8FBD189 0206185407 T S fscsi2 SOFTWARE PROGRAM ERROR B8FBD189 0206185407 T S fscsi2 SOFTWARE PROGRAM ERROR B8FBD189 0206180507 T S fscsi0 SOFTWARE PROGRAM ERROR B8FBD189 0206180507 T S fscsi0 SOFTWARE PROGRAM ERROR # errpt -aj B8FBD189 | head --------------------------------------------------------------------------- LABEL: FSCSI_ERR6 IDENTIFIER: B8FBD189 Date/Time: Tue Feb 6 18:54:03 2007 Sequence Number: 5331 Machine Id: 0054B1BE4C00 Node Id: clay1itn Class: S Type: TEMPles disques :# lspv | grep hdisk79 lsdev -Cc diskarrêter un serveur AIX :# shutdown -F now (sans reboot) # shutdown -Fr now (avec reboot)
calcul avec bc :$ echo "scale=2; 14 / 100" | bc 0.14cherche une erreur dans un fichier :# egrep -i "not|err|warning|severe|fail|abort|doesn't|can't|ora-|exit|unsuccessfully|\[[0-9]*\]|No such file" fichier.logaffiche les 50 dernières commandes Shell :# fc -t 50commandes diverses# date '+%Y-%m-%d_%H%M' 2010-11-30_1124 # date '+%Y-%m-%d_%H%M%S' 2010-11-30_112427
set linesize 300
set feedback off
set head off
set verify off
select 'select userid, regexp_replace(userid, ''-CRYPT'', ''''), passwd from '||owner||'.'||table_name||';'||chr(10)||chr(10)||
'define pass_excel='||chr(10)||
'define user_sap='||chr(10)||chr(10)||
'alter profile '||USR.profile||' limit password_verify_function null;'||chr(10)||
'alter user SYSTEM identified by usersystem;'||chr(10)||
'!brconnect -u system/usersystem -f chpass -o '||'&'||'user_sap -p '||'&'||'pass_excel'||chr(10)||
'alter user SYSTEM identified by values '''||U.password||''';'||chr(10)||
'alter profile '||USR.profile||' limit password_verify_function '||P.limit||';'||chr(10)||chr(10)||
'R3trans -x'
from
user$ U,
dba_tables T,
dba_users USR,
dba_profiles P
where
U.name='SYSTEM'
and T.table_name='SAPUSER'
and USR.username=U.name
and USR.profile=P.profile
and P.resource_name='PASSWORD_VERIFY_FUNCTION';
statussid=SID # su - `echo $sid | tr '[A-Z]' '[a-z]'`adm -c "sapctl start all -sapsid $sid" (su - sidadm -c "sapctl start all -sapsid SID") # su - `echo $sid | tr '[A-Z]' '[a-z]'`adm -c "sapctl stop all -sapsid $sid" (su - sidadm -c "sapctl stop all -sapsid SID")
sid=SID # su - `echo $sid | tr '[A-Z]' '[a-z]'`adm -c "sapctl status all -sapsid $sid" (su - sidadm -c "sapctl status all -sapsid SID")
mysql> connect; mysql> create user sppyw; mysql> set password for sppyw = password('sppyw'); mysql> grant all privileges on sppyw.* to sppyw@'%';
merge pdf$ pdftk largepdfile.pdf burst
convert pdf to jpeg$ pdftk *.pdf cat output onelargepdfile.pdf
convert jpeg to pdf$ convert input_file_name.pdf output_file_name.jpeg $ convert -density 300 input_file_name.pdf output_file_name.jpeg $ convert file_name.pdf file_name.tiff
$ convert input_file_name.jpeg -adjoin output_file_name.pdf
rendre le fond blanc transparentconvert -crop 1021x513+122+206 img.jpeg img.new.jpeg
concaténer 2 imagesconvert input.jpeg -transparent white output.jpeg
ajouter un bordconvert input1.jpeg input2.jpeg -append output.jpeg convert input1.jpeg input2.jpeg +append output.jpeg
Examples of ImageMagick Usage (Version 6)convert label.jpeg -bordercolor white -border 20 label2.jpeg
lister les clés$ gpg --gen-key
encrypter un fichier$ gpg --list-key
décrypter un fichier$ gpg -e --recipient key_name file_to_encrypt $ gpg -e --recipient key_name --output file_to_encrypt.gpg file_to_encrypt $ cat file_to_encrypt | gpg -e --recipient key_name > file_to_encrypt.gpg $ ls -l file_to_encrypt.gpg
supprimer une paire de clés$ gpg -d encrypted_file.gpg > decrypted_file
$ gpg --delete-secret-and-public-key <cle>
décoder un fichier$ uuencode file_to_encode file_to_encode > file_to_encode.uue $ cat file_to_encode | uuencode file_to_encode > file_to_encode.uue
$ uudecode -o decoded_file file_to_decode
backup$ find /dir -print | cpio -ocB > backup_dir.cpio
list contents of the backup$ cat backup_dir.cpio | cpio -itcvB
retrieve a file from the backup$ cat backup_dir.cpio | cpio -icdBum file.name
restore the backup$ cat backup_dir.cpio | cpio -icdBum
copy directory structure$ find dir1/ -print | cpio -pmd dir2/