Installation over Oracle Linux

• Version of the Linux Distro which I am using ,

cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="7.9"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.9"
PRETTY_NAME="Oracle Linux Server 7.9"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:9:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://github.com/oracle/oracle-linux"
ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.9
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.9

• Install the repository RPM,

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

• Install PostgreSQL,

sudo yum install -y postgresql13-server

Optionally initialize the database and enable automatic start

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb [TBD]
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13


initdb

• This file will create all DB related files , directories & Configuration files.

• Also creation of system catalogs --> what is this ? --> It will store all indexes , roles , etc. --> you can see list of catalogue using below statement.

postgres=# select relname from pg_catalog.pg_class where relkind='r';

sudo -i -u postgres psql

• By default a user , a role and a database will be created.
• All the above 3 will be created in same name "postgres".
• Then how to check the roles ?

postgres=# \du

• Now see the list of databases, ( these 3 are created by postgres by default with the help of template1.

postgres=# \l

• If you try to create a new DB , then it will follow the "template1", its like the blueprint.
• Then why template0 ? --> you can edit template1 but not template0 ( you can't change and its not editable ).
• so in short template0 --> NOT EDITABLE & template1 --> EDITABLE.

WAL

• WRITE AHEAD LOG.
• If the database gets crashed , with this help we can retrieve the data.

Roles

• Librarian , Library member and visitor all these are roles.

• Lets create a dummy role and test ,

• You can see "Cannot login" , that means you can't login as it's not assigned.

• creating one more ROLE,

• Assigning the permissions or roles,

• Here you can't see 'CANNOT LOGIN'.
• If a ROLE can login then he/she is the user.
• User can inherits the parents also.

How it works ?

Postmaster

• All connection request goes first here. ( whatever client ask , server will respond )
• Follows client server architecture.

WAL Writer

• It will be write in a notebook , to keep the track of all.

Checkpoint

• It periodically checks and responed.

Auto Vaccum

• It periodically checks.

Stats Collector

• Gathers all stats Eg., if a table is getting more request then it will collect the information and try to keep it in cache to respond very fast and also check whether index is there for the table.

Archiver

• To backup the data.

Reference

https://www.postgresql.org/download/linux/redhat/ --> This site will provide steps to install in different distro's.

Notes

1. "template0" is called pristine.

Questions

1. what command initialize a PostgresSQL database cluster? initdb
2. which role is automatically created during PostgresSQL installation ? postgres
3. what is the purpose of template0 in PostgresSQL ? Pristine , unmodified template.
4. which PostgresSQL template database is used as the default for creating new databases ? template1
5. how to connect PostgresSQL interactive terminal ? psql
6. List all databases ? \l
7. Architecture of PostgresSQL ? Client Server

• Its nothing but " Bulk Loader Utility ".
• With this concept we can load the data to the table in bulk.
• Main word is LOAD.
• Then comes to your mind , what is the difference between load and insert ? Insert happens one by one.Load happens in one go.
• what data ? which table ? loading script ? Execute --> these are the four things YOU NEED TO KEEP IN MIND.

• Flat files --> csv ( comma separated value ) , txt , dat , excel , etc.
• Always use notepad to load the data.

select employee_id || ',' || first_name || ',' || salary from employees_table where rownum <= 10 ; --> this will fetch only 10 rows.

• save this file in a folder as csv.

select employee_id || ',' || first_name || ',' || salary from employees_table where employee_id between 150 and 170 ; --> this will fetch rows between those values.

• Save this file as txt.

Now coming to table creation

create table sample(id number , name varchar2(25) , salary number);

Now coming to creation of script

• loading script or control file both are same.

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
insert into table sample
fields terminated by ','
(id,name,salary)

• create the script and save as ALL FILES ( notepad ) with .ctl file.

Now coming to Execute

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' direct = true

• here why direct=true --> it will load very fast and it will by-pass all constraints and triggers.
• if direct=false --> constraints and triggers it will check and then it execute.

• In short ,

Excluding one column

• If you some column should not be loaded , then use FILLER.

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
insert into table sample
fields terminated by ','
(id,name,salary filler)

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
insert into table sample
fields terminated by ','
(id,name filler,salary)

• In above example , salary and name will be empty . It won't load the data.

Condition

• WHEN --> loading data should obey the condition which you give. If the condition fails , then it stores the failed data in DISCARD FILE.
• If there is Oracle error , then it gets captured in BAD FILE.

• WHEN condition should be used here,

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
insert into table sample when ?
fields terminated by ','
(id,name filler,salary)

How to get the process summary ?

• It will be stored in log file.
• you can set all the files in the command itself , like below.

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' log = summary.log bad = sample.bad discard = sample.dsc direct = true

• If you are giving any file name here , then it will generate automatically.
• So Import take here is ,

skip

• If you want to skip the rows while loading , then you can specify in the command itself.

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' skip = 2 direct = true

• 2 rows will be skipped.

Notes

• SQL loader short key word is sqlldr.
• insert into table sample --> this will work only when the table is EMPTY. If you try to execute again , then it throw below error.

so you can use ,

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
append into table sample
fields terminated by ','
(id,name,salary)

• Also you use truncate ( it will delete old data and insert new data again )

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
truncate into table sample
fields terminated by ','
(id,name,salary)

Task

1. For a particular column instead of (,) separated it's used as (#) - how to load ?
2. how to load the excel file ?

• Network --> communications between devices.
• IP Address --> unique identifier to each device which is internet protocol address.

IPv4

1. 4th of version of Internet protocol.
2. 32 bit
3. Totally 4 blocks with 8 bit segments each. A , B , C & D.
4. 2 types of IP address.
5. Public ( Mainly using internet routing ) & Private ( Office )
6. Range --> 0 to 255
7. 0 & 255 reserved by system.
8. 127 --> loop-back address. 253 address we can use.
9. Then how to find whether its public or private ? By classes.
10. A, B , C --> Commonly used.
11. D & E --> Multi-casting & Research purpose.
12. Class A --> 0 to 127 Public , Private 10 is only used eg., 10.0.0.1 ( 16 million hosts can be declared )
13. Class B --> 128 to 191 Public , Private 172 is only used eg., 172.16.0.1 to 171.16.255.254 ( 65,536 hosts can be declared ) - Med size networks.
14. Class C --> 192 to 223 Public , Private 192.168.1.1 small network for 254 hosts.
15. Class D --> 224 to 239 for multicast groups.
16. Class E --> 240 - 255 for research purpose.

As a whole , the private range is .

A --> 10.0.0.1
B --> 172.16.0.0
C --> 192.168.0.0

Subnetting

• Slashing the network.

Virtual Network in Azure

• Software based network connects virtual machines.

Subnet in Azure

• Subdivison of VNET.
• we can organise the resources within a network.
• Features as follows ,

Azure Portal

All service >> Networking >> Virtual Networks >> Create

• Gave a wrong IP , then you can see a prompt is coming.

• VNET & Subnet creation

Notes

1. DNS , DHCP & Gateway , 255 Broadcast --> 4 IP's are reserved.

• Install Oracle Virtual Box , Winscip & Putty.
• Load the lab.
• Change the network setting from NAT to Bridged Adapter.

• Click ON & OFF its like restart.
• IP which is assigned 192.168.1.104.
• Assign any IP from 192.168.1.(0 to 255) , so I have assigned as 192.168.1.104. This IP taken from IPv4 in laptop.
• Open Terminal in the screen by right click.

• Ping Google and check whether its working or not.

• Now you can login with your IP which is assigned.

• Now we need to understand the concept of SHARED FOLDER.
• You can share the SOFTWARES to the VM from laptop folder , no need to use WINSCP. Lets explore.

• Lets create directories and unzip the software.

mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1
unzip /media/sf_Software/V982063-01-001.zip -d /u01/app/oracle/product/19.0.0.0/dbhome_1

Install all RPM required for DB installation

• Once you install this RPM , oracle user will be created automatically.

• we don't know the password of ORACLE user , now set it.

• password is oracle and user : oracle
• Now run the installer.
• Before that execute xhost + --> Received error , its mainly used to set the DISPLAY.

• Getting error while installing the runinstaller .

• May be because of LOGIN root user. Lets try with new session with ORACLE user.

• Login with VM machine to avoid the DISPLAY issue.

Now lets create Database

• Password is ORACLE

• DB installation is completed.
• How many Oracle Softwares are installed in the servers ? How to check ? oraInventory file will help to do this.

• Now comes the question , how to find the Inventory location ?

• How many databases are created in the server , if this file is changes , complete database may corrupt.

• How many are running in this server , that is the DB server ?

ps -ef|grep pmon
ps -ef|grep smon

• How to start the DB ?

• As I have faced issue with SID , changed completly to new SID and started the server.

Notes

• Oracle Home / Oracle Software / Database Software / Database Home --> All these are same.
• How many Oracle Softwares are installed in the servers ? How to check ? oraInventory file will help to do this.

• Now comes the question , how to find the Inventory location ?

• How many databases are created in the server , if this file is changes , complete database may corrupt.

• How many are running in this server , that is the DB server ?

ps -ef|grep pmon
ps -ef|grep smon

Error & Solution

https://superuser.com/questions/1755254/virtualbox-guest-additions-update-got-error-missing-selinux-target-policy-file

Linux Commands Used

yum install oracle* --skip-broken 
yum -y install oracle-database-ee-19c
yum -y install oracle-database-ee-21c
# groupadd dba
# groupadd oinstall 
# useradd -g oinstall -G oinstall,dba oracle
#chown -R oracle:oinstall /u01

cd /u01/app/oracle/product/19.0.0.0/dbhome_1 --> Tool used to install Oracle Home / Database Home?
./runInstaller
cd /u01/app/oracle/product/19.0.0.0/dbhome_1/bin --> Tool used to create database?
./dbca

cat /u01/app/oraInventory/ContentsXML/inventory.xml
cat /etc/oraInst.loc
cat /etc/oratab
ps -ef|grep smon
ps -ef|grep pmon
su - oracle
. oraenv
env |grep ORA
sqlplus / as sysdba
passwd oracle
xhost +

Sorry GUYS its WINDOWS

• Run as Administrator

Error & Solution

• This is working
  https://stackoverflow.com/questions/78013258/unable-to-launch-jdeveloper-for-missing-msvcr100-dll

• Also try to install this,
  https://learn.microsoft.com/en-gb/cpp/windows/latest-supported-vc-redist?view=msvc-170#latest-microsoft-visual-c-redistributable-version

Notes :

1. If its windows installation then always install via ADMIN user.
2. If you want to set JAVA , do the below but most of the cases its not required.
3. Many issues with Java download , the JDK should have JRE also . Please check after installation of Java.
4. It may take some time please wait patiently.

Null Functions

• nvl
• nvl2
• nullif
• coalease

select 'NULL',null,'',' ' from dual;

• Only 2 & 3 output are the NULL values.

nvl

• if the value is NULL then print the value in the right hand side value.
• Only two arguments are passed. ( Input , what to replace )

select nvl('',0) from dual;
select nvl(NULL,0) , nvl(NULL , 'A') from dual;

• As the left side argument is NOT null then it prints the same value.

nvl2

• 3 arguments.
• ( 'infosys' , 2 , 'B') --> If the first argument is NULL then 3rd arguments will be printed or else second argument will be printed.

select nvl2('A',0,4) from dual;
select nvl2('',0,4) from dual;

nullif

• two arguments
• both should be NULL.

select nullif(1,7) , nullif('aa','aa') from dual;
select nullif(1,7) , nvl(nullif('aa','aa'),'A') from dual;

coalesce

• only one row.
• It prints first NOT NULL Value.
• It accepts n no of arguments, no limit.

select NULL, NULL , 30 , 40 , NULL from dual;
select coalesce(NULL, NULL , 30 , 40 , NULL) from dual;

Conversion Function

• to_char
• to_number
• to_date
• to_timestamp

to_char

select sysdate from dual ;
select to_char(sysdate,'DAY'),to_char(sysdate,'day'),to_char(sysdate,'Day'),to_char(sysdate,'D'),to_char(sysdate,'DD') from dual ;

--select sysdate from dual ;
select to_char(sysdate,'DAY'),to_char(sysdate,'day'),to_char(sysdate,'Day'),to_char(sysdate,'D'),to_char(sysdate,'DD') from dual ;
select to_char(sysdate,'MONTH'),to_char(sysdate,'MONTH'),to_char(sysdate,'Month'),to_char(sysdate,'MM') from dual ;


select to_char(sysdate,'YEAR'),to_char(sysdate,'Year'),to_char(sysdate,'yyyy'),to_char(sysdate,'yy') from dual ;

select to_char(sysdate,'IW'),to_char(sysdate,'W'),to_char(sysdate,'Q') from dual ;

To Number

--select '123' , 123 , to_number('123') from dual ; - how to check its converted to number ?
select '123' , 123 , reverse (to_number('123')) from dual ;

to_date

select '2024/12/24' , to_date('2024/12/24', 'yyyy/mm/dd') from dual ;
select to_date('24-DEC-24', 'MON-YYYY-DD') from dual ; --> Error
select to_date('DEC-2024-24', 'MON-YYYY-DD') from dual ;


to_timestamp

select TO_TIMESTAMP('DEC-2024-24 16:24:00', 'MON-YYYY-DD HH24:MI:SS') from dual ;

Note :

• Anything which is given within single quotes is always STRING.
• Joins , sub query , group functions , Analytics Functions & Set Operators are very import in SQL.
• what is Index , global temp table , Objects , views , sequence ( why we use ? )
• Reverse function will work only for STRING not for Numbers.
• what is the dataype of NULL ? Its NULL only.
• select sysdate , systimestamp from dual; --> Check this

• In SQL plus it will be different :

• RDBMS language.
• Its ANSI --> standard language for operational relational databases.
• Efficient , easy to learn and use.
• Functionally complete.

How SQL Works

• Data Sublanguage.
• works with logical level.
• common language for all relational databases.

SQL Statement types

• Data Manipulation Language DML

• Data Definition Language DDL

• Data Control Language DCL

• Transaction Control Language TCL

Oracle SQL Development Environments

• Below are the Tools used for SQL:
• Oracle SQL Developer --> debug , export , view reports ,etc
• SQL Plus --> Batch query tool.
• Oracle JDeveloper --> Multi-platform tool for web services.
• Oracle Application Express --> Like a web browser.

Oracle SQL Developer

Concept on RDMS ( Relational Database Management Systems )

• Relational Database --> Collection of relations or two-dimensional tables controlled by server processes.
• Before entering RDMS , What is Data Model ?
• Entity Relationship Model --> its business specifications.
• Then Modelling conventions --> its called Entity Relationship Modelling conventions.

• Relating Multiple Tables --> 1. Each row is unique by primary key 2. Logically a foreign key can be related with multiple tables.

HR Schema Model

SQL Statement

• Not case sensitive.
• one line or more lines.
• keywords can't be across lines or it can't be abbreviated.
• Clauses used in separate lines , what is clause here ? The main clauses are SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, INSERT, UPDATE, DELETE, and JOIN. [ TBD ]
• We can use indents so that one have better enhanced readability.

• dualis a table created by Oracle Database and its owned by SYS.
• dual as one column called DUMMY( data type VARCHAR (1)) and contains one row with value x.
• we can use to compute some expressions.

select * from dual;
select sysdate from dual;

Arithmetic Expression

• +
• "-"
• *
• /

• It reads from LEFT to RIGHT. L--->R

• You can see the difference after using Parenthesis.

select first_name,2*(salary+300), 2*salary+300 from hr.employees;

• NULL--> what is the value of it ? its like unavailable , unassigned , unknown or inapplicable.
• NULLis not as same as ZERO or BLANK SPACE.

Column Aliases

• Rename the column headings
• Column followed by AS.
• without AS also you can declare.

• In the column name , it becomes CAPS. To avoid this you can use "" for AS like below,

-- select first_name AS name , last_name as father_name from hr.employees;
select first_name AS "name" , last_name as "father_name" from hr.employees;
select first_name first  , last_name last  from hr.employees;

Concatenation Operators

• To link the column or character strings to other column.
• ||

select first_name||last_name AS "name"  from hr.employees;

Literal Character Strings

• Its a character , number or a date included in the select statement.
• Date & character should be enclosed with single quotation marks.
• Each character string is the output of each row.

select first_name || ' of ' || last_name OUTPUT from hr.employees;

Alternate Quote Operator

• we can use own quotation mark delimiter.
• specified with 'q'.
• To increase the readability.

select department_name || q'[ Departments Name is :]' || manager_id "Department and Manager" from hr.departments ;
select department_name || 'Departments Name is :' || manager_id "Department and Manager" from hr.departments ;

DISTINCT Keyword

• default select query will display all rows irrespective of duplicate rows.
• To eliminate the duplicate we can use DISTINCT keyword.

-- select department_id from hr.employees ;
select distinct department_id from hr.employees ;

DESCRIBE Command

• Its not a KEYWORD.
• To display table structure.
• Alternatively we can see in SQL developer in the column Tabs.

ORACLE QUESTIONS FOR CLEARING THE LEARNING EXAM

General Functions

• case
• decode

case

• its for condition.
• Relation and logical operators will be used when implementing CONDITIONS.
• It needs to have END word while finishing.

-- select first_name , salary , salary*10/100 from hr.employees; 10 % increment but we need only > 10000 ? how to do ?
-- select first_name , salary , CASE WHEN SALARY > 10000 THEN salary*10/100 END from hr.employees;
select first_name , salary , CASE WHEN SALARY > 10000 AND FIRST_NAME LIKE 'A%' THEN salary*10/100 END from hr.employees;

decode

• NO Relation or logical operators will be used when implementing CONDITIONS.
• decode ( input , p1 , p1 , p2 , p2 )
• Read two two.

• select decode (1,2,4,1,0) from dual;

என்னுடைய input வந்து 1 , now read two by two , the next inputs. ‘1’க்கு அடுத்தது ரெண்டு ரெண்டா படிங்க . இதுல input வந்து ஒன்று , என்னுடைய input ‘2’டா இருந்ததுன்னா 4 என்று print பண்ணனும் but our input is ‘1’.
என்னுடைய input ‘1’ இருந்ததுன்னா 0 என்று print பண்ணனும் but our input is ‘1’
So answer is 0.

• select decode (1,2,4,8,0,5) from dual; --> here 5 is else part.

• Now you will get data for all , because you have given else part in the query.

-- select first_name , salary , CASE WHEN SALARY > 10000 AND FIRST_NAME LIKE 'A%' THEN salary*10/100 END from hr.employees;
select first_name , salary , decode ( salary , 24000 , salary*10/100 , 17000 , salary*10/100 ) from hr.employees;

select first_name , salary , decode ( salary , 24000 , salary*10/100 , 17000 , salary*10/100 , salary*5/100 ) from hr.employees; --> Else is implemented.

select first_name , salary , CASE WHEN SALARY > 10000 THEN salary*10/100 
    WHEN SALARY < 10000 THEN salary*9/100 
    ELSE salary*5/100
    END AS BONUS from hr.employees;

-- select first_name , salary , COMMISSION_PCT , CASE WHEN COMMISSION_PCT IS NULL THEN 1.5 END from hr.employees;
select first_name , salary , CASE WHEN COMMISSION_PCT IS NULL THEN 1.5 ELSE COMMISSION_PCT END AS COMMISSION_PCT from hr.employees;

select FIRST_NAME , Hire_date , to_char (Hire_date,'day') from hr.employees;

• To_char used to convert.

Notes:

1. 19c and 23c --> Oracle
2. what is normalisation ?
3. Performance tuning ?
4. how to delete the duplicate ?
5. case is faster than decode , both are general functions .
6. D --> Day

Number Function

• Round
• Trunc
• Mod
• Ceil
• Floor
• Abs
• Sign

Round

• If there is number => 5 after the decimal point , it will round off to the next number otherwise it will remain the same.

select round(67.34) from dual;
select round(67.49) from dual;
select round(67.57) from dual;

Trunc

• It wont see any number after decimal.
• Trunc is used only with the NUMBERS.
• Lets do one more operation with trunc --> trunc(56.89898,2) means it will take the two digit after the decimal.

select trunc(67.7846434) from dual;
select trunc(67.10001) from dual;
select trunc(67.011234) from dual;

select sysdate from dual; --> it will display time also
select trunc(sysdate) from dual;

Mod

• It will give the remainder.

select mod(10,7) from dual;
select mod(61,10) from dual;
select mod(6,2) from dual;

Ceil

• It will go to the next number, whatever may the number.

select ceil(67.7846434) from dual;
select ceil(67.00001) from dual;
select ceil(67.011234) from dual;

Floor

• It will crop the decimal.
• Then what is the use of Trunc ? In trunc we can use, how many decimal can be included. Hope you remember.

select floor(67.7846434) from dual;
select floor(67.00001) from dual;
select floor(67.011234) from dual;

select floor(67.7846434),trunc(67.7846434,1) from dual;
select floor(67.00001),trunc(67.00001) from dual;
select floor(67.011234) , trunc(67.011234,0) from dual;

Abs

• Only + & - are allowed.
• It will remove the sign.
• There is one more function similar to this sign i.e SIGN, it will give 1 & -1.

select abs(67.7846434), abs(-67.7846434), abs(10-15), sign(-10) , sign(+67.89) from dual;

Date Function

• add_months
• months_between
• next_day
• last_day

add_months

• if you want to add MONTHS then this function is used.

select sysdate, sysdate+2 , sysdate+20 , sysdate-10 from dual; -- Days increases
select sysdate from dual;
select add_months(sysdate,1) from dual;
select add_months('01-DEC-2024',1) from dual;
select add_months(sysdate,-1) from dual; -- months increases

select * from hr.employees where hire_date < add_months(sysdate,-276) -- hired before 23 years , 276 is the month

months_between

• Displays the no of months , sometime it will give in negative number that means subtraction happens between months Jan is 1 and Dec is 12.
• Even the date will be considered.

-- select months_between((01-01-20024), (01-12-2024)) from dual;
-- select months_between((01-jan-2024), (01-dec-2024)) from dual;
select months_between(('01-JAN-2024'), ('01-DEC-2024')) , months_between(('01-DEC-2024'), ('01-jan-2024')) from dual;
select months_between(('17-JAN-2024'), ('01-DEC-2024')) , months_between(('01-DEC-2024'), ('25-jan-2024')) from dual;

• If you don't like the decimal , what you can use ? For sure I forgot . Can't remember very quickly . After reading my notes , found its "TRUNC or FLOOR".
• Find my age ? How ?

select months_between(('01-DEC-2024'), ('01-DEC-1989'))/12 from dual;
select months_between(('01-DEC-2024'), ('01-DEC-1989'))/12 AS AGE from dual;

next_day

• Also will order you can specify.
• Sunday --> 1 , Saturday --> 7

select sysdate from dual ;
select next_day(sysdate,'sunday') from dual;
select next_day(sysdate,1) from dual;

last_day

• To find the last day of the month.

select last_day(sysdate) from dual;
select last_day('01-feb-2024') from dual;

General

• greatest
• least
• distinct
• unique
• case
• decode
• concat
• ||

Greatest

select 12,67,98,90,100 from dual;
select greatest(12,67,98,90,100) , least (12,67,98,90,100) from dual;

Least

select 12,67,98,90,100 from dual;
select greatest(12,67,98,90,100) , least (12,67,98,90,100) from dual;

Distinct & Unique

• It will check the duplicate.
• Both will be same .

select unique FIRST_NAME , last_name from hr.employees;
select distinct FIRST_NAME , last_name from hr.employees;

Concat & ||

select concat(concat(FIRST_NAME,LAST_NAME),salary) from hr.employees;
select FIRST_NAME || LAST_NAME ||' '|| salary from hr.employees;

Note

1. In the Date function --> only months_between --> it returns number.

Single Row Function

• 7 types
• Case Manipulation Function : upper , lower & Initcap
• Character Manipulation : Instr , Substr , Length , Replace , Reverse , Translate , Trim , Ltrim , Rtrim , Lpad & Rpad.
• Number
• Date Function
• General Function
• Null
• Conversion

Initcap

• First letter in caps

> -- select * from HR.COUNTRIES;
> -- select * from HR.COUNTRIES where COUNTRY_NAME='BRAZIL'
> -- select * from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> -- select *, UPPER(COUNTRY_NAME) from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> -- select HR.COUNTRIES.*, UPPER(COUNTRY_NAME) from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> -- select HR.COUNTRIES.*, UPPER(COUNTRY_NAME) AS COUNTRY from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> select C.*, UPPER(COUNTRY_NAME) AS COUNTRY from HR.COUNTRIES C where COUNTRY_NAME=Initcap('BRAZIL')

Instr

• Tells the number of the Position.
• To find Letter position.
• Number as Output.
• Number wont change when to count from front or reverse.
• 1 -- > Front
• -1 --> Reverse
• Last one in the bracket is *OCCURRENCE *.
• instr(letter, which one you need , front or reverse , occurrence )

-- select 'APPLEE' FROM dual;
-- select INSTR('APPLEE','P',1,1) FROM dual;
select INSTR('APPLEE','P',1,2) FROM dual;
select INSTR('APPLEE','P',-1,2) FROM dual;

Substr

• Character as output.
• substr ( 'letter' , start , how many character/letter u need to be displayed )
• substr ( 'letter', 2 ) --> it will print all letters after 2.
• '-' reverse.

select substr('APPLEE',3,2) FROM dual;
select substr('APPLEE',-3,2) FROM dual;

Length

• To find the length of the character.
• For NULL there is no length.
• Space will be considered.

-- select length('brettlee') from dual;
-- select DEPARTMENT_NAME, length(DEPARTMENT_NAME) as no_of_letters from hr.DEPARTMENTS;
select '',length(''),length(null) from dual;
select '',length(' '),length(null) from dual;

Reverse

• Numbers,it won't accept for reverse function.

Replace

• replace the word.
• replace('word','exact_word','replace_with_this');
• If the second argument is not present in the first then replace won't work.
• word to word we need to use 'Replace'.
• character/letter to character/letter use 'Translator'.

-- select replace('hcl technology','technology',techno) from dual;
-- select replace('hcl technology','technology','techno') from dual;
-- select replace('hcl technology','soft','techno') from dual;
-- select replace('hcl technology','technology') from dual;
select translate('hcl','hl','ts') from dual;

Trim

• space can be trimmed.

Lpad

• to add.
• To add in both sides that's the task.

-- select lpad((rpad('infosys','9','&')),'11','&') from dual;
select rpad(lpad('INFOSYS',length('INFOSYS')+2,'*') ,length(lpad('INFOSYS',length('INFOSYS')+2,'**'))+2,'*') from dual;

Notes

• dual is a dummy table
• always single quotes for string.
• Data in the table is sensitive --> always remember.
• Query you can write in any format but data is sensitive.
• Can we put , after * ? Yes , you need to put the table name in the * then there wont be error.
• AS is the column name or alternate name.
• AS can be used only in the select column.
• AS can't be used for table.
• Alias name won't be saved in database.
• Alias = Alternate Name
• String = Group of letters

nmcli - NetworkManager Command Line Interface

• The nmcli utility can be used by both users and scripts for controlling NetworkManager.
• nmcli is a command-line tool which is used for controlling NetworkManager.
• nmcli command can also be used to display network device status, create, edit, activate/deactivate, and delete network connections.

List of commands

nmcli general status

nmcli connection

nmcli connection modify <name> i<tab>

• Once modified & then bring the nmcli UP.
• nmcli connection modify "name" ipv4.addresses 192.12.123.10/10 ipv4.gateway 192.12.123.254 ipv4.dns 192.12.123.254
• Now bring it UP.

nmcli connection up "<name>"

Notes

• Nameserver is also referred as DNS.
• Give the tab always so that option appears , its like what is next command or word.
• IP address , Netmask , Gateway & Nameserver.
• How to assign number after "/" in the IP address --> TBD

Virtual Machine

Physical servers , Virtual Machine & Containers.

Manual and Dynamic allocation can be done in Virtual Machine.
Type 1 : Its like Enterprise --> In Azure , OCI , etc this concept is used.
Type 2: Mostly which we are using eg., laptop.

• Containers is a concept.
• Docker uses containerisation concept.
• Docker Engine is like Kitchen .
• Docker File is like notes for recipe.
• Sambar recipe for all i.e Docker Image . One cup for each person i.e Docker Container.
• Pantry --> Docker Registry --> all images are available.
• Chef --> Docker Daemon --> It will be running continuously.
• Docker Network and Volumes --> Its Dinning Area.

docker        --> To check whether docker is installed or not.
docker images --> List all the images which are present.
docker pull hello-world ( if no version is provided then it will take the latest version only )

docker pull hello-world:nanoserver-1809

Now run the image so that a container will be created ,

docker run hello-world

Check the container list,

docker ps -a
( only ps will show the running container )

To delete the container,

docker rm -f <container-id>

Sites or URL

https://hub.docker.com/r/tensorflow/tensorflow/tags

Notes

1. Image ID will be same for all.
2. Container ID will be created each time which will be a new number.
3. While running any image without version , then it will take the latest one only.
4. If latest image is not there in the server then it will pull and then run the image. So use latest wisely.

Interview Questions

1. What is VM ? A software emulation of a physical computer.
2. What does hypervisor do in a virtualisation ? It allows VM to run on a physical host by managing their resources.
3. What is Docker primarily used for ? Containerising applications to ensure portability.
4. Advantage of using containers ? Containers are portable & can run across different environments.
5. which type of hypervisor runs directly on Physical hardware ? Type - 1.
6. Difference btw VM & Container ? Containers share the host OS , while VM have their won OS.

Need to know

VMware ESXi
vCenter - Server Management Software - vCenter
VMware Workstation

Application Programming Interface

• Its like server/Waiter in a HOTEL refers to API.
• Interface--> Allow control of interaction with obstruction --> API is for Programmers.

• Client & Server --> Can be any language.
• All web application will have API.

1. SOAP-API --> Simple Object Access Protocol --> Function/Method needs to be written so that one can call it . Its like a WRAPPER.
2. REST-API --> REPRESENTATIONAL STATE TRANSFER --> If you know the concept of REST then everyone will write the same.

• CURL ( cURL ) --> Command line CLIENT for testing and developing API.
• Postman ( its a Graphical version based ) --> its a company now. 1 year we can use it for FREE.

Continue with REST

• Method ( GET , POST , PUT/PATCH , DELETE )
• Address / Endpoint (URL)
• Path

• Query/Search Parameters --> Starts with ? followed by KEY & VALUE.
• Authorization
• Header & Body
• Request Body can be sent via JSON Data types, which is default.
• Response --> 200 OK or 404 Not found or 201 created along with Headers and body.
• Layered --> It will have all configured LB & everything.

Why REST is popular ?

1. Simple & Standardised --> Everyone will use the same method to use.
2. Scalable & Stateles. Eg., It wont store anything. Each time it request as fresh request.
3. High Performance & Caching.

CRUD

Continue with REST

• https://api.github.com/users/
• users --> it will be mostly plural , followed by SINGULAR --> username.
• https://jsonplaceholder.typicode.com/todos

• https://jsonplaceholder.typicode.com/todos/1

import requests
api_url = "https://jsonplaceholder.typicode.com/todos/1"
response = requests.get(api_url)
print(response)
response.json()
print(response.json())

All Request

import requests
api_url = "https://jsonplaceholder.typicode.com/todos/"
response = requests.get(api_url)
print(response)
response.json()
print(response.json())

Print in proper format or PrettyPrint

Post method

• For this API , the best example is BANKING with website & Mobile APP. ( Why ? In internet banking you will pass all values and its the DB then gets the response which is obvious BUT think of the APP in your mobile which is lite weight app which will use API calls to DB and does the same operations )

• Web Scraping --> you can get only DATA but you can't update or modify. Each website we need to write different web scraping code. EACH WEBSITE WILL HAVE DIFFERENT CONTROL.

• ? & key = value & key = value

• Now with KEY ,

Important Links

• https://realpython.com/api-integration-in-python/
• https://jsonplaceholder.typicode.com/ --> API playground.
• Free open API for weather --> https://open-meteo.com/ --> curl "https://api.open-meteo.com/v1/forecast?latitude=52.52&longitude=13.41&current=temperature_2m,wind_speed_10m&hourly=temperature_2m,relative_humidity_2m,wind_speed_10m"
• https://tamilpesu.us/en/number/ --> https://tamilpesu.us/en/number/
• https://www.slideshare.net/slideshow/an-introduction-to-rest-api/76492672
• https://speakerdeck.com/cli4d/introduction-to-rest-apis
• https://www.slideshare.net/slideshow/what-is-rest-api-rest-api-concepts-and-examples-edureka/174179563
• https://jiminbyun.medium.com/building-python-scripts-for-rest-api-calls-a-practical-guide-2-9-3ac9ca1d701a
• https://github.com/vinta/awesome-python ( Important Site )

Important Notes

• ''' ... ''' --> Commenting multiple lines.
• pprint --> its like making JSON beautification , for this we need to use " import pprint ".
• curl -X GET https://jsonplaceholder.typicode.com/todos/1
• curl -X POST -d {"userID":101} https://jsonplaceholder.typicode.com/posts
• What is BROWSER automation for sites ?
• Try this and get the output --> https://www.instructables.com/Get-Weather-Data-Using-Python-and-Openweather-API/
• All social media platform expose their service with API.
• Try this also --> http://tamilpesu.us/number/34?type=IN --> website --> https://tamilpesu.us/en/number/
• POSTMAN --> good platform with UI, mostly used.
• https://en.wikipedia.org/api/rest_v1/ --> wikiepedia rest api
• md --> Markdown
• Super Market Example :-) You need to search each and everything, we wont get it immediately. https://github.com/vinta/awesome-python --> this as many codes , so search one by one.

Reference

https://www.youtube.com/watch?v=HW9E4TYoYXU&list=PLiutOxBS1Mizte0ehfMrRKHSIQcCImwHL&index=38

** ACCEPT THE SLOWNESS **

OUTPUT REDIRECTION

Any command in Linux will have 3 data streams ,

1. STDIN --> 0
2. STDOUT --> 1
3. STDERR --> 2

These numbers are called DESCRIPTOR.

>--> Redirection to a file.
>> --> Upend to the last line.
2> --> This will get the error.
2>> --> Upend the error.
&> --> Both output & error will be stored in the file.

PIPE SYMBOL or PIPE REDIRECTION

• First command output is the input to the second command.

• cat file1 | less --> Its like Booklet ( so ENTER ENTER --> it will turn like a page , Down arrow and Up arrow )

• cat file1 | more

PATTERN MATCHING or CLOBBING

• * ? [] ! {}
• touch file{a..z}
• touch file{1..100}
• touch file{11,12,17}
• ls -l file[abcd] --> exact match pattern
• ls -l !(file) --> list files which is not having any name with file. Its just exclude.
• To remove all files in one command rm -r file*
• ls -l ??? --> Single character matching
• ls -l ?file --> Character matching
• ls -l file?
• ls -l /var/log/???

VI

• sudo yum install vim
• :set number
• dd --> delete
• copy & past --> yy & c
• cut & past --> dd & c
• 3 & dd --> it will delete 3 lines from the cursor
• shift + g --> Last line
• :1 --> it will go to the first line

• Patching is in-place NOT Upgrade.
• Oracle release Quarterly Patches

• Base software's( GI Software's & Oracle Software's ) can be downloaded in Oracle Site.
• Patches can be downloaded from Support Site and also it needs a license.
• Patches are applied on top of GI Home & Oracle Home.
• What these Patches contains ?

• Where to check what patches are applied ?
  /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch lspatches
  29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
  29517242;Database Release Update : 19.3.0.0.190416 (29517242)
  OPatch succeeded.

• ASM Home

/u01/app/19.0.0.0/grid/OPatch/opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517247;ACFS RELEASE UPDATE 19.3.0.0.0 (29517247)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)
29401763;TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)

