Comparison between MySQL and Oracle-data dictionary metadata

Although the architecture of MySQL and Oracle is quite different, if they are compared from some aspects, they are also the same in some aspects. After all, the main learning line is MySQL.

Although the architecture of MySQL and Oracle is quite different, if they are compared from some aspects, they are also the same in some aspects. After all, the main learning line is MySQL.

Although the architecture of MySQL and Oracle is quite different, if they are compared from some aspects, they are also the same in some aspects.
After all, the main learning line is MySQL, so we will compare some features of Oracle from the MySQL perspective. I have summarized the following content. You are welcome to make a brick,
View the current database name
Mysql> select database ();
+ ------------ +
| DATABASE () |
+ ------------ +
| Test |
+ ------------ +
1 row in set (0.00 sec)

+++ Implementation Method of Oracle +++
Because of the different architectures, the database and instance-level query methods are listed.
Method 1: View through database Parameters
SQL> show parameter instance_name
NAME TYPE VALUE
---------------------------------------------------------------------------------------------------
Instance_name string TRUABP4

Method 2: View data dictionary
Database-level
SQL> select name from v $ database;
NAME
---------------------------
TRUABP4

Instance-level
SQL> select instance_name from v $ instance;
INSTANCE_NAME
------------------------------------------------
TRUABP4
Method 3: Use built-in functions. This method is more common than the first two.
SQL> select sys_context ('userenv', 'instance _ name') from dual;
SYS_CONTEXT ('userenv', 'instance _ name ')
----------------------------------------------------
TRUABP4

Obtain the database creation script
The database creation script named mysql is obtained. After all, the architecture implementation is different, which is similar to the user level in oracle.
Mysql> show create database mysql;
+ ---------- + ------------------------------------------------------------------ +
| Database | Create Database |
+ ---------- + ------------------------------------------------------------------ +
| Mysql | create database 'mysql '/*! 40100 default character set latin1 */|
+ ---------- + ------------------------------------------------------------------ +
1 row in set (0.00 sec)

+++ Implementation Method of Oracle +++
The implementation method in Oracle is much more complicated than that in Oracle. The name is the same, but the implementation is still quite different.
Create database mynewdb
User sys identified by pz6r58
User system identified by y1tz5 P
Logfile group 1 ('/u01/oracle/oradata/mynewdb/redo01.log') SIZE 100 M,
GROUP 2 ('/u01/oracle/oradata/mynewdb/redo02.log') SIZE 100 M,
GROUP 3 ('/u01/oracle/oradata/mynewdb/redo03.log') SIZE 100 M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
Character set US7ASCII
National character set AL16UTF16
DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325 M REUSE
EXTENT MANAGEMENT LOCAL
Sysaux datafile '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325 M REUSE
Default temporary tablespace tempts1
TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20 M REUSE
Undo tablespace undotbs
DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200 m reuse autoextend on maxsize unlimited;

View Current User
Mysql> select user ();
+ ---------------- +
| USER () |
+ ---------------- +
| Root @ localhost |
+ ---------------- +
1 row in set (0.00 sec)

+++ Implementation Method of Oracle +++
Method 1: run the show user command in SQL * plus to obtain
SQL> show user
USER is "N1"
Method 2: It is implemented through built-in functions, which is a common method.
SQL> select sys_context ('userenv', 'current _ user') from dual;
SYS_CONTEXT ('userenv', 'current _ user ')
--------------------------------------------------
N1

View table information
Mysql> show tables;
+ --------------------------- +
| Tables_in_mysql |
+ --------------------------- +
| Columns_priv |
| Db |
| Event |
| Func |
| General_log |
| Help_category |
| Time_zone_transition_type |
| User |
+ --------------------------- +
28 rows in set (0.00 sec)

+++ Implementation Method of Oracle +++
Method 1: Use cat Synonyms
SQL> select * from cat where rownum <3;
TABLE_NAME TABTYPE
---------------------------------------
AAA TABLE
AAAA TABLE
Method 2: Use a tab Synonym
SQL> select * from tab where rownum <3;
TNAME TABTYPE CLUSTERID
-------------------------------------------------
AAA TABLE
AAAA TABLE
Method 3: Use the data dictionary user_tables.
SQL> select table_name from user_tables where rownum <3;
TABLE_NAME
------------------
AAA
AAAA

