How to check the sql id history ?

How to check the sql id history ?

col begin_time for a25

col end_time for a11

col inst for 99999

col snapid for 999999

set lines 200

set pages 20000

select snap_id snapid,

(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time

,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time

,INSTANCE_NUMBER inst , PLAN_HASH_VALUE,

EXECUTIONS_DELTA Executions,

ROWS_PROCESSED_DELTA rows1,

round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,

round( ELAPSED_TIME_DELTA /1000000,0) elapsed

from wrh$_sqlstat a where sql_id in('&SQL_ID')

order by snap_id, INSTANCE_NUMBER;

Invalid Objects In Oracle Database ?

Invalid Objects In Oracle Database ?

select created,owner,object_name,object_type from dba_objects where status='INVALID' order by 1;

select owner,count(*) from dba_objects where status='INVALID' group by owner ORDER BY 2 DESC;

select owner,count(*) from dba_objects where status='INVALID' group by owner;

--Inv. creation date

select created,owner,object_name,object_type,status from dba_objects where created >=sysdate -1 and owner not in ('SYS','PUBLIC') order by 1 desc;

How to check database startup time ?

How to check database startup time ?

set pages 9999 lines 300

col open_mode for a10

col host_name for a20

select name db_name,instance_name,host_name,database_role,open_mode,version db_version,logins,

to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') "db up time" from v$database,gv$instance;

How to truncate audit tablespace ?

How to truncate audit tablespace ?

select sum(bytes)/(1024*1024) "Table size(MB)" from dba_segments where segment_name='AUD$' and owner='SYS';

select count(*) from sys.aud$;

select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 "MB Size" from dba_segments where segment_name='AUD$';

TRUNCATE table sys.AUD$;

select count(*) from sys.aud$;

How To Shrink Tablespaces

How To Shrink Tablespaces

select file_name, ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 ) resize_GB, ceil( blocks*&&blksize/1024/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 ) reclaimable_GB from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+)
/

---With alter command individual tablespace.