• DB Home

/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)

• 19.3.0.0.190416 --> 190416 --> 2019 - April - Date.
• Second digit is RU. i.e 19.3
• RU will be increasing 19.2,19.4 etc
• Here : 19.3 is the base release. --> GI Home and ASM Home.
• These are cumulative patches --> all patches are not to be applied one by one , you can jump to any patches.

Overview plan

How to download & Patch details ?

• Search like below, Critical Patch Update (CPU) Program Jan/Apr/Jul/Oct 2023 Patch Availability Document (DB-only)
• Section 3 which is for Oracle Database.
• 3.1.7 --> 3.1.7.3 which is 19c
• GI Patch will contain DB patch also.
• Now go to the READ ME file and check the table 1-2 which will say how many sub patches need to be applied.

OPatch Tool

• We need to patch 3 home ,
• GI Home
• DB Home

• Database Home

• All these home will be patched via OPatch Tool.

Best Practice

• SDLC ( DEV --> UAT --> PROD )
• Don't rollback of any previous patches.
• Check the conflict check for all the patches using below command, ( repeat for all 5 patches or sub patches )

/u01/app/19.0.0.0/grid/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/19.17/34416665/33575402 -oh /u01/app/19.0.0.0/grid

/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/19.17/34416665/34419443 -oh /u01/app/oracle/product/19.0.0.0/dbhome_1

