How to Find Indexes Status in Oracle

> How to find index status of a Particular Table.

SQL> SELECT OWNER,TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='OTORDERS';

               OWNER               TABLE_NAME         INDEX_NAME              STATUS
              ------------------------------ ----------- -------------- --------------------------------
                 HMIS                  OTORDERS           OREPRC_IDX                VALID
                 HMIS                  OTORDERS           ORMEDN_IND               VALID
                 HMIS                  OTORDERS            PK_ORDERS                 VALID


> How to find the index status of Particular Schema.


SQL> SELECT OWNER,TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES WHERE OWNER='CMIS';

OWNER           TABLE_NAME                     INDEX_NAME                    STATUS                      
---------- ------------ ------------------------- --------------------------------------------------              
CMIS            PMDRGMST                      PK_PMDRGMST_1                VALID                      
CMIS           VISOTUBE                         SYS_C0023980                     VALID                      
CMIS           CANISTER                         SYS_C0023939                     VALID                      
CMIS          WEBCAT                              WEBCAT0                             VALID                      
CMIS          VITALVAL                              VITALVAL0                            VALID                      
CMIS          VITALNRM                            VITALNRM0                          VALID      

Happy Learning...

How to Check the Index Size in Oracle

> How to check a particular index size.


SQL> select segment_name,sum(bytes)/1024/1024 as "SIZE in MB" from user_segments where      segment_name='X_TRANSACTION' group by segment_name


SEGMENT_NAME   SIZE in MB
-------------- -----------------------------
X_TRANSACTION         152



> How to check all the indexes of a Particular User.


 SQL> select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='KMIS' and segment_type='INDEX' group by owner;

OWNER                        SIZE in GB
------------------------------------------------
KMIS                            33.751709


> Check all the indexes of a particular table.


 SQL> SELECT DISTINCT TABLE_NAME,INDEX_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='PHTRNHST';

TABLE_NAME           INDEX_NAME
-------------------- --------------------
PHTRNHST             X_RET_1
PHTRNHST             IND_MRM_D_S
PHTRNHST             X_TRANSACTION
PHTRNHST             X_RETURN
PHTRNHST             X_1


Happy Learning...

How to Check the table Size in Oracle

•  To check table name segment type and table size in MB 

SQL> select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where                                             segment_type='TABLE' and segment_name='OTORDERS'

             SEGMENT_NAME         SEGMENT_TYPE                 MB
           -------------------- ------------------ ---------------------------------------
             OTORDERS                          TABLE                           192

• To check Table Owner,Table Name and Table Size.
• You can check here one HMIS owner have multiple tables with different sizes and names.

SQL> select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments                            where owner='HMIS' and segment_type='TABLE' group by owner,segment_name order by                               "SIZE in GB" desc;

OWNER                          SEGMENT_NAME         SIZE in GB              
------------------------------ ---------------------------------------------------------------            
HMIS                                 SHSHTDTA                  1.50683594              
HMIS                                 AHTRNHST                   .984375              
HMIS                                OTOCURNC                   .921875      



Happy Learning ...

How to Extract DDL of any Table in Oracle

SQL> set long 1000
SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL('TABLE','EMP') from DUAL;

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

Happy Learning...

SGA_MAX_SIZE and Memory_Target Parameter in Oracle 10g & 11g

Hi guys, i will explain you the Difference between 10g SGA_MAX_SIZE, SGA_TARGET & 11g feature MEMORY_TARGET, MEMORY_MAX_TARGET


10g Feature :-


SGA_MAX_SIZE : Not Dynamic

SGA_TARGET     : Dynamic


SGA_MAX_SIZE & SGA_TARGET = Oracle introduces this feature in 10g with Automatic Shared Memory Management (ASMM) where oracle manages only sga and living PGA to parameter pga_aggregate_target.


We can say like this SGA_MAX_SIZE parameter value is the maximum size you can increase for your SGA.


Ex:- If SGA_MAX_SIZE=10gb and SGA_TARGET=6GB in future if you want to increase the size of SGA you can increase only upto 10gb.


> SGA_TARGET : When you set SGA_TARGET to any value the Automatic Shared Memory Management (ASMM) is enabled The main advantage of ASMM is no need to worry for a DBA to size the SGA components.

