Base-Oracle-4

1、数据库环境（正式区、测试区）工厂（营运中心）业务数据TIPTOPOracle用户表系统数据同义词数据库数据库数据库表空间数据库段数据库分区数据库Oracle块数据库数据文件数据库OS块逻辑结构物理结构表和索引都位于该逻辑层次表的物理位置由Oracle自行管理，对用户透明数据库块（block）行首列长度列数据SQLCREATE TABLE imx_file( imx01 VARCHAR2(60), imx02 VARCHAR2(10), imx03 SMALLINT, imx04 DATE imx05 NUMBER(6);SQLCREATE TABLE imx_file( imx01 VARCH

2、AR2(60), imx02 VARCHAR2(10), imx03 SMALLINT, imx04 DATE imx05 NUMBER(6);SQLCREATE TABLE AS SELECTSQLCREATE TABLE AS SELECTSQLCREATE TABLE AS SELECTSQLCREATE TABLE imx_temp AS SELECT * FROM imx_fileSQLCREATE TABLE imx_temp AS SELECT * FROM imx_file WHERE 1=0SQLCREATE TABLE imx_temp AS SELECT * FROM i

3、mx_file WHERE 1=0SQLCREATE TABLE imx_temp AS SELECT imx01,imd02 FROM imx_file,imd_file WHERE imx01=imd01 SQLCREATE TABLE imx_temp AS SELECT imx01,imd02 FROM imx_file,imd_file WHERE imx01=imd01 SQLDROP TABLE imx_temp;SQLDROP TABLE imx_temp;SQLDROP TABLE imx_temp;SQLTRUNCATE TABLE imx_temp;SQLDROP TAB

4、LE imx_temp;SQLRENAME imx_temp to imx_temp1;SQLDROP TABLE imx_temp;SQLdesc imx_file; 名称名称 是否为空是否为空? 类型类型 - - - IMX000 NOT NULL VARCHAR2(40) IMX00 VARCHAR2(40) IMX01 VARCHAR2(40) IMX02 VARCHAR2(40) IMX03 VARCHAR2(10) IMX04 VARCHAR2(10) IMX05 VARCHAR2(10) IMX06 VARCHAR2(40) IMX07 VARCHAR2(40) IMX08 VA

5、RCHAR2(40) IMX09 VARCHAR2(40) IMX10 VARCHAR2(40) 名称名称 是否为空是否为空? 类型类型 - - - IMX000 NOT NULL VARCHAR2(40) IMX00 VARCHAR2(40) IMX01 VARCHAR2(40) IMX02 VARCHAR2(40) IMX03 VARCHAR2(10) IMX04 VARCHAR2(10) IMX05 VARCHAR2(10) IMX06 VARCHAR2(40) IMX07 VARCHAR2(40) IMX08 VARCHAR2(40) IMX09 VARCHAR2(40) IMX10

6、VARCHAR2(40) SQLDROP TABLE imx_temp;SQLALTER TABLE imx_file add imx11 varchar2(10);SQLDROP TABLE imx_temp;SQLALTER TABLE imx_file drop column imx11;SQLDROP TABLE imx_temp;SQLALTER TABLE imx_file modify imx11 varchar2(20);SQLDROP TABLE imx_temp;SQLALTER TABLE imx_file rename column imx1 to imx2; SQLD

7、ROP TABLE imx_temp;SQLALTER TABLE imx_temp modiy imx11 null/not null;DBA_TABLES DBA_TAB_COLUMNS ALL_TABLES ALL_TAB_COLUMNSUSER_TABLES USER_TAB_COLUMNSDBA_TABLES DBA_TAB_COLUMNS DBA_OBJECTSALL_TABLES ALL_TAB_COLUMNS ALL_OBJECTSUSER_TABLES USER_TAB_COLUMNS USER_OBJECTSDBA_XXXSYSTEMDSDS1DS2USER_XXXUSER

8、_XXXUSER_XXXUSER_XXXALL_XXXSQLq_datafileSQLq_locktableLocked Object Tiptop User Proc Terminal SID SERIAL# MACHINE - - - - - - - DS1.OGB_FILE tiptop 28525 136 3727 ERP DS1.OHA_FILE tiptop 28525 136 3727 ERP DS1.OGA_FILE tiptop 3491 146 2543 ERP DS1.OGA_FILE tiptop 3603 147 1906 ERP DS1.OGB_FILE tipto

9、p 3603 147 1906 ERP DS1.OMA_FILE tiptop 3603 147 1906 ERP Locked Object Tiptop User Proc Terminal SID SERIAL# MACHINE - - - - - - - DS1.OGB_FILE tiptop 28525 136 3727 ERP DS1.OHA_FILE tiptop 28525 136 3727 ERP DS1.OGA_FILE tiptop 3491 146 2543 ERP DS1.OGA_FILE tiptop 3603 147 1906 ERP DS1.OGB_FILE t

10、iptop 3603 147 1906 ERP DS1.OMA_FILE tiptop 3603 147 1906 ERP SQLq_datafileSQLalter system kill session SID,SERIAL;SQLq_datafileSQLq_lockOS_USER PID ORL_USR OID LOCK_TYPE LOCK_HELD LOCK_REQUEST STATUS OWNER OBJ_NAME- - - - - - - - - -tiptop 3603 DS1 147 Transaction Exclusive None Blocking DS1 OMA_FI

11、LEtiptop 3603 DS1 147 DML Row-X (SX) None Not Blocking DS1 OMA_FILEtiptop 3603 DS1 147 DML Row-X (SX) None Not Blocking DS1 OMA_FILEtiptop 3603 DS1 147 DML Row-X (SX) None Not Blocking DS1 OMA_FILEtiptop 3603 DS1 147 Transaction None Exclusive Not Blocking DS1 OMA_FILEtiptop 3603 DS1 147 Transaction

12、 Exclusive None Blocking DS1 OGA_FILEtiptop 3603 DS1 147 DML Row-X (SX) None Not Blocking DS1 OGA_FILEtiptop 3603 DS1 147 DML Row-X (SX) None Not Blocking DS1 OGA_FILEtiptop 3603 DS1 147 DML Row-X (SX) None Not Blocking DS1 OGA_FILEtiptop 3603 DS1 147 Transaction None Exclusive Not Blocking DS1 OGA_

13、FILEtiptop 3491 DS1 146 Transaction Exclusive None Blocking DS1 OGA_FILEOS_USER PID ORL_USR OID LOCK_TYPE LOCK_HELD LOCK_REQUEST STATUS OWNER OBJ_NAME- - - - - - - - - -tiptop 3603 DS1 147 Transaction Exclusive None Blocking DS1 OMA_FILEtiptop 3603 DS1 147 DML Row-X (SX) None Not Blocking DS1 OMA_FI

14、LEtiptop 3603 DS1 147 DML Row-X (SX) None Not Blocking DS1 OMA_FILEtiptop 3603 DS1 147 DML Row-X (SX) None Not Blocking DS1 OMA_FILEtiptop 3603 DS1 147 Transaction None Exclusive Not Blocking DS1 OMA_FILEtiptop 3603 DS1 147 Transaction Exclusive None Blocking DS1 OGA_FILEtiptop 3603 DS1 147 DML Row-

15、X (SX) None Not Blocking DS1 OGA_FILEtiptop 3603 DS1 147 DML Row-X (SX) None Not Blocking DS1 OGA_FILEtiptop 3603 DS1 147 DML Row-X (SX) None Not Blocking DS1 OGA_FILEtiptop 3603 DS1 147 Transaction None Exclusive Not Blocking DS1 OGA_FILEtiptop 3491 DS1 146 Transaction Exclusive None Blocking DS1 O

16、GA_FILESQLq_datafileSQLq_lockSQLUSERNAME SID SERIAL# TY LOCK_TYPE LOCK_REQUESTE ID1 ID2 SQL_TEXT - - - - - - - - -DS1 136 3727 TX None Exclusive 262184 4289 update ogb_file SET ogb63DS1 136 3727 TX None Exclusive 262184 4289 = :p1,ogb64 = :p2 WHERE DS1 136 3727 TX None Exclusive 262184 4289 ogb01 =

17、:p3 AND ogb03 = :p4DS1 146 2543 TX Exclusive None 655372 4311 select sma124, smb04 from DS1 146 2543 TX Exclusive None 655372 4311 sma_file ,smb_file whereDS1 146 2543 TX Exclusive None 655372 4311 sma00 = 0 AND sma124 = DS1 146 2543 TX Exclusive None 655372 4311 smb01 AND smb02 = 0DS1 147 1906 TX E

18、xclusive None 262184 4289 update oga_file SET oga10 = DS1 147 1906 TX Exclusive None 262184 4289 :p1 WHERE oga01 = :p2USERNAME SID SERIAL# TY LOCK_TYPE LOCK_REQUESTE ID1 ID2 SQL_TEXT - - - - - - - - -DS1 136 3727 TX None Exclusive 262184 4289 update ogb_file SET ogb63DS1 136 3727 TX None Exclusive 2

19、62184 4289 = :p1,ogb64 = :p2 WHERE DS1 136 3727 TX None Exclusive 262184 4289 ogb01 = :p3 AND ogb03 = :p4DS1 146 2543 TX Exclusive None 655372 4311 select sma124, smb04 from DS1 146 2543 TX Exclusive None 655372 4311 sma_file ,smb_file whereDS1 146 2543 TX Exclusive None 655372 4311 sma00 = 0 AND sm

20、a124 = DS1 146 2543 TX Exclusive None 655372 4311 smb01 AND smb02 = 0DS1 147 1906 TX Exclusive None 262184 4289 update oga_file SET oga10 = DS1 147 1906 TX Exclusive None 262184 4289 :p1 WHERE oga01 = :p2DSDS1ZX_FILEZX_FILESELECT * FROM ds1.zx_file类似于Windows中的快捷方式SQLCREATE TABLE AS SELECTSQLCREATE S

21、YNONYM table FOR owner.table;SQLCREATE TABLE AS SELECTSQLCREATE SYNONYM ze_file FOR ds.ze_file;SQLCREATE TABLE AS SELECTSQLDROP TABLE ze_file;SQLq_datafileSQLq_synonymSynonym Owner.Table- -ZZLL_FILE DS.ZZLL_FILEZZLN_FILE DS.ZZLN_FILEZZLQ_FILE DS.ZZLQ_FILEZZL_FILE DS.ZZL_FILEZZW_FILE DS.ZZW_FILEZZZ_F

22、ILE DS.ZZZ_FILEZZ_FILE DS.ZZ_FILE Synonym Owner.Table- -ZZLL_FILE DS.ZZLL_FILEZZLN_FILE DS.ZZLN_FILEZZLQ_FILE DS.ZZLQ_FILEZZL_FILE DS.ZZL_FILEZZW_FILE DS.ZZW_FILEZZZ_FILE DS.ZZZ_FILE. . SQLq_datafileSQLq_synOWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK - - - - -DS3 GAY_FILE DS GAY_FILE DS3 GAZ_F

23、ILE DS GAZ_FILE DS3 GBA_FILE DS GBA_FILE DS3 GBB_FILE DS GBB_FILE DS3 GBD_FILE DS GBD_FILE DS3 GBE_FILE DS GBE_FILE DS3 GBF_FILE DS GBF_FILE DS3 GBH_FILE DS GBH_FILE DS3 GBI_FILE DS GBI_FILE DS3 GBJ_FILE DS GBJ_FILE DS3 GBK_FILE DS GBK_FILE OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK - - - - -

24、DS3 GAY_FILE DS GAY_FILE DS3 GAZ_FILE DS GAZ_FILE DS3 GBA_FILE DS GBA_FILE DS3 GBB_FILE DS GBB_FILE DS3 GBD_FILE DS GBD_FILE DS3 GBE_FILE DS GBE_FILE DS3 GBF_FILE DS GBF_FILE DS3 GBH_FILE DS GBH_FILE DS3 GBI_FILE DS GBI_FILE DS3 GBJ_FILE DS GBJ_FILE DS3 GBK_FILE DS GBK_FILE SELECT * FROM topdb_imato

25、pprodtopdb_Imatopdbima_fileOracle还可以通过这种方式访问SQL Server中的数据SQLcreate public database link topnj_ds1 connect to ds1 identified by ds1 using topnj;SQLcreate public database link topnj_ds1 connect to ds1 identified by ds1 using topnj;topnj = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOS

26、T = 10.4.234.234)(PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = topdb)topnj = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.234.234)(PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = topdb)SQLselect * from ima_filetopnj_ds1;SQLselect * from ima_filetopnj_ds1;Public关键字表示该数据库链接可以被所有