OPatch Patch

• 6880880 version.
• Upgrade can be done by downloading.

Steps to apply patch

DB Home patching steps

1) Stop DB Home as root user
2) grant permission on patch directory as root user
3) As oracle user do prechks 
Do OPatch version and current lspatches checks
Conflict pre checks:    
System space prechk:
4) Apply Patch:
5) Post verification
6) Start ORACLE_HOME as root user

GI Home patching steps

• Stop the GI home as root user, which means its a HAS --> Standalone.

1) Stop GI Home as root user
2) Unlock the GI Home as root user
3) grant permission patch directory as root user
4) As grid/oracle user do prechks 
Do OPatch version and current lspatches checks
Conflict pre checks:    
System space prechk:
5) Apply Patch:
6) Post verification
7) Lock and start GI 

Prechecks

- backup GI Home --> tar -cvzf grid.tar.gz grid ( cd /u01/app/19.0.0.0/ )
- backup Oracle Home --> tar -cvzf dbhome_1.tar.gz dbhome_1 ( cd /u01/app/oracle/product/19.0.0.0 )

• System space check

/u01/app/19.0.0.0/grid/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/19.17/34416665/33575402 -oh /u01/app/19.0.0.0/grid

/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/19.17/34416665/34419443 -oh /u01/app/oracle/product/19.0.0.0/dbhome_1

