sql – KTEXPERTS

Run SQL as JENKINS_UI_job by SQLplus script runner plugin

Run SQL as JENKINS_UI_job by SQLplus script runner plugin To perform SQL operations connecting to Remote Oracle DB without SSH connection. This article uses the Jenkins master node to run/execute the SQL. ▪️ SQL plus Script Runner plugin is required to run the SQL code… Read More

Connecting SQL database though SQL Server Management Studio (SSMS)

1 year ago by Chandra SekharAzure

Connecting SQL database though SQL Server Management Studio (SSMS) For Connecting SQL Database through SSMS in Azure: The following prerequisites are required for connect SQL Database through SSMS are 👉 SQL Database: Azure SQL Database is a cloud based Relational Database service which has all… Read More

PostgreSQL: Contrib Modules

1 year ago by Prudhvi TejaPostgreSQL

Contrib Modules: In PostgreSQL, “contrib” stands for “contributed modules”. These are additional functionalities and features developed by the community that are not part of the core PostgreSQL distribution, but can be easily added to enhance the capabilities of the database. Contributed modules are distributed separately… 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

HOW TO CONNECT AND ACCESS ATP DATABASE

3 years ago by Venkata Sai PavanOracle DB Admin

HOW TO CONNECT AND ACCESS ATP DATABASE In this article, we will know “HOW TO CONNECT AND ACCESS ATP DATABASE” We can access ATP database through SQL developer for that we have to download wallet file of this particular database 1. Click on the ATP… Read More

Upgradation and Data Migration in SQL Server

4 years ago by KTEXPERTS TEAMSQL Server DBA

Dear Readers, In this article, we will see the Upgradation and Data Migration in SQL Server. Version upgradation 2014 to 2016 SQL 2014 evaluation edition….. Enterprise edition. In SQL server 2014 standard edition to enterprise edition The upgrading the version in SQL server 2014 it… Read More

Tools for Administering the SQL Server

4 years ago by Sridhar YSQL Server DBA

Dear Readers, In this article, we will discuss Tools for Administering the SQL Server. 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/sqlserver SQL Server Supported Platforms… Read More

Tools for Administering the Oracle Database

4 years ago by Vinod SiramOracle DB Admin

Dear Readers, In this article, we will discuss Tools for Administering the Oracle Database Please Fill below Google Form (If we missed any Products, Tools, and Utilities that you use in achieving your goals as a Cassandra Administrator.) 👇🏻👇🏻👇🏻👇🏻👇🏻 https://forms.gle/oracledba Monitoring Tools: OEM Nagios EM… Read More

Protected: Oracle DBA Training Status

5 years ago by Ramesh ReddyOracle DB Admin

There is no excerpt because this is a protected post.

INTRODUCTION TO SQL(Article -1)

6 years ago by Ramesh ReddyMySQL

INTRODUCTION TO SQL Overview : SQL is a language of database, it includes database creation, deletion, fetching rows and modifying rows etc. What is SQL? SQL is Structured/Syntax/Sequential Query Language, which is a language for storing, manipulating and retrieving data stored in relational database. SQL… Read More