12cR2 New Feature -sql prompt "history"

12cR2 has really great features to use in day to day life for dba. Here we will be discussing one such feature i.e. SQL prompt "history". Many dba's use history command on UNIX prompt to analyze the things or events, Before 12c, this option was not there to see history of sql's you have executed but from 12cR2 onward this is very much possible.

Lets' try this thing out, below is the demonstration 

Check if history parameter is ON/OFF

    SQL> show history; 

    history is OFF

Set history ON

    SQL> set history ON;

    SQL> show history;

    history is ON and set to "100"

Now execute few queries

    SQL> select name,open_mode from v$database;

    NAME      OPEN_MODE

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

    TECHCDB   READ WRITE

    SQL> show con_id

    CON_ID

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

    1

    SQL> select count(*) from dba_db_links;

      COUNT(*)

    ----------

             2

    SQL> select sysdate from dual;

    SYSDATE

    ---------

    28-JUL-20

 

Now check the history, you will be able see all the sql's executed.

    SQL> history

      1  show history;

      2  select name,open_mode from v$database;

      3  show con_id

      4  select count(*) from dba_db_links;

      5  select sysdate from dual;

    SQL>

set history OFF

    SQL> set history off

    SQL>

    SQL> history

    SP2-1650: History is off, use "SET HIST[ORY] ON" to enable History.

this is use of "history" command. Suppose if you forget to turn off history after your work, don't worry it will be only available for that session, it will be automatically turned off.

Read More

Physical location of datafiles in PostgreSQL

In this article, we will discuss finding the physical location of data files for a particular database.

In the logical structure, we can see different databases and tables or any other objects under it but as DBA we should also be aware of how to find out the location of data files associated with a database or a table.

In the article table creation in Postgres database we learned to create tables now we use the same tables here to perform a demonstration. Assume we have the below details

Database name- tech_db

Table name - employee

tech_db=# \c tech_db

You are now connected to database "tech_db" as user "postgres".

tech_db=# \dt

          List of relations

 Schema |   Name   | Type  |  Owner

--------+----------+-------+----------

 public | employee | table | postgres

(1 row)

tech_db=# select * from employee;

 emp_id | emp_name | age

--------+----------+-----

    123 | sam      |  25

    124 | John     |  24

(2 rows)

as of now we do not physical location of this database or table, now we will try to find out the same

Read More : Database Creation in PostgreSQL

Data Directory

First of all check data directory using show command

tech_db=# show data_directory;

  data_directory

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

 E:/postgres/data

(1 row)

Above we got data directory, as by default folders for each database gets created under data_directory/base location

as we see folder as created with some random numbers which is called as OID , value of OID can be find using below query.

tech_db=# select datname, oid from pg_database where datname='tech_db';

 datname |  oid

---------+-------

 tech_db | 16393

(1 row) 

we can see value of oid for database tech_db is 16393 and in above screenshot we can find the same folder under base directory. Under it you can find number of files and now lets find the file related to our table employee

tech_db=# select relname,oid, relfilenode from pg_class where relname='employee';

 relname  |  oid  | relfilenode

----------+-------+-------------

 employee | 16394 |       16394

(1 row)

pick up the relfilenode from above output, you will be able to see same file under folder 16393

Read More

Basic Operations on MySQL Users

In this article we are going to discuss about different operations on mysql user like lock, unlock, grant, revoke etc.

• Lock-unlock
• Expire-unexpired
• Check privileges
• Revoke privileges
• Change password
• Rename user (single user, multiple user)
• Drop user

User list can be fetched using query mentioned below in the database

select user,host,authentication_string,password_expired,account_locked from user where user like 'test%';

We can all accounts are unlocked and password not expired. We will perform operations one by one

LOCK and UNLOCK

Let’s lock and unlock test@%
Locking the account
    alter user ‘test’@’%’ account lock;
Checking the status
    select user,host,authentication_string,password_expired,account_locked from user where user like 'test%';

Unlocking it again
    alter user ‘test’@’%’ account unlock;

This will affect only test@% not test@localhost, both are different users.

EXPIRE