• df -h /u01/app/oracle/product/19.0.0.0/dbhome_1/

Patching

/u01/app/19.0.0.0/grid/OPatch/opatch apply -oh /u01/app/19.0.0.0/grid -local -silent /u01/patches/19.17/34416665/34419443

/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch apply -oh /u01/app/oracle/product/19.0.0.0/dbhome_1 -local -silent /u01/patches/19.17/34416665/34419443

• silent --> default is YES , don't wait for command.

Important Notes

• Don't download patches until you have license , it may be fine as they are unauthorised to download.
• List of patches terms used in Oracle :

https://docs.oracle.com/cd/E24628_01/doc.121/e39376/glossary.htm#BGBCGDDF

Users

1. SYS--> DBA Role + SYSDBA Role ( Startup / Maintenance activity ) --> Super Master User
2. SYSTEM--> DBA Role --> Master users
3. 30 to 35 users will be created in default. desc dba_users; Mostly all will be locked and expired.

• Any users created , DBA will assign System Level Privileges & Object Level Privileges.
• what are all the System Level Privileges ( activities performed at DB side ) ? CREATE SESSION , CREATE TABLE.
• When a user tries to read the data of other user , then object level privilege comes into picture.
• Object Level Privileges --> enables users to access and change data's in the object.

• These System level privileges comes along with ADMIN option.
• These Object level privileges comes along with GRANT option.

