Kavyas Tutorials

Wednesday, 10 July 2024

AWS Cloud Practitioner — Top 50 Must-Know Points (CLF-C02)

✅ AWS Cloud Practitioner — Top 25 Must-Know Points (CLF-C02)

(Official exam weightage-based topics)

IAM (Identity & Access Management) – AWS permissions, users, groups, roles, MFA, least privilege.
EC2 (Elastic Compute Cloud) – Virtual servers, pricing (On-Demand, Reserved, Spot), security groups.
Lambda – Serverless compute, event-driven, pay per request.
S3 (Simple Storage Service) – Object storage, 11 9s durability, storage classes, versioning.
S3 Glacier – Archival storage, extremely low-cost, slow retrieval.
EBS (Elastic Block Store) – Block storage for EC2, snapshots.
RDS – Managed SQL databases (MySQL, PostgreSQL, Aurora).
DynamoDB – Serverless NoSQL, millisecond latency.
VPC – Networking: subnets, route tables, NAT, NACLs.
CloudFront – Global CDN, edge caching.
Route 53 – DNS, domain registration, routing policies.
Load Balancers (ELB) – ALB, NLB, CLB.
Auto Scaling – Automatic EC2 scaling.
SQS – Message queue, decoupling apps.
SNS – Push notifications, pub/sub messaging.
EventBridge – Event bus connecting AWS services.
API Gateway – Secure APIs for Lambda & services.
CloudWatch – Metrics, logs, alarms, dashboards.
CloudTrail – API activity logging.
AWS Organizations – Multi-account mgmt, SCPs.
KMS – Encryption key management.
WAF & Shield – Web firewall + DDoS protection.
Cost Explorer – Track & forecast AWS spend.
Trusted Advisor – Best practice checks.
Shared Responsibility Model – AWS vs Customer security roles.

Additional High-Value Exam Topics

AWS Global Infrastructure – Regions, AZs, Edge Locations.
Well-Architected Framework – 5 pillars.
Cloud Adoption Framework (CAF) – Business, People, Governance, Platform, Security, Operations.
AWS Budgets – Budget alerts.
AWS Billing Dashboard – Billing & usage tracking.
Free Tier Types – 12-month, Always free, Trials.
Amazon Cognito – User authentication & identity pools.
AWS Backup – Centralized backup service.
AWS Artifact – Compliance reports.
AWS Service Health Dashboard – Public AWS outages status.
AWS Personal Health Dashboard – Account-specific alerts.
Amazon Lightsail – Simple VPS & hosting.
AWS Outposts – On-prem AWS hardware.
AWS Snowball / Snowmobile – Offline data migration devices.
AWS Storage Gateway – Hybrid storage.
EFS – Serverless Linux file system.
Amazon FSx – High-performance file systems.
AWS Systems Manager – Patch, automation, session mgmt.
AWS Trusted Advisor Categories – Cost, Performance, Fault Tolerance, Security, Service Limits.
AWS IAM Identity Center (SSO) – Central login for AWS accounts.
AWS Marketplace – SaaS, AMIs, software marketplace.
AWS SAM – Serverless app framework.
CloudFormation – Infrastructure as code.
Amazon MQ – Managed message brokers.
AWS X-Ray – Distributed tracing.
AWS Global Accelerator – Improve latency.
Amazon Inspector – Security vulnerability scanning.
AWS Shield Advanced – Enterprise-grade DDoS protection.
Amazon AppStream 2.0 – Stream desktop apps.
Amazon WorkSpaces – Virtual desktops (VDI).

Thursday, 20 June 2024

AWS Cloud Practitioner — Top 55 Must-Know Points (CLF-C02)

56 – 135: Core AWS Services, Security, Networking, Cost & AI