Let’s expire password for 'test'@'localhost' and revert it
alter user ‘test’@’localhost’ password expire;

Verification
    select user,host,authentication_string,password_expired,account_locked from user where user like 'test%';

Password reset using values
    alter user ‘test’@’localhost’ identified with ‘mysq_native_password’ as   ‘*5BA7FD292EB16719F76792ECAF589CE4B62CCC94’;

Verifying status again
    select user,host,authentication_string,password_expired,account_locked from user where user like 'test%';

CHECK PRIVILEGES
show grants for ‘test2’@’locahost’;


REVOKE PRIVILEGES
In below example grants of test2@localhost has been revoked
show grants for ‘test2’@’localhost’;
revoke SELECT, INSERT, DELETE CREATE, DROP, RELOAD, ALETER ON *.* from ‘test2’@’localhost’;
flush privileges;
show grants for ‘test2’@’localhost’;


CHANGING PASSWORD
In below example password of test3@% has been altered.
select user, host, authentication_string, password_expired, account_locked from user where user like 'test%';
alter user ‘test3’@’%’ identified by ‘Hudrt#12’;
select user, host, authentication_string, password_expired, account_locked from user where user like 'test%';


RENAME USER
show grants for ‘test1’@’localhost’;

Single user 

In mysql we can rename the user without dropping it but modification needs to be carried wherever it is not getting used, grants will not get altered

Single user- in below example ‘test1’@’localhost’ user has been renamed with ‘xyz1’@’localhost’

rename user ‘test1’@’localhost’ to ‘xyz1’@’localhost’;
show grants for ‘xyz1’@’localhost’;
select user, host, authentication_string, password_expired, account_locked from user where user like xyz1%';

Multiple user
select user, host, authentication_string, password_expired, account_locked from user where user like 'test%';
rename user ‘test’@’localhost’ to ‘abc’@’%’,
            ‘test’@’%’ to ‘abc’@’localhost’;
select user, host, authentication_string, password_expired, account_locked from user where user like 'test%';
select user, host, authentication_string, password_expired, account_locked from user where user like 'abc%';


DROP USER
In below example we are dropped abc user
select user,host from user where user=’abc’;
drop user ‘abc’@’localhost’;
select user,host from user where user=’abc’;
drop user  ‘abc’@’%’;
select user,host from user where user=’abc’;

Read More

Oracle : STATISTICS_LEVEL parameter

In Oracle as we gather and view statistics though different views or tables can be controlled by instance level parameter STATISTICS_LEVEL
This particular parameter has below three possible settings

• BASIC
• TYPICAL
• ALL

The parameter controls automatic gathering of statistics at two levels 

Instance statistics : statistics accumulate withing instance regarding activity

Object statistics : stats accumulated on objects within database

The instance statistics accumulated in memory and flushed to AWR ( automatic workload repository) by MMON (manageability monitor) background process

Object statistics are gathered by DMBS_STATS procedure calls

Now we will discuss about parameter setting 

TYPICAL : The default setting is TYPICAL, this setting will gather all statistics needed by self management and tuning capabilities of the database and will also enable automatic object analysis task that runs daily in maintenance window

BASIC: The BASIC setting disable  AWR statistics and disable daily analysis

ALL : ALL setting gathers all possible statistics, these include operating system statistics and very detailed statistics on SQL statement execution. This shortcoming with this method is it will impact performance adversely.

Suggestion : this parameter should always be kept default and of ALL setting is required should be kept for short term.

This parameter can be altered runtime using simple alter system command

alter system set STATISTICS_LEVEL= BASIC;

alter system set STATISTICS_LEVEL= TYPICAL;

alter system set STATISTICS_LEVEL= ALL;

and same can be viewed using

show parameter  STATISTICS_LEVEL

Read More

Transferring file from one server to another server [FTP, SFTP, SCP etc]

There are number of ways to transfer file from one server to another server, below are the few methods to to achieve the same

1. ftp
2. sftp
3. scp
4. windows software (Filehippo, WinScp , FileZilla etc)

We will discuss these in details

1. FTP transfer

File Transfer Protocols basically is set of rules on computer network to communicate with one another. This FTP tool or utility can be used to transfer files from one server to another server.

