Learning Oracle: August 2023

Thursday, August 31, 2023

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 ?

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 ?

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 ?

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

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
/

Monday, August 21, 2023

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 ?

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 ?

-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 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

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)

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

)

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 ?

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

Monday, August 7, 2023

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

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

Saturday, August 5, 2023

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?

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?

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?

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?

$ 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

$ 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/

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

Pages

Thursday, August 31, 2023

How to check the sql id history ?

Invalid Objects In Oracle Database ?

How to check database startup time ?

How to truncate audit tablespace ?

How To Shrink Tablespaces

Monday, August 21, 2023

Important Features Of Cassandra Database ?

What is Cassandra Database ?

What is NoSQL Database ?

Cassandra Database

Day 2 day queries

How to check whether port is open or not ?

Monday, August 7, 2023

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

How to install MySQL on Ubuntu

Saturday, August 5, 2023

Docker volume ?

Docker bind mount?

Docker image digests?

How to expose container's ports?

How to rename a container?

How to Export Docker Container?