DIFFERENCE BETWEEN ORACLE AND MySQL -05

Share via:

DIFFERENCE BETWEEN ORACLE AND MySQL -05

Please refer my previous articles.

DIFFERENCE BETWEEN ORACLE AND MySQL -04

In this article will see few more differences between ORACLE and MySQL.

Differences between Oracle and MySQL
SNO ORACLE MySQL
1 In Oracle ,we need to write Parameters to set (or) change the values in pfile or spfile
parameters like …
control_files=
undo_tablespace=
log_archive_dest=
To check the values in pfile we use show command along with parameter
SQL> show parameter %cont%
In MySQL,we need to write Variables to set (or) change the values in my.cnf file
variables like …
bin-log
log_error
max_connect_errors
To check the values in my.cnf we use show command along with variables & like commands
mysql> show variables like ‘%error%’;
2 In the earlier version of Oracle, we have rollback segment to keep before image of DML transactions .
But in 9i we have concept called Undo.
we can create multiple undo tablespace but by default we can use only one.
To set undo tablespace we have undo_tablespaces parameter in pfile .
We have retention policy for undo as 900 sec.
To set undo retention we have parameter called undo_retention.
In the earlier versions of MySQL , UNDO logs were invisible..!! As, UNDO logs were the part of
System table space, i.e., ibdata1.
But, as of 5.7 and later, you can create multiple and separate UNDO log files(table space files).Use variable innodb_undo_tablespaces to set the number of table space files. Max value is 128 and minimum 0.
The Undo logs set by the variable innodb_undo_logs will be divided between the set table spaces.
Maximum you can create 128 undo logs and it is the default value.
The path of the UNDO log table space can be set by the variable innodb_undo_directory.
3 In Oracle , we don’t have a concept like Storage Engines . In MySQL we have different Storage engines to store the data like .
Innodb
MyISAM
BLACKHOLE
ARCHIVE
PERFORMANCE_SCHEMA
To check all the engine we have command
mysql> show engines;
4 In Oracle , when table is created it is getting stored in tablespaces and same is applicable to indexes.
The table structure is stored in system tablespace.
In MySQL , when we create a table MySQL will create files in os.
If it is Innodb storage engine then two files will be created under database directoy with extension .FRM .IBD
If it is MyISAM storage engine then three files will be created under database directory with extension of .FRM .MYD .MYI
.FRM stands for format file which stores table definition.
.IBD stands for InnoDB datafile which stores InnoDB data and Index information.
.MYD stands for MyISAM DATA which stores data for MyISAM tables.
.MYI stands for MyISAM indexes which stores indexes for MyISAM tables.
5 In Oracle we can store all the tables at single datafile or we can store different datafiles In MySQL , we can store in a single datafile(ibdata1) or we can ditribute data into different files .
To store data in single file we need to set innodb_file_per_table=off then all data will be stored in ibdata1 file.
To store data in multiple files we need to enable innodb_file_per_table .Now data will be stored in .IBD file associated with table.
6 In Oracle we can create same table name in different schemas. In MySQL we can create same table name in different databases.
Ex :
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| ctg |
| mysql |
| performance_schema |
| sys |
| trg |
| trg2 |
+——————–+
7 rows in set (0.00 sec)mysql> show grants;
+——————————————————————–+
| Grants for abc@localhost |
+——————————————————————–+
| GRANT ALL PRIVILEGES ON *.* TO ‘abc’@’localhost’ WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON trg.* TO ‘abc’@’localhost’ |
+——————————————————————–+
2 rows in set (0.00 sec)mysql> select * from trg.test3;
Empty set (0.00 sec)mysql> select * from trg2.test3;
Empty set (0.00 sec)
7 How to check a specified table tablespaces.
SQL> select table_name,tablespace_name from dba_tables where table_name=’TEST’;
TABLE_NAME TABLESPACE_NAME
——————- —————————-
TEST USERS02
How to check a specified table database/table schema.
mysql> SELECT TABLE_NAME,TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘trg’;
+————+————–+
| TABLE_NAME | TABLE_SCHEMA |
+————+————–+
| test3 | trg |
| trg | trg |
| tt | trg |
| tt1 | trg |
+————+————–+
4 rows in set (0.00 sec)
8 In Oracle , For long running queries we need to check from v$session view . In MySQL,we can identify long running queries in slow log file
mysql>show variables like ‘%long_query%’;
+—————–+———–+
| Variable_name | Value |
+—————–+———–+
| long_query_time | 10.000000 |
+—————–+———–+
1 row in set (0.00 sec)
mysql> show variables like ‘%slow_query_log_file%’;
+———————+——————————————+
| Variable_name | Value |
+———————+——————————————+
| slow_query_log_file | /var/lib/mysql/ip-172-31-21-159-slow.log |
+———————+——————————————+
1 row in set (0.00 sec)
mysql> Select * from information_schema.processlist ;
+—-+——+—————–+——+———+——+———–+———————————————-+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+—-+——+—————–+——+———+——+———–+———————————————-+
| 17 | root | localhost | NULL | Sleep | 1502 | | NULL |
| 18 | abc | localhost:35800 | NULL | Query | 0 | executing | Select * from information_schema.processlist |
| 16 | abc | localhost | NULL | Sleep | 1521 | | NULL |
+—-+——+—————–+——+———+——+———–+———————————————-+
3 rows in set (0.00 sec)
9 In Oracle , to check any errors number we use oerr utility
Ex :
SQL> !oerr ora 103301033, 00000, “ORACLE initialization or shutdown in progress”
// *Cause: An attempt was made to log on while Oracle is being started up
// or shutdown.
// *Action: Wait a few minutes. Then retry the operation.
In MySQL, to check error number we use perror utility.
[root@ip-172-31-21-159 ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
[root@ip-172-31-21-159 ~]# perror 1045
MySQL error code 1045 (ER_ACCESS_DENIED_ERROR): Access denied for user ‘%-.48s’@’%-.64s’ (using password: %s)
[root@ip-172-31-21-159 ~]#
10 In Oracle we don’t have any help command to view the Syntax’s and description. In MySQL , We have command called help to view the syntax’s and description of each topics.
mysql> help contents
You asked for help about help category: “Contents”
For more information, type ‘help <item>’, where <item> is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Help Metadata
Language Structure
Plugins
Procedures
Storage Engines
Table Maintenance
Transactions
User-Defined Functions
Utility
mysql> help Functions
You asked for help about help category: “Functions”
For more information, type ‘help <item>’, where <item> is one of the following
categories:
Bit Functions
Comparison operators
Control flow functions
Date and Time Functions
Encryption Functions
Information Functions
Logical operators
Miscellaneous Functions
Numeric Functions
String Functionsmysql> help Bit Functions
You asked for help about help category: “Bit Functions”
For more information, type ‘help <item>’, where <item> is one of the following
topics:
&
<<
>>
BIT_COUNT
^
|
~mysql> help &
Name: ‘&’
Description:
Syntax:
&Bitwise AND.The result is an unsigned 64-bit integer.URL: http://dev.mysql.com/doc/refman/5.7/en/bit-functions.htmlExamples:
mysql> SELECT 29 & 15;
-> 13

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

2 thoughts on “DIFFERENCE BETWEEN ORACLE AND MySQL -05

Leave a Reply to naveen Cancel reply