SAP BASIS,HANA AND ORACLE DBA BLOGS

FORMATTING OPTIONS AS FOLLOWS: Line and Page display format. SQL> SET LINES 150 SQL> SET PAGESIZE 500 TO FORMAT COLUMNS SQL> column username format a18 ==================================== SQL> SELECT MAX(LENGTH(USERNAME)), MAX(LENGTH(ACCOUNT_STATUS)),MAX(LENGTH(DEFAULT_TABLESPACE)),MAX(LENGTH( TEMPORARY_TA LESPACE)) FROM DBA_USERS; MAX(LENGTH(USERNAME)) MAX(LENGTH(ACCOUNT_STATUS)) MAX(LENGTH(DEFAULT_TABLESPACE)) --------------------- --------------------------- ------------------------------- MAX(LENGTH(TEMPORARY_TABLESPACE)) ---------------------------------                    18                          16                               6                                 4 SQL> CO...

SQL> DESC DBA_OBJECTS Name Null? Type ----------------------------------------------------- -------- ------------------ OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GE...

# # Copyright (c) 1991, 1997, 1998 by Oracle Corporation # ############################################################################## # Example INIT.ORA file # # This file is provided by Oracle Corporation to help you customize # your RDBMS installation for your site.  Important system parameters # are discussed, and example settings given. # # Some parameter settings are generic to any size installation. # For parameters that require different values in different size # installations, three scenarios have been provided: SMALL, MEDIUM # and LARGE.  Any parameter that needs to be tuned according to # installation size will have three settings, each one commented # according to installation size. # # Use the following table to approximate the SGA size needed for the # three scenarious provided in this file: # #                     -------Installation/Database Size------ #         ...

SQL> create table temp_jp (num int,name char(4)); SQL> DECLARE  BEGIN  FOR i in 1..1000 loop  if mod(i,2)=0 then  insert into temp_jp values(i,'ram');  else  insert into temp_jp values(i,'sita');  end if;  end loop;  end;  / SQL> Create index idx_temp_jp on temp_jp(num); SQL> set timing on SQL> explain plan for select count(*) from temp_jp where name='sita'; Explained. Elapsed: 00:00:00.15 SQL> select * from table(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2917205682 ------------------------------------------------------------------------------ | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT   |   ...

View Oracle Sessions Information ========================= Check Current Situation ========================= SELECT name, value    FROM V$PARAMETER  WHERE name like '%session%' OR  name like '%process%' NAME                           VALUE ------------------------------ --------------------- processes                      1000 sessions                       1528 session_cached_cursors         100 job_queue_processes            1000 session_max_open_files         10 ========================= See Resource Limit ========================= SELECT current_utilization, limit_value    FROM V$RESOURCE_LIMIT   WHERE resource_name='sessions'; CURRENT_UTILIZATION LIMIT_VALUE ------------------- ----------- ...

SQL for Space usage for Tablespaces and Segments ================================ SQLs ================================ View per Tablespace.  Free space, Used space, Total space. SET LINESIZE 120 SET PAGESIZE 200 SELECT TABLESPACE_NAME,    (MAX(MAX_SPACE)-MAX(USED_SPACE)  )AS DBA_FREE_SPACE_MB,                                      MAX(USED_SPACE) AS USED_SPACE_MB,    MAX(MAX_SPACE) AS MAX_SPACE_MB,    ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,    CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE  FROM (  SELECT tablespace_name,      ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,     0 AS MAX_SPACE,     0 AS USED_SPACE  ...

SQL for Space usage for Tablespaces and Segments ================================ SQLs ================================ View per Tablespace.  Free space, Used space, Total space. SET LINESIZE 120 SET PAGESIZE 200 SELECT TABLESPACE_NAME,    (MAX(MAX_SPACE)-MAX(USED_SPACE)  )AS DBA_FREE_SPACE_MB,                                      MAX(USED_SPACE) AS USED_SPACE_MB,    MAX(MAX_SPACE) AS MAX_SPACE_MB,    ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,    CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE  FROM (  SELECT tablespace_name,      ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,     0 AS MAX_SPACE,     0 AS USED_SPACE  ...