Below are the commands to use ftp

$ftp
User : 
Password :
ftp> bin  -- to transfer in binary format
ftp> cd
ftp> put/get -- put for sending from one server to another and get is vice versa
ftp>bye

2. SFTP transfer

As name suggest SFTP is secure file transfer protocol, it uses full security and authentication functionality of SSH. SFTP is new utility compared to FTP and recommended to use instead of FTP
This utility can be used using below commands
$sftp @
password : ***
sftp>cd
sftp> put/get -- put for sending from one server to another and get is vice versa
sftp> bye

3.SCP 

SCP is used for securely copying files from one server system to another server system. 
Below is syntax for the same

scp [OPTION] [user@]src_server:] file1 [user@]target_server:]file2
where 
[user@]src_server:] file1 -- source file
[user@]target_server:]file2 -- destination file

for example
scp text_file.txt  @:/

4. Windows software

There are number of freely available software on internet, choose your favourite one and transfer files using simple GUI
Below are few I came across

• FileZilla
• Filehippo
• WinScp
• FireFTP

Read More

MongoDB : Capped Collections

There are two types of collections normal collection and capped collection

• In comparison to the normal collection, capped collections are created in advance and are fixed in size 
• Normal collections are created dynamically and automatically grow in size to fit extra data
• These capped collections are designed to consume less space and are rotating that means once allocated space is full, it will start writing from older documents again.
• Below operations are not allowed on a capped collection

• Documents can not be removed
• Updates that make documents to grow in size are not allowed 

Capped collections can not be sharded

Creating Capped collection

As discussed above, capped collection needs to be created explicitly, we will discuss commands for the same

> db.createCollection("my_cap_col1",

... {"capped" : true, "size":100000});

{ "ok" : 1 }

> 

above command creates capped collection with below details
capped collection name : my_cap_col1
fixed size : 100000 byes

same way capped collection can be created with limit on number of documents
for example

> db.createCollection("my_cap_col2",

... {"capped":true, "size" : 100000, "max" : 100});

{ "ok" : 1 }

> show collections;

my_cap_col1

my_cap_col2

Once capped collections are created, it can not be modified, it must be dropped and recreated with desired parameters

Converting normal collection to capped collection

> db.runCommand({"convertToCapped": "techno_col",size:10000});

{ "ok" : 1 }

> 

There is no way to convert capped collection to normal collection

Read More

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

In previous articles, we have learned about user creation and grants in MySQL in detail, but there are a few privileges called global privileges that are meant to be granted to DBA accounts only
for example
PROCESS privileges is a global privilege. 
we will try to grant it to user tech_user on author database

mysql>grant process on author.* to tech_user;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Here we see it failed with error 1221 (HY000).

In order to resolve this error, we need to reconsider this grant statement and its usage.
we must know global privileges can not be granted to the individual database, instead, it should be on all databases. i.e. use of *.* instead of an author.*

so the statement should look like this

grant process on *.* to tech_user;

Below are some of the well know global privileges

• FILE
• PROCESS
• REPLICATION CLIENT
• REPLICATION SLAVE
• SUPER

In the below way, grants can be executed using the root account without error. 

mysql> grant file on *.* to tech_user;

Query OK, 0 rows affected (0.01sec)

mysql>flush privileges;

Query OK, 0 rows affected (0.01sec)

Read More

MySQL: pid-file (process id file)

In previous articles have gone through options available with mysql_safe, one of the option is pid file i.e. process identifier file

• The server writes its process id into pid file when mysql services are started and removes file when stopped.
• The pid file is the means by which a server allows itself to be found by other processes.

Location
default location of  pid file is data directory or it can be specified by specific variable 
pid-file=file_name
generally pid file is given as hostname.pid

Content
The pid file content the process identification number of mysqld process, you can see content by using below command
cat

and also see the process id  of mysql daemon process using 
ps -ef|grep mysqld

you can see content of pid file and this process id identified using ps -ef command are same.

Read More

Aggregation Function in MySQL

