DIFFERENCE BETWEEN ORACLE AND MySQL -04

Share via:

DIFFERENCE BETWEEN ORACLE AND MySQL.

Please refer previous article .

DIFFERENCE BETWEEN ORACLE AND MySQL -03

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 MB
FROM 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

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

One thought on “DIFFERENCE BETWEEN ORACLE AND MySQL -04

Add Comment