grant CREATE SESSION to user1 with ADMIN OPTION; --> which very risky. Because this user can give access to any user.

grant select on USER2.T2 to user1 with GRANT option; --> which very risky. Because this user can give select access to any user.

• If we want to revoke the admin option (System level privileges) for the user which you have given then it will revoke only for that user , it won't revoke for other users. If the user as given access to other friends . So Manually you need to check in the audit and then manually you need to remove the access.

• If we want to revoke the Grant option (Object level privileges) , then it will revoke for that user and also for other user which is granted.

Roles

• Create a role and assign the privileges to the role. Why ? If new users comes in and DBA can't provide privilege for each users who is coming.

create roles ROLE1;
grant select on HR.Employees to ROLE1;
grant select on HR.Regions to ROLE1;
grant select on HR.Locations to ROLE1;
grant ROLE1 to user1;
grant ROLE1 to user2; --> these are new users joining
grant ROLE1 to user3; --> these are new users joining

one more on this ROLE

grant select on HR.JOBS to user1;
.
.
grant select on HR.JOBS to user100;

rather than the above one , we can provide to roles because roles are already assigned to user1,user2,user3

grant select on HR.JOBS to ROLE1;

Profiles & Quotas

• Quotas --> the space usage

• profiles --> we can further more restrictions like the CPU usage a user can use & how much logical reads & password complexity.