View table information in a specified database
For example, query the table in the database named mysql.
Mysql> show tables from mysql;
+ --------------------------- +
| Tables_in_mysql |
+ --------------------------- +
| Columns_priv |
| Db |
| Event |
| Func |
| General_log |
| Help_category |
| Help_keyword |
| Help_relation |
| Time_zone_transition_type |
| User |
+ --------------------------- +
28 rows in set (0.00 sec)

+++ Implementation Method of Oracle +++
The implementation in Oracle is based on the data dictionary table * _ tables
SQL> select table_name from all_tables where owner = 'refwork ';
TABLE_NAME
------------------------------
OFFER

View the temp structure of the table in the test Database
> Mysqlshow test temp
Database: test Table: temp
+ --------- + ------------- + --------------- + ------ + ----- + --------- + --------------
| Field | Type | Collation | Null | Key | Default | Extra
+ --------- + ------------- + --------------- + ------ + ----- + --------- + --------------
| Id | int (11) | NO | PRI | auto_incremen
| Char (50) | utf8_general_ci | NO | MUL |
| Varchar (50) | utf8_general_ci | NO | MUL |
| Text | utf8_general_ci | NO | MUL |
+ --------- + ------------- + --------------- + ------ + ----- + --------- + --------------

+++ Implementation Method of Oracle +++
It can be implemented through all_tab_cols.
Select table_name, column_name from all_tab_cols where owner = 'n1 'and table_name = 'test ';

View table creation statements
Mysql> show create table event \ G
* *************************** 1. row ***************************
Table: event
Create Table: create table 'event '(
'Db' char (64) character set utf8 COLLATE utf8_bin not null default '',
'Name' char (64) not null default '',
'Body' longblob not null,
'Definer' char (77) character set utf8 COLLATE utf8_bin not null default '',
'Execute _ at' datetime default null,
'Body _ utf8' longblob,
Primary key ('db', 'name ')
) ENGINE = MyISAM default charset = utf8 COMMENT = 'events'
1 row in set (0.00 sec)