27、用户使用，不加该关键字则表示该数据库链接只限创建者自己使用SQLselect * from ima_filetopnj_ds1;SQLcreate synonym ima_nj for ima_filetopnj_ds1;SQLselect * from ima_filetopnj_ds1;SQLselect * from ima_nj;SQLselect owner,object_name from dba_objects where object_type=DATABASE LINK;SQLselect owner,object_name from dba_objects where ob

28、ject_type=DATABASE LINK;SQLselect * from ima_filetopnj_ds1;SQLdrop public database link topnj_ds1;SQLselect * from ima_filetopnj_ds1;SQLalter system set global_name=FALSE;NAME TYPE VALUE - - - global_names boolean FALSE NAME TYPE VALUE- - -global_names boolean FALSE SQLselect * from ima_filetopnj_ds

29、1;SQLshow parameters global_name;NAME TYPE VALUE - - - global_names boolean FALSE GLOBAL_NAME - TOPDB SQLselect * from ima_filetopnj_ds1;SQLselect * from global_name;OOOOOOBBBBBBFFFRRR数据对象序号相关文件序号行序号块序号索引头Key值长度Key值内容ROWID根节点分支节点叶子节点索引项B-TREE索引位图索引key起始ROWID终止ROWID位图表索引块 10块 11块 12文件 3B-TREE索引位图索引适合于低重复性的列适合高重复性的列数据更新时的开销较小数据更新时的开销较大当使用OR子句的时候效率较低当使用OR子句的时候效率较高适用于OLTP适用于数据仓库在TIPTOP中使用的都是B-TREE索引SQLDROP TABLE imx_temp;SQ