desc dba_profiles;

• 20 to 30 resources will be listed down in each profile.

• any user you create "DEFAULT" profile will be associated with the user.

desc dba_users;

• what are the resources ? Each resources you can specify.

create custom profile for users

• Like for DEVELOPERS, DBA's , app users .

• Grant the profile to user1.

Alter user user1 profile dummy;
select username, acccount_status, profile from dba_users where username = 'user1';

• You can increase the complexity for creating the user slowly after getting the knowledge like below :

• Even you can modify using alter user too ,

alter user user1 profile dummy;
alter user user1 quota 2g on users;
alter user user1 default tablespace test1;

Notes

1. sqlplus / as sysdba or sqlplus sys/password as sysdba
2. sqlplus system/password ( no need of any role )

Above both the users are used to perform DB and maintenance activities .

• DBSNMP user mostly used for OEM.
• "---------------------" this is 100 characters , just restrict to 40 letters/characters like " col PROFILE for a40; --> for is format.

Command

show user
set pages 1000 lines 1000
col username for a20 --> for is format
/ --> last command will be executed
grant CREATE SESSION to user1;
grant CREATE TABLE to user1;
create table T1 (SLNO number(10))
insert into T1 value (1);
alter user user1 quota unlimited on USERS; & then commit ;--> UNLIMITED space is allocated.
grant select on USER2.T2 to user1;
grant insert on USER2.T2 to user1;
grant delete on USER2.T2 to user1;

