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.