> But still PGA is not managed here in 10g.


Note:-

  If you are using a server parameter file (SPFILE), the database remembers the sizes of the                    automatically tuned SGA components across instance shutdowns.

  As a result, the database instance does not need to learn the characteristics of the workload again     each  time the instance is started.

  The instance can begin with information from the previous instance and continue evaluating                   workload where it left off at the last shutdown.


                                                   ============ xxxxx ===========

11g New Feature :-


   MEMORY_TARGET             : Dynamic

   MEMORY_MAX_TARGET  : Not Dynamic



> Prior to 11g, Oracle manages PGA seperately by the pga_aggregate_target parameter.

> Oracle introduces AMM feature from 11g which will manage SGA as well as PGA.

> When we set MEMORY_TARGET, Oracle will dynamically assign memory to SGA & PGA            

   Note :-  MEMORY_TARGET= SGA_TARGET + PGA_AGGREGATE_TARGET.



> You can see in this demo my requirement is to enable 11g feature Memory_Target and give 10gb size to SGA.

> I have created database with DBCA and selected SGA size by enabling the feature."Use Automatic Memory Management"

> Oracle created this database with 38gb SGA size, this size oracle calculated 40% from existing RAM which is available in my server.



SQL> select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;

TOTAL SGA (GB)
--------------
    38.3281136

> You can see here memory_target and sga_max_size has enabled.


SQL> show parameter memory

NAME                                                   TYPE                      VALUE
------------------------------------ ----------- -----------------------------------
hi_shared_memory_address                integer                        0
memory_max_target                         big integer                 39424M
memory_target                                  big integer                 39424M
shared_memory_address                     integer                         0


SQL> show parameter sga

NAME                                 TYPE                        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                              boolean                    FALSE
pre_page_sga                     boolean                    FALSE
sga_max_size                     big integer               39424M
sga_target                           big integer                    0



> Now i need to allocate some restricted size for the SGA as per my requirement.

> Like we decided to allocate 10gb to SGA and Max_Target should be 2 gb extra.

> So we need to set these 11g parameters SGA_MAX_SIZE,MEMORY_MAX_SIZE AND MEMORY_TARGET.


Steps:-


SQL> alter system set sga_max_size=10g scope=spfile;

System altered.

SQL> alter system set memory_max_target=12g scope=spfile;

System altered.

SQL> alter system set memory_target=12g scope=spfile;

System altered.


>> To effect this SGA  size you need to bounce the database.


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area     1.0689E+10 bytes
Fixed Size                             2264616 bytes
Variable Size                         4328522200 bytes
Database Buffers                  6341787648 bytes
Redo Buffers                        16900096 bytes
Database mounted.
Database opened.

SQL> show parameter sga

NAME                                         TYPE                    VALUE
------------------------------------ ----------- ------------------------------
lock_sga                                  boolean                  FALSE
pre_page_sga                         boolean                  FALSE
sga_max_size                         big integer               10G
sga_target                               big integer                 0


SQL> show parameter memory

NAME                                                     TYPE                 VALUE
------------------------------------ ----------- ---------------------------------
hi_shared_memory_address                integer                  0
memory_max_target                        big integer               12G
memory_target                                 big integer               12G
shared_memory_address                     integer                 0



>> You can observe here the SGA_max_size is 10gb and Memory_max_target and memory_target is 12g.

>> We can exceed the SGA size upto 12gb.




Happy Learning...

Test and Production Environments in IT

Hi Guy's i will explain you the Environments of the IT companies ....


These sequence will differ in every company but mostly we have Test & Production environment.


DEV :-   A Development environment where you configure, customize, and use source control to                                     build an image of the application to be promoted to another environment.


SIT   :-   System Integration Testing, Its an testing environment where after testing finishes the                                         application and its respective data  are moved to UAT environment.


UAT / TEST  :-  User Acceptance Testing, will be done after finishing testing on SIT environment, Finally                                     after every testing is passed  the app and its respective data will be moved to PROD                                           environment or some companies will keep STAGE environment prior to deploying on                                           production.


STAGE :-   Used for final testing immediately prior to deploying to production. You can also say                                             Mirror of Production.


PROD  :-   This is Production or Live environment after all the testing done it moves in this environment where                    users directly interact with production.




Happy Learning ...