AWS Control Tower – Multi-account setup with guardrails (best practice governance).
AWS Landing Zone – Initial secure enterprise account blueprint (older concept).
Multi-AZ vs Multi-Region – Multi-AZ → high availability | Multi-Region → disaster recovery.
AWS Backup Vault Lock – Prevents backups from being deleted (ransomware protection).
AWS Elastic Disaster Recovery (DRS) – Fast recovery of servers from on-prem/AWS to AWS.
AWS Migration Hub – Central place for migration tracking.
AWS Application Migration Service (MGN) – Lift-and-shift migration tool.
AWS Database Migration Service (DMS) – Move databases between engines (e.g., Oracle → Aurora).
Amazon Aurora Serverless v2 – Auto-scales instantly, SQL-compatible.
Amazon QLDB – Immutable ledger database (tamper-proof log).
Amazon Neptune – Graph database for relationships (social graphs).
Amazon OpenSearch Service – Search, logs, analytics (replacement for Elasticsearch).
AWS Step Functions – Serverless orchestration of Lambda / SQS / APIs.
AWS Batch – Run large batch workloads without managing servers.
AWS Glue – Serverless ETL service for data processing (data catalog).
Amazon Athena – Query S3 data using SQL — serverless analytics.
Amazon QuickSight – Visualization and BI dashboards.
Amazon Kinesis – Real-time data streaming analytics.
Amazon EMR – Big data processing (Spark, Hadoop, Hive).
AWS IQ – Hire certified AWS experts for on-demand work.
AWS CodeCommit – Private Git repositories service.
AWS CodeBuild – Fully managed CI build service.
AWS CodeDeploy – Deploy to EC2, Lambda, on-prem servers.
AWS CodePipeline – Orchestrate CI/CD pipelines.
AWS AppRunner – Easy way to run containerized apps (fully managed).
AWS Elastic Beanstalk – Deploy applications without managing servers (PaaS).
AWS Amplify – Frontend/mobile hosting + backend APIs + Auth.
AWS GameLift – Deploy and scale multiplayer game servers.
AWS IoT Core – Connect and manage IoT devices securely.
AWS Greengrass – Run Lambda + ML on edge (IoT gateway).
IAM Policies – JSON format, Allow/Deny rules, identity-based + resource-based.
IAM Permission Boundaries – Maximum permissions allowed to a user/role.
IAM Access Analyzer – Detects public or cross-account access.
AWS GuardDuty – Threat detection using machine learning.
AWS Macie – Finds & protects sensitive S3 data (PII detection).
AWS Detective – Investigates security incidents using AI.
Security Groups – Stateful firewall for EC2 instances.
NACL (Network ACL) – Stateless VPC security layer.
VPC Peering – Connect VPCs privately (one-to-one).
AWS Transit Gateway – Central hub connecting thousands of VPCs.
VPC Endpoints – Private AWS service connections.
Direct Connect – Dedicated fiber link to AWS.
AWS Certificate Manager (ACM) – Free SSL/TLS certificates.
AWS Secrets Manager – Store & rotate secrets.
AWS Parameter Store – Configuration storage (SSM).
Public vs Private Subnet – Public → IGW | Private → NAT.
Route Tables – Control traffic flow between subnets.
NAT Gateway – Secure internet access for private subnets.
Internet Gateway – Public internet access.
AWS Global Infrastructure Benefits – Fault tolerance, availability, low latency.
Availability Zones Failure Independence – One AZ failure won’t affect others.
Edge Caching in CloudFront – Improves speed, reduces origin load.
S3 Object Lock – Prevents deletion of objects.
MFA Delete – Extra layer for S3 delete protection.
AWS Resilience Hub – Automatic workload resilience evaluation.
AWS Support Plans – Basic, Developer, Business, Enterprise On-Ramp, Enterprise.
Business Plan Features – 24/7 support, full Trusted Advisor, fast response.
Enterprise Support Features – Architect, TAM, concierge billing.
AWS Cost Anomaly Detection – Alerts on unusual usage spikes.
AWS Savings Plans – 1 or 3-year compute commitments.
Compute Savings Plan – EC2 + Lambda + Fargate.
EC2 Instance Savings Plan – EC2 only.
Spot Instances Best Use Cases – Batch & flexible workloads.
Reserved Instances – Capacity commitment for discounts.
AWS TCO Calculator – On-prem vs cloud cost comparison.
AWS Pricing Calculator – Estimate AWS architecture cost.
AWS Billing Alarms – Cost threshold alerts.
AWS Glue Data Catalog – Central metadata store.
S3 Replication – Cross-region / same-region replication.
S3 Pre-Signed URLs – Temporary secure access URLs.
Athena Partitioning – Faster & cheaper queries.
Redshift Spectrum – Query S3 data from Redshift.
Kinesis Firehose – Streaming ingestion → S3 / Redshift.
Kinesis Data Streams – Real-time event pipeline.
Amazon Polly – Text-to-speech ML.
Amazon Rekognition – Image & video analysis.
Amazon Textract – OCR & document text extraction.
Amazon SageMaker – End-to-end ML platform.
AWS Snowcone – Portable 8TB edge device.
AWS Compute Optimizer – Cost & performance recommendations.