select * FROM DBA_SYS_PRIVS where grantee in ('USER1');
select * FROM DBA_TAB_PRIVS where grantee in ('USER1');
select * FROM DBA_ROLE_PRIVS where grantee in ('USER1');

sqlplus sys/password@service_name as sysdba; --> remote authentication
sqlplus / as sysdba; --> OS authentication

grant sysdba to user1;
grant dba to user1; --> dba is role

• DBA is a role --> system level , object level , roles.

Issues

• Insufficient privilege
• user lacks CREATE SESSION privilege-
• Above two errors are related to missing " System Level Privileges " for the users.
• no insert privilege on tablespace --> assign some quota so that we can assign some values to it.
• Quota exceeds limit.
• Account is locked & timed

Questions

1. What are all the privileges user can have ? System level , Object level & roles.
2. List all the privileges owned by user1 ?
3. List all the privileges owned by user1 & grant some privilege to user2?
4. Create user5 & assign all privilege of user1 ?
5. duplicate user1 as user5 with all privileges ?
6. what is composite limit in the profile ?

• Listener --> server side configuration file --> listener.ora
• TNSnames --> Client side configuration file --> tnsnames.ora

listener.ora

• it contains port.
• on which port the database is listening.
• protocol --> TCP ( on which protocol its running )
• hostname or IP address
• All these can assigned to Listener name, so that we can start and stop the listener ( Alias Name )
• location --> TNS admin ? --> network configuration location.
• This network location will be in $ORACLE_HOME/network/admin
• Listener Base --> Listener related logs will go here, its like a base directory.
• lsnrctl --> listener Control