There are number of functions available in MySQL library, in this particular article we will  discuss aggregation function
Aggregation function s are generally used with SELECT statements containing GROUP BY clauses, however these may be used without group by clauses as well.
Below table describes aggregation functions available in MySQL (including MySQL 8.0 version)

Name	Description
AVG()	Returns average value of argument
BIT_AND()	Return bitwise AND
BIT_OR()	Return bitwise OR
BIT_XOR()	Return bitwise XOR
COUNT()	Return a count of rows
COUNT(DISTINCT)	Return a count of number of different values
GROUP_CONCAT()	Returns concatenated string
JSON_ARRAYAGG()	Return result set as single array
JSON_OBJECTAGG()	Return result set as a single object
MAX()	Returns maximum value
MIN()	Returns minimum value
STD(), STDDEV(), STDDEV_POP()	Returns the population standard  deviation
STDDEV_SAMP()	Returns the sample standard deviation
SUM()	Return the summation
VAR_POP()	Return population standard variance
VAR_SAMP()	Returns sample variance
VARIANCE()	Returns Population standard variance

In below section, we will discuss few of these with examples, 
first we will create table agg_tut for use with few records in it.

CREATE TABLE agg_tut (
emp_id int NOT NULL,
emp_name varchar(255),
salary double,
itax double,
age int
);

insert into agg_tut values ('101','John','12500','0','25');
insert into agg_tut values ('102','Smith','52500','2500','45');
insert into agg_tut values ('103','Jinh','22500','2200','32');
insert into agg_tut values ('104','Mist','15500','1000','23');
insert into agg_tut values ('105','Suki','13000','1200','28');
insert into agg_tut values ('108','Rock','33000','1800','30');

count ()

select count of all rows present in table

select count(*) from agg_tut;

+----------+

| count(*) |

+----------+

| 6        |

+----------+

min() and max()

Apply min() and max() on emp_id column first

select min(emp_id) from agg_tut;

+-------------+

| min(emp_id) |

+-------------+

| 101         |

+-------------+

select max(emp_id) from agg_tut;

+-------------+

| max(emp_id) |

+-------------+

| 108         |

+-------------+

Now apply min(), min() on salary column to rectify how much company is paying

select min(salary) from agg_tut;

+-------------+

| min(salary) |

+-------------+

| 12500       |

+-------------+

select max(salary) from agg_tut;

+-------------+

| max(salary) |

+-------------+

| 52500       |

+-------------+

Now calculate the average using avg() salary of employee in organization

select avg(salary) from agg_tut;

+-------------------------+

| avg(salary)             |

+-------------------------+

| 24833.33333333332       |

+-------------------------+

Now calculate the total salary of employee using sum(), so organization will have estimation towards employee cost

select sum(salary) from agg_tut;

+--------------+

| sum(salary)  |

+--------------+

| 149000       |

+--------------+

JSON_OBJECTAGG

Consider we have below data in employee table

select * from agg_tut;

+--------+-----------+--------+-------+------+

| emp_id | emp_name  | salary |itax   |age   |

+--------+-----------+--------+-------+------+

| 101    | John      |12500   |0      |25    |

| 102    | Smith     |52500   |2500   |45    |

| 103    | Jinh      |22500   |2200   |32    |

| 104    | Mist      |15500   |1000   |23    |

| 105    | Suki      |13000   |1200   |28    |

| 108    | Rock      |33000   |1800   |30    |

+--------+-----------+--------+-------+------+

as we know JSON is key value pair language , consider emp_id a as key and other columns such as emp_name and salary are value

Select emp_id,JSON_OBJECTAGG(emp_name,salary) from agg_tut group by emp_id;

+--------+---------------------+

| emp_id | JSON_OBJECTAGG      |

+--------+---------------------+

| 101    | {“John” : 2500.0    |

| 102    | {“Smith” : 52500.0  |

| 103    | {“Jinh” : 22500.0   |

| 104    | {“Mist” : 15500.0   |

| 105    | {“Suki” : 13000.0   |

| 108    | {“Rock” : 33000.0   |

+--------+---------------------+

We have discussed, daily used aggregation function, as described in first table, there are number of aggregation functions available in MySQL.

Read More