Saturday, 20 April 2024

AWS Cloud Practitioner — Top 100 Must-Know key Points (CLF-C02)

136 – 210: Compute, Storage, Database, Analytics, AI

EC2 Instance Families – General (t), Compute (c), Memory (r/x), Storage (i), GPU (g/p).
EC2 Instance Types Naming – Example: m5.large → m = family, 5 = generation, large = size.
Spot Fleet – Group of spot instances for cost optimization.
Placement Groups – Cluster, Partition, Spread (HPC & big workloads).
Amazon Fargate – Serverless containers; no EC2 management.
ECS (Elastic Container Service) – Container orchestration (AWS-native, cheaper).
EKS (Elastic Kubernetes Service) – Managed Kubernetes (heavy workloads).
ECS Anywhere – Run ECS on on-prem or hybrid.
Firecracker MicroVM – Technology behind Lambda & Fargate.
Lambda Layers – Share common code/resources across Lambda functions.
Lambda Provisioned Concurrency – Zero cold start.
Lambda Execution Role – Permissions function needs to access AWS resources.
Lambda Function Timeout – Max 15 minutes.
AWS Batch Compute Environments – Automate batch workloads on EC2/Spot/Fargate.
Amazon Lightsail Containers – Simple container hosting.
Spot Blocks – Spot instances with guaranteed run time.
AMI (Amazon Machine Image) – Blueprint for EC2 instances.
Elastic GPU – Attach GPU to EC2.
EC2 Hibernate – Preserves RAM to EBS.
ENI (Elastic Network Interface) – Multiple IPs, network cards.
EC2 Instance Metadata v2 – IMDSv2 more secure (mandatory in exam).
Instance Store – Temporary storage, data lost on stop/terminate.
Capacity Reservations – Guarantee EC2 capacity for a period.
EC2 Dedicated Host – Physical server fully dedicated to you.
EC2 Dedicated Instance – Runs on hardware not shared with other accounts.
S3 Access Points – Custom access paths for apps.
S3 Inventory – Report of objects & metadata.
S3 Object ACLs – Legacy permissions (discouraged, but exam asks).
S3 Block Public Access – Account-level public block.
S3 Bucket Policy – JSON resource-based permission.
S3 CORS – Cross-domain access configuration.
S3 Select – Query a subset of S3 object.
S3 Multi-Part Upload – Recommended for >100MB objects.
S3 Transfer Acceleration – Speeds uploads using CloudFront edge locations.
S3 Intelligent-Tiering – Automatically moves objects for cost saving.
S3 Requester Pays – Requester pays for download.
EFS Throughput Modes – Bursting, Provisioned.
EFS Storage Classes – Standard, Infrequent Access (IA).
EBS Volume Types – gp3, io2, sc1, st1.
EBS Multi-Attach – Attach to multiple EC2 at the same time.
EBS Snapshots Archive – Cheaper archival storage.
EBS Fast Snapshot Restore – High-performance restore.
AWS Storage Gateway Types – File, Volume, Tape.
AWS Snowball Edge Compute – Includes GPU, compute power.
AWS DataSync – Automated large-scale data transfer.
AWS Transfer Family – Managed SFTP/FTP/FTPS.
AWS Backup Policies – Backup plans, vaults, schedules.
Glacier Retrieval Classes – Expedited, Standard, Bulk.
S3 Lifecycle Policies – Automate class transitions + deletion.
AWS EFS One-Zone – Cheaper but less durable.
RDS Multi-AZ Failover – Automatic standby failover.
RDS Read Replicas – Read scaling.
RDS Automated Backups – Point-in-time recovery.
Aurora Global Database – Lag < 1 second between regions.
DynamoDB Global Tables – Multi-region active-active.
DynamoDB TTL – Automatic expiry of items.
DynamoDB Streams – Change data capture for Lambda.
DynamoDB Accelerator (DAX) – In-memory cache for Dynamo.
ElastiCache Redis – Sub-millisecond caching.
OpenSearch Dashboards – Visualization tool (Kibana alternative).
Athena Federated Query – Query other sources beyond S3.
Glue Crawlers – Scan data & generate schema.
Glue Jobs – ETL pipelines.
Kinesis Analytics – Real-time SQL analytics on streaming data.
Kinesis Shards – Unit of capacity for streams.
Redshift Concurrency Scaling – Handles spike workloads.
Redshift RA3 Nodes – Managed storage.
EMR Serverless – No cluster management.
EMR Notebooks – Data exploration with Jupyter.
SageMaker Ground Truth – Labeling tool for ML data.
SageMaker Studio – IDE for ML model lifecycle.
Transcribe – Speech-to-text.
Translate – Language translation.
Comprehend – NLP sentiment analysis.
Forecast – Time-series forecasting using ML.