lsnrctl start name_of_the_listener
lsnrctl status name_of_the_listener

• Change the listener port , now the scenario will be like --> Listener is running in 1522 but the LREG takes the details of DB and then search for 1521 port. Even if you wait for 60 secs it wont happen . So your Listener will never get the update of DB details. " The Listener supports no service " --> That means Listener doesn't have any DB details.
• show parameter local_listener;
• alter system set local_listener = ' ( Address = ... ) ' ;
• Now my LREG is aware where my listener is running.

Static Registration

• Static registration with SID OR Service name.
• Forcing the listener to register the DB details ( don't worry about the status of the DB , but just register )
• UNKNOWN is the static registration.
• What is the purpose of registering with Static registration ? --> Its used in Data Guard Build for online mode is one of the use case.

tnsnames.ora

• Its a client side configuration file.
• The client may connect from any application.
• Inside the file we need : Port , protocol , hostname or IP address , service name ( name of the DB will be equal to service name , most of the time )
• All these 4 details are required.
• The client will connect with these parameters along with username and password. -Each time all these details to be passed , to avoid this , Oracle came up with ALIAS NAME in the tnsnames.ora file which is inside the network admin file.

sqlnet.ora

• Both server and client side file.
• It can be used for many purposes.
• If there is any miss-match in version at any side , client or server side , at that time this HERO will come into picture. OR at TDE / Wallet usage.
• In simple terms , its used in : ( search in internet "sqlnet.ora" )

lower client version
wallet location
AD/LDAP
Connect Timeout ( Client to server connectivity )
40 or > 40 parameters can be defined.

NOTE

1. Oracle_home will be more or less like --> /u01/app/oracle/product/19.0.0.1/dbhome_1
2. Listener supports no service.
3. For every 60 secs these pmon goes to listener and registers its details. what details ? DB details . This is as per 11g.
4. LREG in 12c --> Listener register , every 60 secs it registers the DB details with the LISTENER. This registration happens only when the DB is running with 1521 PORT.
5. Listener is a dynamic parameter.
6. What is UNKNOWN ? when we check the listener status ? [TBD] --> Ready or Unknown. READY --> Dynamic Registration , UNKNOWN --> Static Registration --> we have the DB details in the listener configuration file and forcing the listener to register the DB details and don't worry about the DB status.

Commands

su - oracle
. oraenv
env | grep ORA
ps -ef|grep tns
ps -ef|grep lreg
sqlplus / as sysdba
telnet host 1521
netstats -anp | grep 1521
ps -ef|grep smon
startup
sqlplus user/password
sqlplus user/password@"(DESCRIPTION ...)
select instance_name from v$instance;
show user;
sqlplus user/password@alias_name;

Issues

1. Firewall Issues.
2. Listener may be wrong.
3. Port may be wrong.
4. Listener may be down.

Questions

1. Can we setup a secured listener ? This can be done at the Protocol parameter.
2. what is the difference between SID or service name ?
3. TNS_ADMIN --> location of the network configuration files.
4. What are the network configuration files ?

listener.ora
tnsnames.ora
sqlnet.ora

1. Client software's like Oracle Client download , netca , netmgr , OEM , JDBC , ODBC , toad app , sql developer , linux , windows etc

1. Backup & Recovery software's --> rubrik , Tivoli Storage Manager database - IBM.

Reference

https://docs.oracle.com/en/database/oracle/oracle-database/19/netrf/parameters-for-the-sqlnet.ora.html

https://www.oracle.com/in/database/technologies/instant-client/downloads.html

• Format in next line

• Do you need "quotation marks" when printing numbers? --> No

• Multiplication

• How do you print the value of a variable name which is set to “Syed Jafer” or Your name?

• How do you print the variables name, age, and city with labels “Name:”, “Age:”, and “City:”?

• How do you concatenate and print the strings greeting (“Hello”) and target (“world”) with a space between them?

• How do you print three lines of text with the strings “Line1”, “Line2”, and “Line3” on separate lines?

• How do you print the string He said, "Hello, world!" including the double quotes?

• How do you print the string C:\Users\Name without escaping the backslashes?

• How do you print the string “Hello” followed by a space, and then print “world!” on the same line?

• How do you print the value of a boolean variable is_active which is set to True?

TBD

• How do you print the string “Hello ” three times in a row?

• How do you print the sentence The temperature is 22.5 degrees Celsius. using the variable temperature?

• How do you print the value of pi (3.14159) rounded to two decimal places in the format The value of pi is approximately 3.14?

• How do you print the words “left” and “right” with “left” left-aligned and “right” right-aligned within a width of 10 characters each?

with open ( " file.txt " , "w" ) as f;
print ( file is to open " , file=f)

• prints only blank line.
• to determine joints element with as " it will join ".