select file_name,'alter database datafile'||' '''||file_name||''''||' resize '||ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 )||''||'G'||';', ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 ) resize_GB, ceil( blocks*&&blksize/1024/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 ) reclaimable_GB from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and a.tablespace_name='SYSTEM' and ceil( blocks*&&blksize/1024/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024/1024 )!=0 order by 5 desc /

---with alter command.

set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select case when autoextensible='YES' and maxbytes>=bytes then -- we generate resize statements only if autoextensible can grow back to current size '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ ' ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;' else -- generate only a comment when autoextensible is off '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999) ||'M after setting autoextensible maxsize higher than current size for file ' || file_name||' */' end SQL from hwmdf where bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed order by bytes-hwm_bytes desc
/

---with alter command in GB

set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5GB minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select
case when autoextensible='YES' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024/1024),999999)
||'GB from '||to_char(ceil(bytes/1024/1024/1024),999999)||'GB */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024/1024)||'GB;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024/1024),999999)
||'GB from '||to_char(ceil(bytes/1024/1024/1024),999999)
||'GB after setting autoextensible maxsize higher than current size for file '
|| file_name||' */'
end SQL from hwmdf where bytes-hwm_bytes>1024*1024*1024 -- resize only if at least 1GB can be reclaimed order by bytes-hwm_bytes desc
/

Important Features Of Cassandra Database ?

Important Features Of Cassandra Database ?

-Elastic scalability

Cassandra is highly scalable; it allows to add more hardware to accommodate more customers and more data as per requirement.

-Always on architecture

Cassandra has no single point of failure and it is continuously available for business critical applications that cannot afford a failure.

-Fast linear scale performance

Cassandra is linearly scalable, i.e., it increases your throughput as you increase the number of nodes in the cluster.

Therefore it maintains a quick response time.

-Flexible data storage

Cassandra accommodates all possible data formats including structured, semistructured, and unstructured.

It can dynamically accommodate changes to the data structures according to the need.

-Easy data distribution

Cassandra provides the flexibility to distribute data where you need by replicating data across multiple data centers.

-Transaction support

Cassandra supports ACID properties.

-Fast writes

Cassandra was designed to run on cheap commodity hardware.

It performs very fast writes and can store hundreds of terabytes of data, without sacrificing the read efficiency.

What is Cassandra Database ?

What is Cassandra Database ?

Cassandra is a premier project by Apache

- An open source product

- It is primarily a column-oriented database

- Can be used as a hybrid database too

- Distributed and decentralized database

- Distributed and decentralized storage system

- Used for managing very large volume of structured, unstructured data

- Data can spread across host of clustered nodes

- Cassandra high availability features ensures no single point of failure

- Is fault tolerant, that avoids loss of data changes

- Cassandra can be configure for high scalability

What is NoSQL Database ?

What is NoSQL Database ?

-NoSQL database is called as "non SQL" or "non relational" database.

-A NoSQL database provides a mechanism for storage and retrieval of data which is modeled that is stored in the tabular relations as in RDBMS.

-The need for NoSQL database gained momentum with Web2.0 companies like Facebook, Google and Amazon.com.

-NoSQL databases are widely used in BigData and Real-time web applications.

-NoSQL databases are also called as "Not only SQL" as they support SQL like query languages. As the variety and volume of data stored about users, objects and products increase extra ordinary, then frequency in which the data is accessed and the consideration of performance and faster processing requirement lead to the improvising the NoSQL database.

==>Benefits of NoSQL Database

-NoSQL databases are designed to store and manage a very large sets of distributed data.

-NoSQL databases solve the scalability and bigdata performance issues that were not addressed by the relational databases.

-NoSQL database is generally useful when an enterprise needs to access and analyze large amount of unstructured data or data that is stored remotely on multiple virtual

servers, may be in the cloud.

-NoSQL databases helps in overcoming the challenges to store and manage data other than the well defined structured and uniform data.

-NoSQL databases overcome the problem or need of creating the schema before the application is build, as NoSQL databases are schemaless databases.

-NoSQL databases suits the application that has to store and manage data of highly dynamic applications.

-NoSQL databases can be scaled dynamically as the data starts growing in leaps and bounds.

==>Key benefits

Flexible Data Handling

NoSQL database can handle structured, semi structured and unstructured data.

Dynamic Growth

NoSQL databases can to grow dynamically as the requirement changes and the data grows.

Horizontal Scaling

NoSQL databases are scaled horizontally by adding servers dynamically unlike the RDBMS those are scaled vertically.

Can adopt the technique of sharding and replication while scaling horizontally.

Very suitable for cloud computing services to manage and handle virtual servers that are expanded horizontally based on the demand.

Schemaless

NoSQL databases are schema less databases and are not defined for a strict data structure.

Improved Performance

The NoSQL databases provides data in an efficient, better and faster performance in comparison with RDBMS generally for semi structured and unstructured data.

==>Limitation of NoSQL Database

NoSQL databases are an entire set of databases but not a single database.

Some NoSQL databases do not support ACID transactions.

Few NoSQL databases lacks in reliability.

Based on the requirement of the application, need to choose a NoSQL database based on it's strength to support the requirement of the application.

Cassandra Database

Cassandra Database

- Cassandra is a Open Source, NoSQL database.
- Cassandra provides:

 Highly scalable
 High performance
 Distributed database

-Cassandra is designed to handle large amounts of data in a cluster of nodes. The nodes of the cluster are commodity servers.
- Cassandra provides high availability with no single point of failure.

Cassandra supports the CAP theorem.

According to CAP theorem, the system must provide guarantee for:

- Consistency
- Availability
- Partition Tolerance

Cassandra supports the following

Consistency
Updates to the state of the system are seen by all the clients simultaneously.

Availability
Guarantee of the system to be available for every valid request.

Partition tolerance
The system continues to operate despite arbitrary message loss or network partition.

Day 2 day queries

Day 2 day queries

To Find DB/Instance Name in RAC 

=========================

sql> show parameter cluster;

col host_name for a20

select inst_id,instance_name,host_name,instance_role from gv$instance;

select instance_name from v$instance;

To check from the operating system

 

# lsnodes

To get the NLS PARAMETER details 

================================

set linesize 150

set pagesize 1000

col name for a50

col parameter for a30

col value for a30

col member for a50

col DATAFILE_NAME for a70

col TEMPFILE_NAME for a70

col CONTROLFILE_NAME for a70

col LOGFILE_MEMBER for a70

select * from NLS_DATABASE_PARAMETERS;

To check the Instance Uptime 

================================

select instance_name||', up since '|| to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') start_time from gv$instance;

To Identify Space occupied and original space utilized of a database 

=====================================================================

set numf 999999.99

select sum(bytes)/(1024*1024*1024) "Total Allocated GB",Total_Consumed_GB from dba_data_files,

(select sum(bytes)/(1024*1024*1024) Total_Consumed_GB from dba_segments) group by Total_Consumed_GB;

To get the DATABASE SCHEMA SIZE from the database 

===================================================

set linesize 150

set pagesize 5000

col owner for a15

col segment_name for a30

col segment_type for a20

col TABLESPACE_NAME for a30

clear breaks

clear computes

compute sum of SIZE_IN_GB on report

break on report

select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;

To get the LOGFILES INFORMATION from the database 

==================================================

col member for a65

select lf.MEMBER,l.GROUP#,THREAD#,SEQUENCE#,MEMBERS,bytes/1024/1024 "BYTES IN MB",ARCHIVED,l.status from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#

To get the CONTROLFILES INFORMATION from the database 

=======================================================

col name for a90

select * from v$controlfile

To get the TEMPFILES SIZE from the database 

=============================================

select tablespace_name,file_name,bytes/1024/1024 "SIZE_IN_MB" from dba_temp_files order by file_name asc;

To get the TABLESPACE SIZE from the database 

==============================================

select tablespace_name,sum(bytes)/1024/1024 "SIZE_IN_MB" from dba_data_files group by tablespace_name;

To get the datafile sizes from the database

================================================

col file_name for a70

col tablespace_name for a30

clear breaks

clear computes

compute sum of SIZE_IN_MB on report

break on report

select tablespace_name,file_name,AUTOEXTENSIBLE,INCREMENT_BY,MAXBYTES/1024/1024 "MAX in MB",bytes/1024/1024 "SIZE_IN_MB" from dba_data_files order by tablespace_name;

To get information about the database 

======================================

SET LINESIZE 150

SET PAGESIZE 50000

cOL HOST_NAME FOR A25

col LOGINS FOR A20

col STATUS for A15

col "STARTUP_TIME" FOR A30

col INSTANCE_NAME for a20

col VERSION for a20

select INSTANCE_NAME,HOST_NAME,VERSION,LOGINS,STATUS,to_char(STARTUP_TIME,'DD-MON-YYYY DAY HH24:MI:SS')

"STARTUP_TIME" FROM v$instance;

col name for a30

col "CREATED" for a25

col LOG_MODE for a15

col OPEN_MODE for a15

col DATABASE_ROLE for a15

select NAME,to_char(CREATED,'DD-MON-YYYY HH24:MI:SS') "CREATED",LOG_MODE,OPEN_MODE,DATABASE_ROLE from v$database;

############33Procedure for generating the DDL statements of the database objects. 

=========================================================================

There are two parameters used in the specified object,

  

1. Object Type        ==> eg: FUNCTION,PROCEDURE,PACKAGE,TABLE,VIEW etc.,

2. Directory Name  ==> Specify the directory name where the output to be stored.

Usage -  DS_GET_DDL_STATEMENT(<Object type>,<Directory Name>)

Objects to be created:

----------------------

 CREATE TABLE "SMF_APEXRPS"."DS_ERROR_TABLE" 

   (    "COMNAME" VARCHAR2(200 BYTE), 

    "COMDDDL" LONG

   );

Create or Replace

PROCEDURE DS_GET_DDL_STATEMENT(P_OBJECTTYPE VARCHAR2,PDIR VARCHAR2)

AS

V_DDL LONG;

V_ERRMSG VARCHAR2(500);

vInHandle  utl_file.file_type;

CURSOR C1 IS 

SELECT * FROM USER_OBJECTS WHERE object_type=P_OBJECTTYPE;

BEGIN

FOR I IN C1 LOOP

select 

dbms_metadata.get_ddl(P_OBJECTTYPE,I.OBJECT_NAME)  INTO V_DDL

from dual;

  vInHandle := utl_file.fopen(PDIR, I.OBJECT_NAME||'.txt', 'W');

  IF utl_file.is_open(vInHandle) THEN

   utl_file.put_line(vInHandle, V_DDL, FALSE);

    utl_file.fflush(vInHandle);

    utl_file.fclose_all;

 END IF;

END LOOP;

EXCEPTION WHEN OTHERS THEN

V_ERRMSG:=SQLERRM;

INSERT INTO DS_ERROR_TABLE VALUES('ERROR',V_ERRMSG);

END;

-- +==================================================================+

-- PURPOSE: Provides a report on the top segments (in bytes) grouped by Segment Type

-- +==================================================================+

SET LINESIZE 155

SET PAGESIZE 9999

SET VERIFY   OFF

BREAK ON segment_type SKIP 1

COMPUTE SUM OF bytes ON segment_type

COLUMN segment_type        FORMAT A20                HEADING 'Segment Type'

COLUMN owner               FORMAT A15                HEADING 'Owner'

COLUMN segment_name        FORMAT A30                HEADING 'Segment Name'

COLUMN partition_name      FORMAT A30                HEADING 'Partition Name'

COLUMN tablespace_name     FORMAT A20                HEADING 'Tablespace Name'

COLUMN bytes               FORMAT 9,999,999,999,999  HEADING 'Size (in bytes)'

COLUMN extents             FORMAT 999,999,999        HEADING 'Extents'

SELECT

    a.segment_type      segment_type

  , a.owner             owner

  , a.segment_name      segment_name

  , a.partition_name    partition_name

  , a.tablespace_name   tablespace_name

  , a.bytes             bytes

  , a.extents           extents

FROM

    (select

         b.segment_type

       , b.owner

       , b.segment_name

       , b.partition_name

       , b.tablespace_name

       , b.bytes

       , b.extents

     from

         dba_segments b

     order by

         b.bytes desc

    ) a

WHERE

    rownum < 101

ORDER BY

    segment_type, bytes desc, owner, segment_name

/

To check for index fragmentation 

 -- +------------------------------------------------------------------------------+

-- | PURPOSE  : To check for index fragmentation. As a rule of thumb if 10-15%  |

-- |            of the table data changes, then you should consider rebuilding the index  |

-- +-------------------------------------------------------------------------------+

ANALYZE INDEX &&index_name VALIDATE STRUCTURE; 

COL name         HEADING 'Index Name'          FORMAT a30 

COL del_lf_rows  HEADING 'Deleted|Leaf Rows'   FORMAT 99999999 

COL lf_rows_used HEADING 'Used|Leaf Rows'      FORMAT 99999999 

COL ibadness     HEADING '% Deleted|Leaf Rows' FORMAT 999.99999 

SELECT

    name

  , del_lf_rows

  , lf_rows - del_lf_rows lf_rows_used

  , TO_CHAR( del_lf_rows /(DECODE(lf_rows,0,0.01,lf_rows))*100,'999.99999') ibadness 

FROM   index_stats

/

prompt 

prompt Consider rebuilding any index if % of Deleted Leaf Rows is > 20%

prompt 

undefine index_name 

Report free space fragmentation 

-- +----------------------------------------------------------------------------+

-- | PURPOSE  : Report free space fragmentation.                                             |

-- |            THIS SCRIPT MUST BE RUN AS THE SYS USER!!!                  |

-- +----------------------------------------------------------------------------+

connect / as sysdba

CREATE OR REPLACE VIEW free_space (

    tablespace

  , pieces

  , free_bytes

  , free_blocks

  , largest_bytes

  , largest_blks

  , fsfi

  , data_file

  , file_id

  , total_blocks

)

AS

SELECT

    a.tablespace_name

  , COUNT(*)

  , SUM(a.bytes)

  , SUM(a.blocks)

  , MAX(a.bytes)

  , MAX(a.blocks)

  , SQRT(MAX(a.blocks)/SUM(a.blocks))*(100/SQRT(SQRT(count(a.blocks))))

  , UPPER(b.file_name)

  , MAX(a.file_id)

  , MAX(b.blocks)

FROM

    sys.dba_free_space  a

  , sys.dba_data_files  b

WHERE

    a.file_id = b.file_id

GROUP BY

    a.tablespace_name,  b.file_name

/

CLEAR COLUMNS

SET LINESIZE  120

SET PAGESIZE  9999

SET FEEDBACK  off

SET VERIFY    off

BREAK ON tablespace SKIP 2 ON REPORT

COMPUTE SUM OF  total_blocks  ON tablespace

COMPUTE SUM OF  free_blocks   ON tablespace

COMPUTE SUM OF  free_blocks   ON report

COMPUTE SUM OF  total_blocks  ON report

COLUMN tablespace     HEADING "Tablespace"    FORMAT a15

COLUMN file_id        HEADING File#           FORMAT 99999

COLUMN pieces         HEADING Frag            FORMAT 9999

COLUMN free_bytes     HEADING 'Free Byte'

COLUMN free_blocks    HEADING 'Free Blk'      FORMAT 999,999,999

COLUMN largest_bytes  HEADING 'Biggest Bytes'

COLUMN largest_blks   HEADING 'Biggest Blks'  FORMAT 999,999,999

COLUMN data_file      HEADING 'File Name'     FORMAT a45

COLUMN total_blocks   HEADING 'Total Blocks'  FORMAT 999,999,999

SELECT

    tablespace

  , data_file

  , pieces

  , free_blocks

  , largest_blks

  , file_id

  , total_blocks

FROM

    free_space

/

DROP VIEW free_space

/

List all currently connected user sessions ordered by current PGA size 

-- +-------------------------------------------------------------------------------------+

-- | PURPOSE  : List all currently connected user sessions ordered by current PGA size  |

-- +-------------------------------------------------------------------------------------+

SET LINESIZE 145

SET PAGESIZE 9999

COLUMN sid                     FORMAT 99999          HEADING 'SID'

COLUMN serial_id               FORMAT 999999         HEADING 'Serial#'

COLUMN session_status          FORMAT a9             HEADING 'Status'          JUSTIFY right

COLUMN oracle_username         FORMAT a12            HEADING 'Oracle User'     JUSTIFY right

COLUMN os_username             FORMAT a9             HEADING 'O/S User'        JUSTIFY right

COLUMN os_pid                  FORMAT 9999999        HEADING 'O/S PID'         JUSTIFY right

COLUMN session_program         FORMAT a18            HEADING 'Session Program' TRUNC

COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC

COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'

COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'

COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'

COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'

prompt 

prompt +----------------------------------------------------+

prompt | User Sessions Ordered by Current PGA Size          |

prompt +----------------------------------------------------+

SELECT

    s.sid                sid

  , s.serial#            serial_id

  , lpad(s.status,9)     session_status

  , lpad(s.username,12)  oracle_username

  , lpad(s.osuser,9)     os_username

  , lpad(p.spid,7)       os_pid

  , s.program            session_program

  , lpad(s.machine,8)    session_machine

  , sstat1.value         session_pga_memory

  , sstat2.value         session_pga_memory_max

  , sstat3.value         session_uga_memory

  , sstat4.value         session_uga_memory_max

FROM 

    v$process  p

  , v$session  s

  , v$sesstat  sstat1

  , v$sesstat  sstat2

  , v$sesstat  sstat3

  , v$sesstat  sstat4

  , v$statname statname1

  , v$statname statname2

  , v$statname statname3

  , v$statname statname4

WHERE

      p.addr (+)            = s.paddr

  AND s.sid                 = sstat1.sid

  AND s.sid                 = sstat2.sid

  AND s.sid                 = sstat3.sid

  AND s.sid                 = sstat4.sid

  AND statname1.statistic#  = sstat1.statistic#

  AND statname2.statistic#  = sstat2.statistic#

  AND statname3.statistic#  = sstat3.statistic#

  AND statname4.statistic#  = sstat4.statistic#

  AND statname1.name        = 'session pga memory'

  AND statname2.name        = 'session pga memory max'

  AND statname3.name        = 'session uga memory'

  AND statname4.name        = 'session uga memory max'

ORDER BY session_pga_memory DESC

/

List all currently connected user sessions ordered by Logical I/O 

-- +--------------------------------------------------------------------------------+

-- | PURPOSE  : List all currently connected user sessions ordered by Logical - I/O |

-- +--------------------------------------------------------------------------------+

SET LINESIZE 145

SET PAGESIZE 9999

COLUMN sid               FORMAT 99999            HEADING 'SID'

COLUMN serial_id         FORMAT 999999           HEADING 'Serial#'

COLUMN session_status    FORMAT a9               HEADING 'Status'          JUSTIFY right

COLUMN oracle_username   FORMAT a12              HEADING 'Oracle User'     JUSTIFY right

COLUMN os_username       FORMAT a9               HEADING 'O/S User'        JUSTIFY right

COLUMN os_pid            FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right

COLUMN session_program   FORMAT a18              HEADING 'Session Program' TRUNC

COLUMN session_machine   FORMAT a8               HEADING 'Machine'         JUSTIFY right TRUNC

COLUMN logical_io        FORMAT 999,999,999,999  HEADING 'Logical I/O'

COLUMN physical_reads    FORMAT 999,999,999,999  HEADING 'Physical Reads'

COLUMN physical_writes   FORMAT 999,999,999,999  HEADING 'Physical Writes'

prompt 

prompt +----------------------------------------------------+

prompt | User Sessions Ordered by Logical I/O               |

prompt +----------------------------------------------------+

SELECT

    s.sid                sid

  , s.serial#            serial_id

  , lpad(s.status,9)     session_status

  , lpad(s.username,12)  oracle_username

  , lpad(s.osuser,9)     os_username

  , lpad(p.spid,7)       os_pid

  , s.program            session_program

  , lpad(s.machine,8)    session_machine

  , sstat1.value

    + sstat2.value         logical_io

  , sstat3.value         physical_reads

  , sstat4.value         physical_writes

FROM 

    v$process  p

  , v$session  s

  , v$sesstat  sstat1

  , v$sesstat  sstat2

  , v$sesstat  sstat3

  , v$sesstat  sstat4

  , v$statname statname1

  , v$statname statname2

  , v$statname statname3

  , v$statname statname4

WHERE

      p.addr (+)            = s.paddr

  AND s.sid                 = sstat1.sid

  AND s.sid                 = sstat2.sid

  AND s.sid                 = sstat3.sid

  AND s.sid                 = sstat4.sid

  AND statname1.statistic#  = sstat1.statistic#

  AND statname2.statistic#  = sstat2.statistic#

  AND statname3.statistic#  = sstat3.statistic#

  AND statname4.statistic#  = sstat4.statistic#

  AND statname1.name        = 'db block gets'

  AND statname2.name        = 'consistent gets'

  AND statname3.name        = 'physical reads'

  AND statname4.name        = 'physical writes'

ORDER BY logical_io DESC

/

List all currently connected user sessions ordered by CPU time. 

-- +----------------------------------------------------------------------------+

-- | DATABASE : Oracle |

-- | PURPOSE : List all currently connected user sessions ordered by CPU time. |

-- +----------------------------------------------------------------------------+

SET LINESIZE 145

SET PAGESIZE 9999

COLUMN sid FORMAT 99999 HEADING 'SID'

COLUMN serial_id FORMAT 999999 HEADING 'Serial#'

COLUMN session_status FORMAT a9 HEADING 'Status' JUSTIFY right

COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right

COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right

COLUMN os_pid FORMAT 9999999 HEADING 'O/S PID' JUSTIFY right

COLUMN session_program FORMAT a20 HEADING 'Session Program' TRUNC

COLUMN session_machine FORMAT a14 HEADING 'Machine' JUSTIFY right TRUNC

COLUMN cpu_value FORMAT 999,999,999,999 HEADING 'CPU'

prompt

prompt +----------------------------------------------------+

prompt | User Sessions Ordered by CPU |

prompt +----------------------------------------------------+

SELECT

s.sid sid

, s.serial# serial_id

, lpad(s.status,9) session_status

, lpad(s.username,12) oracle_username

, lpad(s.osuser,9) os_username

, lpad(p.spid,7) os_pid

, s.program session_program

, lpad(s.machine,14) session_machine

, sstat.value cpu_value

FROM

v$process p

, v$session s

, v$sesstat sstat

, v$statname statname

WHERE

p.addr (+) = s.paddr

AND s.sid = sstat.sid

AND statname.statistic# = sstat.statistic#

AND statname.name = 'CPU used by this session'

ORDER BY cpu_value DESC

/

How to check whether port is open or not ?

How to check whether port is open or not ?

(echo > /dev/tcp/192.168.1.75/1521) >/dev/null 2>&1 && echo "Port is open" || echo "Port is Closed"

How To Install "gnome-system-tools" Package on Ubuntu / Default packages in Ubuntu

How To Install "gnome-system-tools" Package on Ubuntu / Default packages in Ubuntu

Cross-platform configuration utilities for GNOME

The GNOME System Tools are a fully integrated set of tools aimed to make easy the job that means the computer administration on an UNIX or Linux system. They're thought to help from the new Linux or UNIX user to the system administrators. Cross-platform configuration utilities for GNOME

The GNOME System Tools are a fully integrated set of tools aimed to make easy the job that means the computer administration on an UNIX or Linux system. They're thought to help from the new Linux or UNIX user to the system administrators.

Step 1

Run update command to update package repositories and get latest package information.
sudo apt-get update -y (or) apt-get update -y

Step 2

Run the install command with -y flag to quickly install the packages and dependencies.
sudo apt-get install -y gnome-system-tools (or) apt-get install -y gnome-system-tools

Optional:

sudo apt-get update
sudo apt-get upgrade -y
sudo apt-get update
sudo apt-get install build-essential
sudo apt-get update && sudo apt-get install build-essential

How to install MySQL on Ubuntu

How to install MySQL on Ubuntu

Step 1 — Installing MySQL

To install it, update the package index on your server if you’ve not done so recently:

sudo apt update or apt update


Then install the mysql-server package:
sudo apt install mysql-server


Ensure that the server is running using the systemctl start command:
sudo systemctl start mysql.service


Step 2 — Configuring MySQL

sudo mysql

Then run the following ALTER USER command to change the root user’s authentication method to one that uses a password. The following example changes the authentication method to mysql_native_password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
exit

Following that, you can run the mysql_secure_installation script without issue.
sudo mysql_secure_installation

This will take you through a series of prompts where you can make some changes to your MySQL installation’s security options. The first prompt will ask whether you’d like to set up the Validate Password Plugin, which can be used to test the password strength of new MySQL users before deeming them valid.

If you elect to set up the Validate Password Plugin, any MySQL user you create that authenticates with a password will be required to have a password that satisfies the policy you select:

Output:

Securing the MySQL server deployment.
Enter password for user root:

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Using existing password for root.
Estimated strength of the password: 25
Change the password for root ? ((Press y|Y for Yes, any other key for No) : Y
New password:
Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.
All done!

$ mysql -u root -p

Then go back to using the default authentication method using this command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

This will mean that you can once again connect to MySQL as your root user using the sudo mysql command.


Step 3 — Creating a Dedicated MySQL User and Granting Privileges

sudo mysql

Note: If you installed MySQL with another tutorial and enabled password authentication for root, you will need to use a different command to access the MySQL shell. The following will run your MySQL client with regular user privileges, and you will only gain administrator privileges within the database by authenticating:

mysql -u root -p

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

Run the following command to create a user that authenticates with caching_sha2_password. Be sure to change sammy to your preferred username and password to a strong password of your choosing:

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

Note: There is a known issue with some versions of PHP that causes problems with caching_sha2_password. If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure, mysql_native_password plugin instead:

CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

If you aren’t sure, you can always create a user that authenticates with caching_sha2_plugin and then ALTER it later on with this command:

ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

After creating your new user, you can grant them the appropriate privileges. The general syntax for granting user privileges is as follows:

GRANT PRIVILEGE ON database.table TO 'username'@'host';

Run this GRANT statement, replacing sammy with your own MySQL user’s name, to grant these privileges to your user:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD on *.*

Note that this statement also includes WITH GRANT OPTION. This will allow your MySQL user to grant any permissions that it has to other users on the system

Warning: Some users may want to grant their MySQL user the ALL PRIVILEGES privilege, which will provide them with broad superuser privileges akin to the root user’s privileges, like so:

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Such broad privileges should not be granted lightly, as anyone with access to this MySQL user will have complete control over every database on the server.

Following this, it’s good practice to run the FLUSH PRIVILEGES command. This will free up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:

FLUSH PRIVILEGES;
Then you can exit the MySQL client:

exit

In the future, to log in as your new MySQL user, you’d use a command like the following:

mysql -u sammy -p

The -p flag will cause the MySQL client to prompt you for your MySQL user’s password in order to authenticate.

Finally, let’s test the MySQL installation.

Step 4 — Testing MySQL

Regardless of how you installed it, MySQL should have started running automatically. To test this, check its status.

systemctl status mysql.service

The output will be similar to the following:

Output

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2022-04-11 16:04:39 UTC; 2h 36min ago
Process: 2593 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 2601 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 1119)
Memory: 354.3M
CPU: 19.944s
CGroup: /system.slice/mysql.service
└─2601 /usr/sbin/mysqld


If MySQL isn’t running, you can start it with sudo systemctl start mysql.

Source/Reference:

https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-22-04

Docker volume ?

Docker volume ?

Volumes are stored in a part of the host file system which is managed by docker (/var/lib/docker/volumes/ on Linux). Non-Docker processes should not modify this part of the file system. Volumes are the best way to persist data in docker.

Sharing with one or more containers are allowed.

Note: To attach multiple volumes to container use -v/--mount flag more than once.


Commands:

docker run -d \
--name devtest1 \
--mount source=myvol1,target=/qpath \
nginx:latest

$ docker run -d --name devtest1 --mount source=myvol1,target=/qpath nginx:latest
$ docker volume inspect myvol1

docker run -d \
--name devtest2 \
-v myvol2:/app \
nginx:latest

$ docker run -d --name devtest2 -v myvol2:/app nginx:latest
$ docker volume inspect myvol2

Docker bind mount?

Docker bind mount?

When you use a bind mount, a file or directory on the host machine is mounted into a container.

Bind mounts may be stored anywhere on the host system. They may even be important system files or directories. Non-Docker processes on the Docker host or a Docker container can modify them at any time.

Sharing with one or more containers are allowed in bind mount.

Note: To attach multiple (bind mount) to container use -v/--mount flag more than once

Commands:

[root@localhost ~]# mkdir target
[root@localhost ~]# cd target/
[root@localhost target]# touch qader1

docker run -d \
-it \
--name devtest \
--mount type=bind,source="$(pwd)"/target,target=/app \
nginx:latest

[root@localhost ~]# docker run -d -it --name devtest --mount type=bind,source="$(pwd)"/target,target=/app nginx:latest

[root@localhost ~]# docker exec -it devtest bash
root@7e7fc162399c:/# ls
app bin boot dev docker-entrypoint.d docker-entrypoint.sh etc home lib lib32 lib64 libx32 media mnt opt proc root run sbin srv sys tmp usr var
root@7e7fc162399c:/# cd app/
root@7e7fc162399c:/app# ls
qader1
root@7e7fc162399c:/# cd app/
root@7e7fc162399c:/app# touch qader3
root@7e7fc162399c:/app# exit
exit
[root@localhost ~]# ls -ltrh target
total 0
-rw-r--r--. 1 root root 0 Aug 5 15:37 qader1
-rw-r--r--. 1 root root 0 Aug 5 15:41 qader2
-rw-r--r--. 1 root root 0 Aug 5 15:42 qader3


docker run -d \
-it \
--name abc \
-v "$(pwd)"/target:/app \
nginx:latest

[root@localhost ~]# docker run -d -it --name abc -v "$(pwd)"/target:/app nginx:latest
7e9d2a26b3b14a106f1855e0795808539ef28fa5c34d206ce1e1b64916c073d6

[root@localhost ~]# docker exec -it abc bash
root@7e9d2a26b3b1:/# cd app/
root@7e9d2a26b3b1:/app# ls
qader1
root@7e9d2a26b3b1:/app# touch qader2
rot@7e9d2a26b3b1:/app# exit
exit
[root@localhost ~]# ls -ltrh target
total 0
-rw-r--r--. 1 root root 0 Aug 5 15:37 qader1
-rw-r--r--. 1 root root 0 Aug 5 15:41 qader2

Docker image digests?

Docker image digests?

When you download something from the internet, a common method for determining both integrity and authenticity of an object is to generate a cryptographic hash of it and compare it to what you expect.

Let’s say you’re using Docker Hub to store images, and you’re also deploying Docker images to your infrastructure by specifying a tag like oneminutenotes/app:v1.2.11.

Now, one day, you discover that someone’s Docker Hub credentials on your team have been exposed. Docker Hub doesn’t support MFA, so you know an attacker could have had push access to your repositories.

How can you be sure that the image you’re running, hasn’t been overwritten with a malicious version? Short answer is you can’t, because you’re not verifying what you’re downloading from the internet.

The answer to this in the Docker world is digests

Images that use the v2 or later format have a content-addressable identifier called a digest. As long as the input used to generate the image is unchanged, the digest value is predictable.

Instead of specifying oneminutenotes/app:v1.2.11, we should have been specifying the content addressable identifier for that tag; oneminutenotes/app@sha256:e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855

When we use the digest as the identifier, Docker will not only pull the image with that digest, but also calculate the sha256 digest of what we downloaded and verify it against what we specified.

This provides a number of protections:

1. It removes any attack vector through the Docker Registry to change what we’re running in production. An attack that overwrites a mutable tag, has no effect on what we’re running.

2. It prevents any possibility of a MiTM attack, since any alteration (either malicious, or accidental) will be checked.

3. It increases the overall stability of the system as a whole, by the simple fact that the digest is an immutable identifier, so we know it can never change.

4. It improves cacheability for docker pull’s; content-addressable identifiers can never change, so they can be cached efficiently.


How to see container digest ?
docker images --digests

docker pull ubuntu:latest ----------bydefault

docker pull ubuntu@sha256:5d1d5407f353843ecf8b16524bc5565aa332e9e6a1297c73a92d3e754b8a636d
docker pull nginx@sha256:67f9a4f10d147a6e04629340e6493c9703300ca23a2f7f3aa56fe615d75d31ca

How to expose container's ports?

How to expose container's ports?

Options Used for port expose: 

--publish, -p Publish a container’s port(s) to the host
--publish-all, -P Publish all exposed ports to random ports

Commands:

[root@localhost ~]# docker run --name 'nginx2' -P -itd nginx
[root@localhost ~]# docker run --name 'nginx3' -p 1521:80 -itd nginx

How to rename a container?

How to rename a container?

$ docker rename CONTAINER NEW_NAME

The docker rename command renames a container.

[root@localhost ~]# docker ps -a

CONTAINER ID   IMAGE                                     COMMAND                  CREATED          STATUS                      PORTS     NAMES

90a3993d725a   ubuntu                                    "/bin/bash"              4 hours ago      Up 4 hours                            c1

[root@localhost ~]# docker container rename 90a 'TESTNAME'

[root@localhost ~]# docker ps -a

CONTAINER ID   IMAGE                                     COMMAND                  CREATED          STATUS                      PORTS     NAMES

90a3993d725a   ubuntu                                    "/bin/bash"              4 hours ago      Up 4 hours                            TESTNAME

How to export docker container?

How to Export Docker Container?

The docker export command does not export the contents of volumes associated with the container. If a volume is mounted on top of an existing directory in the container, docker export will export the contents of the underlying directory, not the contents of the volumes.

--output, -o   Write to a file, instead of STDOUT

$ docker export red_panda (greaterthan angle bracket symbol) latest.tar 

or

$ docker export --output="latest.tar" red_panda

Export Commands:

docker export c1 > c1backup.tar

docker export -o='c2backup.tar' c1

[root@localhost ~]# docker images |grep c1backup.tar

[root@localhost ~]# ls

anaconda-ks.cfg  c1backup.tar  Dockerfile  hello-world

Import Commands:

[root@localhost ~]# docker import c1backup.tar backup:v1

sha256:ac48e627777e035b65cadb144e05bea4dbcdbbd9289310c73bd12d5552a75cfb

[root@localhost ~]# docker run -it -d --name mybackup backup:v1 bash

19e8b31ca95f03dec29a7b4b0e3e66510ab3a931e5b807eac0b4b79b5ef9c05e

To extract tar file & check its content

$ mkdir Ubuntu && tar -xf mybackup.tar -C Ubuntu

Extract and see the tar file content.

mkdir Ubuntu && tar -xf c1backup.tar -C Ubuntu

cd Ubuntu/

ls

bin  boot  dev  etc  home  lib  lib32  lib64  libx32  media  mnt  opt  proc  qader  root  run  sbin  srv  sys  tmp  u01  usr  var