🔥 SET-9 (25 Points) – Developer Tools, DevOps, Monitoring, Cost, Support

CloudFormation Drift Detection – Checks differences between stack & deployed resources.
CloudFormation Change Sets – Preview changes before applying.
CDK (Cloud Development Kit) – Define infra using Python/TS/Java.
Elastic Beanstalk Health Monitoring – Green / Yellow / Red indicators.
AWS SAM CLI – Test Lambda / APIs locally.
CodeStar – Unified DevOps project setup.
CodeArtifact – Package dependency management (npm / pypi / maven).
CodeGuru Reviewer – AI code review.
CodeGuru Profiler – Detects performance bottlenecks.
CloudWatch Events – Deprecated → EventBridge.
CloudWatch Logs Insights – Query logs using SQL-like syntax.
CloudWatch Metrics Resolution – Standard: 1 min | High-resolution: 1–30 sec.
CloudWatch Embedded Metric Format – Send custom metrics.
CloudTrail Insights – Detect unusual API activity.
AWS Health API – Programmatic health alerts.
AWS Service Quotas – Manage & request service limit increases.
Cost Categories – Group cost data logically.
Reservation Utilization Report – See how RIs are used.
Savings Plans Utilization – Commitment utilization tracking.
AWS Marketplace Metering – Pay-as-you-go software usage.
AWS Concierge Support – Enterprise-only cost help.
TAM (Technical Account Manager) – Enterprise support engineer.
AWS Abuse Team – Report spam / misuse.
AWS Penetration Testing Rules – Allow list for pentesting EC2.
Well-Architected Tool – Checks workloads against AWS 5 pillars.

Tuesday, 13 February 2024

AWS Cloud Practitioner — Top 76 Must-Know Points (CLF-C02)

236 – 260: Edge, Networking & Hybrid

