DIFFERENCE BETWEEN ORACLE AND MySQL.
Please refer previous article .
In this article will see few more differences between ORACLE and MySQL.
| Differences between Oracle and MySQL
|
||
| SNO | ORACLE | MySQL |
| 1 | How to check metadata of table ? SYS>> desc <objectname> “;” is not required. Another way of getting metadata by using packages. set pagesize 0 set long 90000 set feedback off set echo off set heading 999 set lines 100 SYS>>select dbms_metadata.GET_DDL(u.object_type,u.object_name,’EMP’) from dba_objects u where owner = ‘EMP’; |
How to check metadata of table ? mysql> desc t1; +——-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——-+————-+——+—–+———+——-+ | id | int(11) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | +——-+————-+——+—–+———+——-+ 2 rows in set (0.00 sec) Anothe way of getting metadata using information_schema.columns. By defaut all metadata will store in columns(object) under information_schema. mysql> select * from information_schema.columns where table_name=’t1’\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: ctg TABLE_NAME: t1 COLUMN_NAME: id ORDINAL_POSITION: 1 COLUMN_DEFAULT: NULL IS_NULLABLE: YES DATA_TYPE: int CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: 10 NUMERIC_SCALE: 0 DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: int(11) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: GENERATION_EXPRESSION: *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: ctg TABLE_NAME: t1 COLUMN_NAME: name ORDINAL_POSITION: 2 COLUMN_DEFAULT: NULL IS_NULLABLE: YES DATA_TYPE: varchar CHARACTER_MAXIMUM_LENGTH: 10 CHARACTER_OCTET_LENGTH: 10 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: latin1 COLLATION_NAME: latin1_swedish_ci COLUMN_TYPE: varchar(10) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: GENERATION_EXPRESSION: 2 rows in set (0.00 sec) |
| 2 | How to find table size in ORACLE ? In Oracle, we can calculate the size of table by using dba_segments.SYS>> SELECT owner, segment_name, segment_type, tablespace_name, bytes/1048576 MB, initial_extent, next_extent, extents, pct_increase FROM DBA_SEGMENTS WHERE OWNER = ‘table owner’ AND SEGMENT_NAME = ‘table name’ AND SEGMENT_TYPE = ‘TABLE. |
How to find table size in MySQL ? In MySQL to calculate table size we have information_schema.tables We need to sum data_length & index_length to calculate exact size of tables mysql>>SELECT table_schema as Database,table_name AS Table,round(((data_length + index_length) / 1024 / 1024), 2) Size in MBFROM information_schema.TABLES ORDER BY (data_length + index_length) DESC; |
| 3 | In Oracle, The SYSDBA system privilege is for fully empowered database administrators
|
In MySQL , the ROOT user has all permissions . |
| 4 | How to find out Transaction log information ? In Oracle we have v$log viewSQL> select GROUP#,BYTES/1024/1024 from v$log;GROUP# BYTES/1024/1024 ———- ————— 1 50 2 50 3 50In Oracle, by default every logfile has 50MB. |
How to find out Transaction log information ? In MySQL we have variables like innodb_log_files_in_group mysql> show variables like ‘%innodb_log_files_in_group%’; +—————————+——-+ | Variable_name | Value | +—————————+——-+ | innodb_log_files_in_group | 2 | +—————————+——-+ 1 row in set (0.00 sec)mysql> show variables like ‘%innodb_log_file_size%’; +———————-+———-+ | Variable_name | Value | +———————-+———-+ | innodb_log_file_size | 50331648 | +———————-+———-+ 1 row in set (0.01 sec)In MySQL , by default every logfile has 500MB. |
| 5 | How to kill a user session. In Oracle , to find out user session details we have v$session SQL> select sid,serial#,username from v$session where username=’USER1′;SID SERIAL# USERNAME ———- ———- —————————— 66 35175 USER1To Kill a session we follow below syntax SQL> ALTER SYSTEM KILL SESSION ‘66,35175’;System altered. |
How to kill a user session. In MySQL , to find out user session details we have Show processlist. mysql> show processlist; +—-+——+———–+——+———+——+———-+——————+ | Id | User | Host | db | Command | Time | State | Info | +—-+——+———–+——+———+——+———-+——————+ | 35 | root | localhost | NULL | Sleep | 4960 | | NULL | | 40 | trg | localhost | NULL | Sleep | 40 | | NULL | | 41 | root | localhost | NULL | Query | 0 | starting | show processlist | +—-+——+———–+——+———+——+———-+——————+ 3 rows in set (0.00 sec) To kill user we need only session Id we will kill trg user using kill command. mysql> kill 40; Query OK, 0 rows affected (0.00 sec) Check connected users again mysql> show processlist; +—-+——+———–+——+———+——+———-+——————+ | Id | User | Host | db | Command | Time | State | Info | +—-+——+———–+——+———+——+———-+——————+ | 35 | root | localhost | NULL | Sleep | 4999 | | NULL | | 41 | root | localhost | NULL | Query | 0 | starting | show processlist | +—-+——+———–+——+———+——+———-+——————+ 2 rows in set (0.00 sec) |
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
Good article