MySQL和Oracle虽然在架构上有很大的不同，但是如果从某些方面比较起来，它们有些方面也是相通的。毕竟学习的主线是MySQL，所以会mysql和oracle虽然在架构上有很大的不同，但是如果从某些方面比较起来，它们有些方面也是相通的。
毕竟学习的主线是mysql，所以会从mysql的角度来对比oracle的一些功能。
大体总结了以下的内容，欢迎大家拍砖，查看当前的数据库名mysql> select database();+------------+| database() |+------------+| test |+------------+1 row in set (0.00 sec)+++ oracle的实现方法 ++++因为架构的不同，所以列举了数据库，实例级的查询方法。
方法一，通过数据库参数来查看sql> show parameter instance_name name type value------------------------------------ --------------------------------- ------------------------------instance_name string truabp4方法二：通过数据字典来查看数据库级sql> select name from v$database;name---------------------------truabp4实例级sql> select instance_name from v$instance;instance_name------------------------------------------------truabp4方法三：通过内置函数来实现，这种方法相比前两种更为通用。
sql> select sys_context('userenv','instance_name') from dual;sys_context('userenv','instance_name')----------------------------------------------------truabp4得到数据库创建的脚本得到数据库名为mysql的创建脚本，，毕竟在架构实现上不同，有点类似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)+++ oracle的实现方法 ++++oracle中的实现方式相比要复杂很多。叫法一样，但是实现还是有很大的差别。
create database mynewdb user sys identified by pz6r58 user system identified by y1tz5p logfile group 1 ('/u01/oracle/oradata/mynewdb/redo01.log') size 100m,group 2 ('/u01/oracle/oradata/mynewdb/redo02.log') size 100m,group 3 ('/u01/oracle/oradata/mynewdb/redo03.log') size 100m 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 325m reuse extent management local sysaux datafile '/u01/oracle/oradata/mynewdb/sysaux01.dbf' size 325m reuse default temporary tablespace tempts1 tempfile '/u01/oracle/oradata/mynewdb/temp01.dbf'size 20m reuse undo tablespace undotbs datafile '/u01/oracle/oradata/mynewdb/undotbs01.dbf'size 200m reuse autoextend on maxsize unlimited;查看当前的用户mysql> select user();+----------------+| user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)+++ oracle的实现方法 ++++方法一，通过sql*plus中的show user命令杰克得到sql> show user user is "n1"方法二：通过内置函数来实现，比较通用的方式。
sql> select sys_context('userenv','current_user') from dual;sys_context('userenv','current_user')--------------------------------------------------n1查看含有的表信息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)+++ oracle的实现方法 ++++方法一：通过cat同义词来实现sql> select *from cat where rownumtable_name tabtype------------------ ---------------------aaa table aaaa table方法二：通过tab同义词来实现sql> select *from tab where rownumtname tabtype clusterid------------------ --------------------- ----------aaa table aaaa table方法三：通过数据字典user_tables来实现sql> select table_name from user_tables where rownumtable_name------------------aaa aaaa查看指定数据库中的表信息比如查询数据库名为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)+++ oracle的实现方法 ++++oracle中的实现还是根据数据字典表*_tables sql> select table_name from all_tables where owner='refwork';table_name------------------------------offer查看test数据库中的表temp结构>mysqlshow test temp database: test table: temp+---------+-------------+-----------------+------+-----+---------+--------------| field | type | collation | null | key | default | extra+---------+-------------+-----------------+------+-----+---------+--------------| id | int(11) | | no | pri | | auto_incremen| char | char(50) | utf8_general_ci | no | mul | || varchar | varchar(50) | utf8_general_ci | no | mul | || text | text | utf8_general_ci | no | mul | |+---------+-------------+-----------------+------+-----+---------+--------------+++ oracle的实现方法 ++++可以通过all_tab_cols来实现select table_name,column_name from all_tab_cols where owner='n1' and table_name='test';查看表的建表语句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)+++ oracle的实现方法 ++++oracle中一直直观的方式是使用dbms_metadata.get_ddl来实现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)...
得到表结构的信息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)+++ oracle的实现方法 ++++这一点完全一样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)得到表中的列信息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)+++ oracle的实现方法 ++++通过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得到索引的信息mysql> show index from columns_priv;+--------------+------------+----------+--------------+-------------+-----------| table | non_unique | key_name | seq_in_index | column_name | collation+--------------+------------+----------+--------------+-------------+-----------| columns_priv | 0 | primary | 1 | host | a| columns_priv | 0 | primary | 2 | db | a| columns_priv | 0 | primary | 3 | user | a| columns_priv | 0 | primary | 4 | table_name | a| columns_priv | 0 | primary | 5 | column_name | a+--------------+------------+----------+--------------+-------------+-----------5 rows in set (0.00 sec)+++ oracle的实现方法 ++++通过user_indexes来实现sql> select index_name,index_type from user_indexes where table_name='offer';index_name index_type------------------------------ ---------------------------inx_offer normal基于列的模糊查找可能在这方面mysql提供的直观方式要多一些。
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)+++ oracle的实现方法 ++++oracle里面还是用user_tab_cols，一用倒底。
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精确查找列名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)+++ oracle的实现方法 ++++还是使用usre_tab_cols，一用倒底。
sql> select column_name from user_tab_cols where table_name='offer' and column_name= 'object_name';column_name------------------------------object_name查看进程相关的信息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)+++ oracle的实现方法 ++++oracle中提供了比较全面的视图，可以通过v$session,v$process来查找username machine program sid------------------------------ -------------------- ------------------------- ----------refwork rac1 sqlplus@rac1 (tns v1-v3) 257 select *from v$process;查数据字典的信息mysql中的数据字典信息都包含在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 |+---------------------------------------++++ oracle的实现方法 ++++oracle中的视图更加丰富，除了常说的数据字典表，还有动态性能视图，调优诊断很是方便。
select *from dict; --数据字典表select *from v$fixed_table;--动态性能视图表