DIFFERENCE BETWEEN ORACLE AND MySQL -05
Please refer my previous articles.
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
Note: Please test scripts in Non Prod before trying in Production.
Loading...
naveen
Nice Informative Article.
Mahesh S
nice article with good information.