Edge Locations Count – CloudFront POPs worldwide (exam sometimes asks conceptually).
Regional Edge Caches – Bigger caches between origin and POP.
AWS Local Zones – Compute closer to users in metro cities.
Wavelength Zones – For 5G ultra-low latency edge compute.
AWS Outposts Rack – Smaller on-prem AWS hardware.
AWS Outposts Server – Even smaller, fits in standard IT rack.
AWS Egress Fees – Cost for data leaving AWS.
Public vs Elastic IP – Elastic IP → fixed, account-owned.
ENA (Elastic Network Adapter) – High-performance networking for EC2.
Global Accelerator vs CloudFront – GA → TCP/UDP app acceleration | CF → content delivery & caching.
VPC Flow Logs – Capture network traffic metadata.
VPC Reachability Analyzer – Find connectivity issues.
IPv6 in VPC – Dual-stack possible.
AWS Shield Standard – Free DDoS protection.
AWS Route 53 Health Checks – Monitor endpoints & failover.
Route 53 Routing Policies – Simple, Weighted, Latency, Geolocation, Failover, Multi-value.
AWS IoT Fleet Hub – Monitor IoT devices fleet-wide.
AWS IoT Device Defender – Security audits for IoT devices.
AWS IoT Device Management – Scale registration + remote actions.
AWS RoboMaker – Robotics simulation & deployment.
Direct Connect Gateway – Connect DC to multiple VPCs across regions.
Transit Gateway Multicast – Support multicast traffic.
Cloud WAN – Global wide-area networking managed by AWS.
PrivateLink – Private access to services via interface endpoints.
Hybrid DNS with Route 53 Resolver – On-prem ↔ AWS DNS integration.

🔥 SET-11 (25 Points) – Data, AI, Application Integration, Messaging

Kinesis Enhanced Fan-Out – 1 MB/s per shard throughput for consumers.
Kinesis Extended Retention – Store streams up to 1 year.
Kinesis Shard Splitting – Increase capacity.
Kinesis Shard Merging – Reduce capacity.
SQS Delay Queue – Delay messages up to 15 min.
SQS Long Polling – Reduces empty responses → lowers cost.
SQS FIFO Throughput – Per-message ordering guaranteed.
SNS Mobile Push – Push to APNS / FCM.
SNS SMS Types – Promotional vs Transactional.
EventBridge Pipes – Point-to-point event flow.
EventBridge Schedule – Cron-based serverless jobs.
Step Functions Express Workflow – High-throughput rapid workflows.
Glue DataBrew – Visual data preparation without coding.
Glue Elastic Views – Unified view of multiple data stores.
Lake Formation – Data lake permission control.
Redshift Spectrum Pricing – Pay only per scanned TB.
Redshift Materialized Views – Precomputed data for performance.
OpenSearch UltraWarm Storage – Warm storage tier.
OpenSearch Domain – Cluster of search nodes.
Forecast Use-Cases – Retail sales, cloud capacity, traffic prediction.
Personalize – Recommendation engine.
Lookout for Metrics – Detect anomalies using ML.
Lookout for Equipment – Predict equipment failures.
Textract Queries – Search for specific fields in documents.
Rekognition Custom Labels – Train custom image models.
Transcribe Call Analytics – Contact-center call analysis.
Polly Neural TTS – More natural speech.
Amazon Lex V2 – Chatbot building (NLP + intent slots).
Amazon Bedrock – Foundation models for GenAI (conceptual).
AWS HealthLake – Healthcare data processing (HIPAA-ready).

Monday, 10 April 2023

Defragmentation Implementation in ODI

Defragmentation Implementation in ODI:

Execute below Code in database to create tables:

create table bi11g_dw.defragment_table as

select owner, table_name,sum(BLOCKS) S,'N' as flag

FROM ALL_TABLES

where owner in ('BI11G_DW', 'BI12C_BIA_ODIREPO','STG') and

(ROUND(((BLOCKS*16/1024)-(NUM_ROWS*AVG_ROW_LEN/1024/1024)),2)/ROUND(((BLOCKS*16/1024)),2))*100 >20 and

--table_name not like '%CFG%' and avg_row_len <> '0' group by owner, table_name order by s ASC;

------------------------------------------------------------------------------------------------------------

