DIFFERENCE BETWEEN ORACLE AND MySQL -04

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)

 

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