| Note |
SQL |
Remark |
| 33516 |
alter tablespace PSAPTEMP default storage (INITIAL 10M NEXT 1M MINEXTENTS 1 MAXEXTENTS 1000); |
|
| 16277 |
alter rollback segment prs_1 storage (maxextents 800); |
|
|
select PERNR, LGA01, BET01, LGA02, BET02, LGA03, BET03, LGA04, BET04, LGA05, BET05, LGA06, BET06, LGA07, BET07, LGA08, BET08, LGA09, BET09, LGA10, BET10, LGA11, BET11, LGA12, BET12, LGA13, BET13, LGA14, BET14, LGA15, BET15, LGA16, BET16, LGA17, BET17, LGA18, BET18, LGA19, BET19, LGA20, BET20 from SAPR3.PA0008 where MANDT=480 and PERNR=’00200589′; |
|
|
select name from v$database; |
To determine DB_NAME |
|
select instance from v$thread; |
To determine instance (SID) |
|
select * from global_name; |
To determine Global Database Name |
| 619188 |
SELECT SUBSTR(EVENT, 1, 30), TIME_WAITED, AVERAGE_WAIT FROM V$SYSTEM_EVENT ORDER BY TIME_WAITED DESC; |
To find which wait event slowing down Oracle system (exclude idle times) |
|
|
|
| 151603 |
alter database tempdb modify file (name = tempdev, size = 250MB)
alter database tempdb modify file (name = templog, size = 50MB) |
SQL Server |
| 363018 |
ALTER DATABASE PRD ADD LOG FILE (NAME = PRDLOG2, FILENAME = ‘l:\PRDLOG\PRDLOG2.ldf’, SIZE = 512, MAXSIZE = UNLIMITED, FILEGROWTH = 100MB) |
SQL Server |
|
ALTER DATABASE PRD MODIFY FILE (NAME = ‘PRDLOG’,SIZE = 2048) |
|
| Table and Index size sort |
SELECT SEGMENT_NAME, BYTES FROM DBA_SEGMENTS WHERE OWNER=’SAPR3′ ORDER BY BYTES; |
DBEXPORT |
| Number of Rows in table (provided statistic is updated) |
SELECT TABLE_NAME, NUM_ROWS FROM DBA_TABLES ORDER BY NUM_ROWS; |
|
| Tablespace storage parameter |
SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME=’PSAPTEMP’; |
DBEXPORT |
| Monitor PSAPTEMP use during db export (PSAPTEMP should be twice as biggest table size) |
SELECT SUM(BYTES), SUM(EXTENTS) FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=’PSAPTEMP’; |
DBEXPORT |
| 609430 |
select name from v$obsolete_parameter where isspecified=’TRUE’; |
ORA-32004: obsolete and/or deprecated parameter(s) specified |
|
select attr,val from UME_STRINGS where PID like ‘%iffah%’; |
portal |
|
alter database datafile ‘/oracle/JP1/sapdata6/rolll_1/roll.data1’ resize 500M; |
|
| 690241 |
ALTER SYSTEM FLUSH SHARED_POOL |
|
| SAP Table entries |
SELECT TABLE_NAME, NUM_ROWS FROM DBA_TABLES inner join (select OBJ_NAME from SAPRP1.TADIR inner join SAPRP1.DD02L on SAPRP1.DD02L.TABNAME=SAPRP1.TADIR.OBJ_NAME where PGMID=’R3TR’ and OBJECT=’TABL’and (TABCLASS=’TRANSP’ or TABCLASS=’POOL’) and DEVCLASS in (‘VAR’,’VBAS’,’VS’,’VSCORE’)) on TABLE_NAME=OBJ_NAME ORDER BY NUM_ROWS; |
|
ALTER DATABASE ADD TEMPFILE …. |
|
| Unlock, reset, enable SAP* |
select BNAME,UFLAG from SAPSR3.USR02 where MANDT=800 and BNAME=’SAP*’;
update SAPSR3.USR02 set UFLAG=0 where MANDT=001 and BNAME=’SAP*’;
delete from SAPSR3.USR02 where MANDT=810 and BNAME=’SAP*’; |
|
|
create spfile from pfile= |
|
| Generate log file: |
ALTER SYSTEM SWITCH LOGFILE; |
|
| Enabling Archivelog |
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ARCHIVE LOG LIST;
ALTER DATABASE OPEN; |
|
| Disabling Archivelog |
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ARCHIVE LOG LIST;
ALTER DATABASE OPEN; |
|
| SAP Note 600513 – ORA-25153 after recovery due to missing tempfiles |
select tablespace_name,file_name from dba_data_files where tablespace_name=’PSAPTEMP’;
select tablespace_name,file_name from dba_data_files where tablespace_name=’PSAPUNDO; |
|
| alter tablespace PSAPTEMP add tempfile ‘<filename>’ reuse; |
|
CREATE TEMPORARY TABLESPACE “PSAPTEMP” TEMPFILE ‘<filename>’ SIZE 2047984K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10240K; |
|
| oracle import monitor (imp) |
select substr(sql_text,instr(sql_text,’INTO “‘),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like ‘INSERT %INTO “%’
and command_type = 2
and open_versions > 0; |
|
| Lobsegment |
SELECT owner, table_name, column_name, segment_name, index_name
FROM DBA_LOBS
WHERE segment_name = ‘SYS_LOB0017931286C00016$$’; |
|
| Lobindex |
SELECT owner, table_name, column_name, segment_name, index_name
FROM DBA_LOBS
WHERE index_name = ‘SYS_IL0021404147C00007$$’; |
|
| Move/rename data file |
STARTUP MOUNT;
ALTER TABLESPACE <tablespace name> OFFLINE;
ALTER TABLESPACE alter tablespace <tablespace name> ONLINE
ALTER TABLESPACE <tablespace name> RENAME DATAFILE ‘<fully qualified path to original data file name>’ TO ‘<new or original fully qualified path to new or original data file name>’; |
or use brtools (space management) |
| See table extents inside datafile |
SELECT owner, segment_name FROM dba_extents a, dba_data_files b WHERE a.file_id = b.file_id AND b.file_name = ‘/oracle/AOP/sapdata6/sr3_38/sr3.data38’; |
or use brtools (segments management) |
| ORA 1187 |
alter database tempfile ‘/oracle/AOP/sapdata2/temp_1/temp.data1’ drop;
alter tablespace PSAPTEMP add tempfile ‘/oracle/AOP/sapdata2/temp_1/temp.data1’ reuse;
alter database tempfile ‘/oracle/AOP/sapdata2/temp_1/temp.data1’ autoextend on maxsize 10000M; |
|
| Alter database datafile |
alter database datafile ‘/oracle/AOP/sapdata1/sr3700_4/sr3700.data4’ autoextend off;
alter database datafile ‘/oracle/AOP/sapdata1/system_2/system.data2’ autoextend on maxsize 5000M; |
|
|
select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = ‘PSAPSR3’ and file_id = 67; |
|
|
SELECT substr(owner,1,5), substr(segment_name,1,5),block_id,blocks, bytes FROM dba_extents WHERE file_id = 67; |
|
|
select file_id, block_id, blocks, bytes, ‘Free’ from dba_free_space where tablespace_name = ‘PSAPSR3’ and file_id = 67 and rownum < 7 order by block_id desc; |
|
| See last free space in all datafiles for a tablespace (for shrinking) |
select sfs.file_id, sfs.block_id, sfs.bytes from dba_free_space sfs inner join
(select fs.file_id, max(fs.block_id) maxblock from dba_free_space fs inner join (select distinct file_id from dba_data_files where tablespace_name=’PSAPSR3′) df on fs.file_id=df.file_id group by fs.file_id order by fs.file_id) fidmaxb
on sfs.file_id=fidmaxb.file_id and sfs.block_id=fidmaxb.maxblock order by sfs.bytes desc; |
|
| See last free space in a datafile (for shrinking) |
select file_id, block_id, blocks, bytes, ‘Free’ from dba_free_space fs inner join dba_data_files df on fs.file_id = df.file_id where tablespace_name = ‘PSAPSR3’ and rownum < 7 order by block_id desc; |
|
|
|
|
|
select fs.file_id, max(fs.block_id) from dba_free_space fs inner join (select distinct file_id from dba_data_files where tablespace_name=’PSAPSR3′) df on fs.file_id=df.file_id group by fs.file_id order by fs.file_id; |
|
| Cluster table |
SELECT owner, table_name, column_name, segment_name, index_name FROM DBA_LOBS
WHERE table_name = ‘ATAB’; |
|
| select tabname, varkey from sapsr3.atab where tabname=’TVCPL’; |
|
| select count(*) from sapsr3.atab where tabname=’TVCPL’; |
|
| Memory |
Note 570375 – SAP on Sun Solaris: Swap Space and Paging |
|
| PSAPTEMP error during backup after system copy |
alter database tempfile ‘/oracle/AOP/sapdata2/temp_1/temp.data1’ drop including datafiles;
alter tablespace PSAPTEMP add tempfile ‘/oracle/AOP/sapdata2/temp_1/temp.data1’ size 2048M autoextend on maxsize 10000M; |
|
| Check archive log mode |
ARCHIVE LOG LIST |
|
|
select grantee, granted_role, default_role from dba_role_privs where grantee=’SYSTEM’; |
|
ROLANDO - MY SHARING BLOG 