create table bi11g_dw.defragment_ind_table as select a.owner, a.table_name, b.index_name, 'N' as FLAG from bi11g_dw.defragment_table a, all_indexes b where a.table_name=b.table_name and a.owner=b.owner;

-----------------------------------------------------------------------------------------

CREATE TABLE "BI11G_DW"."DEFRAGMENT_TMP"

( "TABLE_NAME" VARCHAR2(50 BYTE)

)

------------------------------------------------------------------------------------------

ODI Proc code:

-------------------

step 1:

update bi11g_dw.defragment_table set flag='N';

Step2:

DECLARE

CUR1 SYS_REFCURSOR;

CUR2 SYS_REFCURSOR;

V_TABLENAME VARCHAR2(50);

V_INDEXNAME VARCHAR2(50);

V_OWNER VARCHAR2(50);

BEGIN

OPEN CUR1 FOR

select owner,table_name from bi11g_dw.defragment_table WHERE FLAG='N' order by s ASC;

LOOP

FETCH CUR1 INTO V_OWNER,V_TABLENAME;

EXIT WHEN CUR1%NOTFOUND;

EXECUTE IMMEDIATE'TRUNCATE TABLE bi11g_dw.defragment_tmp';

insert into bi11g_dw.defragment_tmp values (V_TABLENAME);

EXECUTE IMMEDIATE 'ALTER TABLE '||V_OWNER||'.'||V_TABLENAME||' MOVE';

OPEN CUR2 FOR SELECT OWNER,TABLE_NAME, INDEX_NAME FROM bi11g_dw.defragment_ind_table WHERE TABLE_NAME = V_TABLENAME;

LOOP

FETCH CUR2 INTO V_OWNER,V_TABLENAME,V_INDEXNAME;

EXIT WHEN CUR2%NOTFOUND;

EXECUTE IMMEDIATE'TRUNCATE TABLE bi11g_dw.defragment_tmp';

insert into bi11g_dw.defragment_tmp values (V_TABLENAME);

EXECUTE IMMEDIATE 'ALTER INDEX '||V_OWNER||'.'||V_INDEXNAME||' REBUILD ONLINE';

UPDATE bi11g_dw.defragment_ind_table SET FLAG='Y' WHERE TABLE_NAME=V_TABLENAME AND INDEX_NAME=V_INDEXNAME;

commit;

END LOOP;