+++ Implementation Method of Oracle +++
In oracle, dbms_metadata.get_ddl is used for implementation.
SQL> select DBMS_METADATA.GET_DDL (object_type => 'table', name => 'csm _ OFFER ') from dual
DBMS_METADATA.GET_DDL (OBJECT_TYPE => 'table', NAME => 'csm _ OFFER ')
--------------------------------------------------------------------------------
Create table "REFWORK". "OFFER"
("OWNER" VARCHAR2 (30) not null enable,
"OBJECT_NAME" VARCHAR2 (30) not null enable,
"SUBOBJECT_NAME" VARCHAR2 (30 ),
"OBJECT_ID" number not null enable,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2 (19 ),
"CREATED" date not null enable,
"LAST_DDL_TIME" date not null enable,
"TIMESTAMP" VARCHAR2 (19 )...

Obtain the table structure information.
Mysql> desc columns_priv
->;
+ ------------- + ---------------------------------------------- + ------ + -----
| Field | Type | Null | Key | Defa
+ ------------- + ---------------------------------------------- + ------ + -----
| Host | char (60) | NO | PRI |
| Db | char (64) | NO | PRI |
| User | char (16) | NO | PRI |
| Table_name | char (64) | NO | PRI |
| Column_name | char (64) | NO | PRI |
| Timestamp | timestamp | NO | CURR
| Column_priv | set ('select', 'insert', 'update', 'references ') | NO |
+ ------------- + ---------------------------------------------- + ------ + -----
7 rows in set (0.01 sec)

+++ Implementation Method of Oracle +++
This is exactly the same.
SQL> desc offer
Name Null? Type
-----------------------------------------------------------------------------
Owner not null VARCHAR2 (30)
OBJECT_NAME not null VARCHAR2 (30)
SUBOBJECT_NAME VARCHAR2 (30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2 (19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2 (19)
STATUS VARCHAR2 (7)
TEMPORARY VARCHAR2 (1)
GENERATED VARCHAR2 (1)
SECONDARY VARCHAR2 (1)

Obtain the column information in the table.
Mysql> show columns from columns_priv;
+ ------------- + ---------------------------------------------- + ------ + -----
| Field | Type | Null | Key | Defa
+ ------------- + ---------------------------------------------- + ------ + -----
| Host | char (60) | NO | PRI |
| Db | char (64) | NO | PRI |
| User | char (16) | NO | PRI |
| Table_name | char (64) | NO | PRI |
| Column_name | char (64) | NO | PRI |
| Timestamp | timestamp | NO | CURR
| Column_priv | set ('select', 'insert', 'update', 'references ') | NO |
+ ------------- + ---------------------------------------------- + ------ + -----
7 rows in set (0.01 sec)

+++ Implementation Method of Oracle +++
Use user_tab_cols.
SQL> select column_name from user_tab_cols where table_name = 'offer ';
COLUMN_NAME
------------------------------
OWNER
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME

Obtain the index information.
Mysql> show index from columns_priv;
+ -------------- + ------------ + ---------- + -------------- + -----------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
+ -------------- + ------------ + ---------- + -------------- + -----------
| Columns_priv | 0 | PRIMARY | 1 | Host |
| Columns_priv | 0 | PRIMARY | 2 | Db |
| Columns_priv | 0 | PRIMARY | 3 | User |
| Columns_priv | 0 | PRIMARY | 4 | Table_name |
| Columns_priv | 0 | PRIMARY | 5 | Column_name |
+ -------------- + ------------ + ---------- + -------------- + -----------
5 rows in set (0.00 sec)

+++ Implementation Method of Oracle +++
Implemented through User_indexes
SQL> select index_name, index_type from user_indexes where table_name = 'offer ';
INDEX_NAME INDEX_TYPE
---------------------------------------------------------
INX_OFFER NORMAL

Column-based fuzzy search
In this regard, MySQL may provide more intuitive methods.
Mysql> show columns from columns_priv like '% AB % ';
+ ------------ + ---------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------------ + ---------- + ------ + ----- + --------- + ------- +
| Table_name | char (64) | NO | PRI |
+ ------------ + ---------- + ------ + ----- + --------- + ------- +
1 row in set (0.01 sec)

+++ Implementation Method of Oracle +++
In Oracle, user_tab_cols is used.
SQL> select column_name from user_tab_cols where table_name = 'offer' and column_name like '% OBJE % ';
COLUMN_NAME
------------------------------
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE

Exact search for column names
Mysql> show columns from columns_priv where field = 'user ';
+ ------- + ---------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ---------- + ------ + ----- + --------- + ------- +
| User | char (16) | NO | PRI |
+ ------- + ---------- + ------ + ----- + --------- + ------- +
1 row in set (0.01 sec)

+++ Implementation Method of Oracle +++
Use usre_tab_cols.
SQL> select column_name from user_tab_cols where table_name = 'offer' and column_name = 'object _ name ';
COLUMN_NAME
------------------------------
OBJECT_NAME

View process-related information
Mysql> show processlist
->;
+ ---- + ------ + --------------- + ------- + --------- + ------ + ------- + ----------------
| Id | User | Host | db | Command | Time | State | Info
+ ---- + ------ + --------------- + ------- + --------- + ------ + ------- + ----------------
| 3 | root | localhost: 49479 | mysql | Query | 0 | init | show processlis
+ ---- + ------ + --------------- + ------- + --------- + ------ + ------- + ----------------
1 row in set (0.00 sec)

+++ Implementation Method of Oracle +++
Oracle provides a comprehensive view, which can be queried through v $ session and v $ process.
USERNAME MACHINE PROGRAM SID
-------------------------------------------------------------------------------------
REFWORK rac1 sqlplus @ rac1 (TNS V1-V3) 257


Select * from v $ process;

Query data dictionary Information
The data dictionary information in MySQL is included in schema information_schema.
> Mysqlshow information_schema
Database: information_schema
+ --------------------------------------- +
| Tables |
+ --------------------------------------- +
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
+ --------------------------------------- +

+++ Implementation Method of Oracle +++
The views in Oracle are richer. In addition to data dictionary tables and dynamic performance views, it is very convenient to optimize and diagnose.
Select * from dict; -- data dictionary table
Select * from v $ fixed_table; -- Dynamic Performance Chart