sql – KTEXPERTS

Introduction to SQL Commands (DDL, DML,DQL, DCL, TCL)

Introduction to SQL Commands (DDL, DML,DQL, DCL, TCL) Beginner-Friendly Professional Documentation 1. SQL Basics & Command Categories What is SQL? SQL (Structured Query Language) is a standard language used to manage and interact with databases. Databases store data in the form of tables (rows and… Read More

Data Retrieval language(DRL)

2 weeks ago by Lavanya NandagiriSQL

Data Retrieval language(DRL) Data Retrieval Language (DRL) is a part of SQL used to retrieve (fetch) data from a database without modifying it. Main Command: SELECT – used to get data from one or more tables. Examples:

SELECT * FROM students;
→ Retrieves all records from the students table.

SELECT name, marks FROM students WHERE marks > 60;
→ Retrieves names and marks of students who scored more than 60.

SELECT * FROM students;

→ Retrieves all records from the students table.

SELECT name, marks FROM students WHERE marks > 60;

→ Retrieves names and marks of students who scored more than 60.

Purpose: ● To view data ● To… Read More

Data Definition Language (DDL)

2 weeks ago by Kishor KumarSQL

Data Definition Language (DDL) Introduction Structured Query Language (SQL) is the standard language used for interacting with relational databases. SQL is divided into different categories based on the type of operations performed on the database. Among these categories, Data Definition Language (DDL) plays a foundational… Read More

Introduction to SQL

3 weeks ago by Bharath KumarSQL

Introduction to SQL What is SQL? SQL (Structured Query Language) is a database computer language used to communicate with and retrieve data from relational databases. It allows users to store, manipulate, update, and retrieve data efficiently. SQL is widely used with popular database systems such… Read More

Understanding SQL Server Architecture – Article 1

3 months ago by Vinod SiramSQL Server DBA

Understanding SQL Server Architecture Microsoft SQL Server is a relational database management system (RDBMS) designed to store, manage and retrieve data efficiently. To achieve high performance, reliability and scalability SQL Server is structured into several inter-related components that work together to process user queries, manage… Read More

Run SQL as JENKINS_UI_job by SQLplus script runner plugin

2 years ago by Vinod SiramDevOps

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)

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

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

3 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

4 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