dbms_stats.gather_table_stats(ownname => V_OWNER, tabname =>V_TABLENAME, estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

UPDATE bi11g_dw.defragment_table SET FLAG='Y' WHERE TABLE_NAME=V_TABLENAME;

COMMIT;

END LOOP;

END;

ODI Proc2:

---------

update Bi11G_DW.defragment_table set flag='E' where table_name in (select * from Bi11G_DW.defragment_tmp)

How to check table space in database

Query : To check tablespace in database

select t.tablespace, t.totalspace as " Totalspace(MB)",
     round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
     nvl(fs.freespace,0) as "Freespace(MB)",
     round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
     round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free",
     us.USERNAME
     from
     (select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
     from dba_data_files d
     group by d.tablespace_name) t,
     (select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
     from dba_free_space f
     group by f.tablespace_name) fs,
     ( SELECT USERNAME ,default_tablespace FROM dba_users) us     where t.tablespace=fs.tablespace (+)   AND t.tablespace(+) = us.default_tablespace
     order by "% Free";

Saturday, 28 May 2022

Get results for all tables row count in schema and truncate all tables at a time in schema

get results for all tables row count in schema

---------------------------------------------------

SET SERVEROUTPUT ON;

DECLARE

VAL NUMBER;

BEGIN

for i in (SELECT table_name FROM all_tables WHERE owner = 'Ora_Schema' and table_name like '%GL%')

LOOP

EXECUTE IMMEDIATE 'SELECT count(*) from Ora_Schema.' || i.table_name INTO VAL;

DBMS_OUTPUT.PUT_LINE(i.table_name || ' | ' || VAL);

END LOOP;

END;

truncate all tables at a time in schema

---------------------------------------------------

set serveroutput on;

begin

for t1 in (SELECT table_name FROM all_tables WHERE owner = 'Orc_schema' and table_name in ('GL_LEDGERS','GL_CALENDARS')) loop

begin

----- dbms_output.put_line('truncate table '||t1.table_name || ';');

execute immediate ('truncate table '||t1.table_name);

end;

end loop;

end;

Wednesday, 4 May 2022

Remove all characters after a specific character in oracle query

Monday, 25 April 2022

get/extract char , date , numeric values from varchar column

requirement:

amount column having numeric data , char data and date also

So char /date data into one column and numeric (+ve, -ve ) values in another column

use below query to acheive that:

select

case when trim(TRANSLATE(replace(substr(amount_in_char,0,1),'-','')||substr(amount_in_char,2), '0123456789,.', ' ')) is not null

then amount_in_char end getchardtae,

case when trim(TRANSLATE(replace(substr(amount_in_char,0,1),'-','')||substr(amount_in_char,2), '0123456789,.', ' ')) is null

then amount_in_char end getnum,

amount_in_char original from w_plan_fs;

Tuesday, 19 April 2022

Find out the long running queries/locked objects in database

Use below queries

check the odi session id status in database

Example:

select * from GV$SESSION WHERE ACTION LIKE '%21622%'; (odi session id)

SELECT * FROM V$SESSION_LONGOPS WHERE SID=1392;

SELECT * FROM V$SESSION WHERE SEQ#='9383';ACTION LIKE '%21622%';

SELECT SQL_FULLTEXT ,LOCKED_TOTAL,SQL_ID FROM GV$SQL WHERE SQL_ID='383g771j';

SELECT * FROM GV$SQL;

alter system kill session '648,51886' IMMEDIATE

select username, status from v$session where SID=12;

Friday, 11 March 2022

employee-manager relationship hierarchy using SQL Query

As you can see, I had to put a hardcoded value for EMPLEVEL field in the Recursive section of the query above. This was because I did not join the Recursive section of the query with our recursive CTE. Now, in order to make the entire hierarchical query work with actual hierarchical level of records assigned, we need to complete the INNER JOIN as in the below query. Notice the difference in value of EMPLEVEL field. This is the complete hierarchical query in CTE format. Please note that ORDER BY is optional here.

WITH OURCTE (EMPNO, ENAME, MGR, EMPLEVEL)
AS (SELECT EMPNO, ENAME, MGR, 1 EMPLEVEL ---Initial Subquery
FROM Emp
WHERE MGR IS NULL
UNION ALL
SELECT E.EMPNO, E.ENAME, E.MGR, CTE.EMPLEVEL + 1 ---Recursive Subquery
FROM EMP E
INNER JOIN OURCTE CTE ON E.MGR = CTE.EMPNO –Inner Join
WHERE E.MGR IS NOT NULL)
SELECT *
FROM OURCTE
ORDER BY EMPLEVEL;

If you need to display the manager name next to the manager ID, refer to the following query:

SELECT CTE.EMPNO, CTE.ENAME, CTE.MGR, E.ENAME “MGR NAME”, CTE.EMPLEVEL
FROM (
WITH OURCTE (EMPNO, ENAME, MGR, EMPLEVEL)
AS (SELECT EMPNO, ENAME, MGR, 1 EMPLEVEL –Initial Subquery
FROM Emp
WHERE MGR IS NULL
UNION ALL
SELECT E.EMPNO, E.ENAME, E.MGR, CTE.EMPLEVEL + 1 –Recursive Subquery
FROM EMP E
INNER JOIN OURCTE CTE ON E.MGR = CTE.EMPNO
WHERE E.MGR IS NOT NULL)
SELECT *
FROM OURCTE
ORDER BY EMPLEVEL) CTE
LEFT OUTER JOIN EMP E ON CTE.MGR = E.EMPNO
ORDER BY CTE.EMPLEVEL,E.ENAME;