DBA – KTEXPERTS

RULES FOR DATA MODELLING IN CASSANDRA:

RULES FOR DATA MODELLING IN CASSANDRA: Cassandra is a query language which is resembles with SQL, but the modulation of data is completely different when compared with other data types. In Cassandra, a worst model of data can lower the performance, especially when the data… Read More

KILLING SESSIONS

1 year ago by Prudhvi TejaPostgreSQL

KILLING SESSIONS: In PostgreSQL, you can terminate or “kill” a session using the pg_terminate_backend function. This function allows you to forcefully terminate a specific session, which can be helpful in scenarios where a session is unresponsive or causing issues. We can kill PostgreSQL session in… Read More

Table/index movement using pl/sql procedure

2 years ago by Vinod SiramOracle DB Admin

Table/index movement using pl/sql procedure

CREATE TABLESPACE AWS_DATA01 DATAFILE '+DATA' size 10G
##################### PRE-VALIDATIONS ##################

1 2	CREATE TABLESPACE AWS_DATA01 DATAFILE '+DATA' size 10G ##################### PRE-VALIDATIONS ##################

1) Gather all below data into a SQL log file for future validations

spool TABLE_INDEX_REORG_REBUILD_PREVALIDATIONS.log
set echo on
set linesize 300
col index_name for a30
col degree for a10
col status for a10
col table_name for a30
col owner for a30
col object_type for a20
col constraint_type for a20
col constraint_name for a30
col validated for a20
col degree for a10
set pagesize 100

select owner,object_type,status,count(*) from dba_objects where status <> 'VALID' and owner='SCOTT' group by owner,object_type,status order by owner;
select owner,index_name,status from dba_indexes where TABLE_OWNER='SCOTT' and status<>'VALID';
select index_name,status from dba_ind_partitions where status <> 'USABLE';
select CONSTRAINT_TYPE,STATUS,count(1) from dba_constraints where owner='SCOTT' group by CONSTRAINT_TYPE,STATUS;

set linesize 300
col index_name for a30
col degree for a10
col status for a10

select TABLE_NAME,INDEX_NAME,DEGREE,status from dba_indexes where OWNER='SCOTT' and degree not in (0,1);
select object_type , count(*),status from dba_objects where owner=upper('SCOTT') group by object_type,status order by object_type;
select table_name,index_name,logging from dba_indexes where OWNER='SCOTT' and logging = 'NO';
select distinct STATUS from v$datafile;
select constraint_name,constraint_type,table_name,status,validated from dba_constraints where owner='SCOTT' and status <> 'ENABLED';
select distinct owner from dba_segments where tablespace_name='&tablespace_name';
select distinct owner from dba_tables where tablespace_name='&tablespace_name';
select distinct owner from dba_indexes where tablespace_name='&tablespace_name';
select distinct table_owner from dba_tab_partitions where tablespace_name='&tablespace_name';
select distinct table_owner from dba_tab_subpartitions where tablespace_name='&tablespace_name';
select distinct index_owner from dba_ind_partitions where tablespace_name='&tablespace_name';
select distinct index_owner from dba_ind_subpartitions where tablespace_name='&tablespace_name';
select distinct STATUS from v$datafile;
select * from v$recover_file;
select distinct segment_type from dba_segments where tablespace_name='&tablespace_name';
select distinct status from dba_indexes;
select distinct status from dba_ind_partitions;
select distinct status from dba_ind_subpartitions;

spool off

spool TABLE_INDEX_REORG_REBUILD_PREVALIDATIONS.log

set echo on

set linesize 300

col index_name for a30

col degree for a10

col status for a10

col table_name for a30

col owner for a30

col object_type for a20

col constraint_type for a20

col constraint_name for a30

col validated for a20

col degree for a10

set pagesize 100

select owner,object_type,status,count(*) from dba_objects where status <> 'VALID' and owner='SCOTT' group by owner,object_type,status order by owner;

select owner,index_name,status from dba_indexes where TABLE_OWNER='SCOTT' and status<>'VALID';

select index_name,status from dba_ind_partitions where status <> 'USABLE';

select CONSTRAINT_TYPE,STATUS,count(1) from dba_constraints where owner='SCOTT' group by CONSTRAINT_TYPE,STATUS;

set linesize 300

col index_name for a30

col degree for a10

col status for a10

select TABLE_NAME,INDEX_NAME,DEGREE,status from dba_indexes where OWNER='SCOTT' and degree not in (0,1);

select object_type , count(*),status from dba_objects where owner=upper('SCOTT') group by object_type,status order by object_type;

select table_name,index_name,logging from dba_indexes where OWNER='SCOTT' and logging = 'NO';

select distinct STATUS from v$datafile;

select constraint_name,constraint_type,table_name,status,validated from dba_constraints where owner='SCOTT' and status <> 'ENABLED';

select distinct owner from dba_segments where tablespace_name='&tablespace_name';

select distinct owner from dba_tables where tablespace_name='&tablespace_name';

select distinct owner from dba_indexes where tablespace_name='&tablespace_name';

select distinct table_owner from dba_tab_partitions where tablespace_name='&tablespace_name';

select distinct table_owner from dba_tab_subpartitions where tablespace_name='&tablespace_name';

select distinct index_owner from dba_ind_partitions where tablespace_name='&tablespace_name';

select distinct index_owner from dba_ind_subpartitions where tablespace_name='&tablespace_name';

select distinct STATUS from v$datafile;

select * from v$recover_file;

select distinct segment_type from dba_segments where tablespace_name='&tablespace_name';

select distinct status from dba_indexes;

select distinct status from dba_ind_partitions;

select distinct status from dba_ind_subpartitions;

spool off

2) Export respective schema and whole DB as part of prechecks. 3) Create a Guaranteed Restore point and drop before dropping tablespace if everything is… Read More

Roles (L1,L2,L3) in SQL Server DBA

4 years ago by Sridhar YSQL Server DBA

Dear Readers, In this article, we will discuss Roles (L1,L2,L3) in SQL Server DBA. Please Fill below Google Form (If we missed any Products, Tools, and Utilities that you use in achieving your goals as a SQL Server Administrator.) 👇🏻👇🏻👇🏻👇🏻👇🏻 https://forms.gle/roles L1 Roles: ➼Check to… Read More

ORACLE ARCHITECTURE -2

6 years ago by Binnary Ajay KumarOracle Architecture

ORACLE SERVER ARCHITECTURE What is Oracle Sever ? Oracle Server is a combination of Instance and database . What is an Oracle instance ? Instance is a gateway in-order to access the database. Without instance we can’t access database and its objects . Every database… Read More

Oracle : REDOLOG FILE MANAGEMENT

7 years ago by SoundharyaOracle Architecture

REDOLOG FILE MANAGEMENT Redolog files are used to store latest transactional information which are used for recovery purpose incase of any instance failure. These are mandatory files for the database. The latest transactional data will send to redolog buffers then it will send… Read More

Oracle : Steps for Update Statement Process in SGA

8 years ago by DayakerOracle Architecture

Steps for Update Statement Process in SGA: update processes >update emp set sal=5000 where empno=7903; step 1 when user fires update statement first it will start user processes step 2 user processes to server processes with the help of user processe interface step 3… Read More

Oracle : Steps for Select Statement Process in SGA

8 years ago by DayakerOracle Architecture

Steps for Select Statement Process in SGA : >SELECT * FROM EMP; step 1 when user fires update statement first it will start user processes step 2 user processes to server processes with the help of user processe interface step 3 server processes to… Read More