01-0 Oracle 存儲與關(guān)鍵表設(shè)計(50)

1、Oracle RDBMS存儲與表設(shè)計2/21/20221內(nèi)容提要2/21/20222Oracle 數(shù)據(jù)庫-表空間-對象TablespaceTables, indexes, etc.DatafilesSegmentExtentBlocks2/21/20223Oracle 表空間(Tablespaces)2/21/20224表空間(tablespace)與表(Tables)TablespaceTables, indexes, etc.DatafilesSegmentExtentBlocks2/21/20225Oracle 表空間與數(shù)據(jù)文件F1.oraF2.oraF3.oraF4.oraF5.ora

2、Tablespace 1Tablespace 2Table 1Table 2Table 32/21/20226Oracle 塊結(jié)構(gòu)與段對象2/21/20227Type Format FillerDBASCNBaseSCNWrapSeq FlagChk ValFillerTailObj IDSCN of LastCleanoutNo of ITLSlotsFree ListFlagBlockTypeITL Freelist SlotDBA of next blockon FreelistBlockHeaderCacheLayerTransLayerITL Index NumberTrans ID

3、Undo Address.No ofRowsAffectedCommitted SCN/FreeSpace CreditsTable DirectoryRow DirectoryRowFlagClusterKeyColumnNumberLockByteDataColLengthDataColLengthRows.Transaction Free ListsFree SpaceOracle 塊結(jié)構(gòu)與段對象-塊內(nèi)部結(jié)構(gòu)2/21/20228Oracle 塊結(jié)構(gòu)與段對象2/21/20229內(nèi)容提要2/21/202210創(chuàng)建表結(jié)構(gòu)準(zhǔn)備2/21/202211創(chuàng)建表結(jié)構(gòu)準(zhǔn)備數(shù)據(jù)類型數(shù)據(jù)類型使用使用VARCH

4、AR2 (size)變長- 1 4000 字符CHAR (size)定長 1 2000字符NUMBER (p)整數(shù)NUMBER (p, s)定點數(shù)十進(jìn)制 NUMBER浮點數(shù)十進(jìn)制DATE日期類型LONG變長大對象( 4 Gigabyte)NCHAR類似 CHAR, 使用 2-字節(jié) 編碼BLOB二進(jìn)制數(shù)據(jù)，4GBCLOB單字節(jié)字符數(shù)據(jù)，可達(dá)4GBNCLOB類似CLOB, 支持2-字節(jié) 編碼BFILE引用的外部的二進(jìn)制文件LONG RAW原二進(jìn)制數(shù)據(jù)2/21/202212創(chuàng)建表結(jié)構(gòu)準(zhǔn)備2/21/202213創(chuàng)建表結(jié)構(gòu)準(zhǔn)備2/21/202214創(chuàng)建表結(jié)構(gòu)準(zhǔn)備2/21/202215創(chuàng)建表結(jié)構(gòu)準(zhǔn)備 BI

5、NARY-FLOAT BINARY-DOUBLE MAXIMUM FINITE VALUE 3.40282+38F 1.79769313486231E+308MINIMUM FINITE VALUE 1.17549E-38F 2.22507485850720E-30810g2/21/202216Oracle 對象創(chuàng)建準(zhǔn)備-數(shù)據(jù)類型2/21/202217內(nèi)容提要2/21/202218創(chuàng)建表結(jié)構(gòu)-語法CREATE TABLE USER. table_name( COLUMN1 DATATYPE DEFAULT EXPN COLUMN_CONSTRAINT |TABLE_CONSTRAINT , C

6、OLUMN1 DATATYPEDEFAULT EXPN COLUMN_CONSTRAINT |TABLE_CONSTRAINT . )CLUSTER CLUSTER (COLUMN1 ,COLUMN2 .) PCTFREE N PCTUSED NINITRANS N MAXTRANS NSTORAGE NTABLESPACE tablespace_name ENABLE | DISABLE AS QUERY2/21/202219創(chuàng)建表結(jié)構(gòu)語法-子句解釋2/21/202220創(chuàng)建表結(jié)構(gòu)語法-子句解釋2/21/202221創(chuàng)建表結(jié)構(gòu)語法-子句解釋2/21/202222創(chuàng)建表結(jié)構(gòu)語法-塊變化2/21

7、/202223內(nèi)容提要2/21/202224創(chuàng)建表結(jié)構(gòu)樣例1-塊控制參數(shù)create table user_dflow( user_name varchar2(30), /* 用用 戶戶 名名 */ bj_date date, /* 日期及時間日期及時間 */. . . day_fee_in number(16,6), /* 日入流量費用日入流量費用 */ day_fee_out number(16,6) /* 日出流量費用日出流量費用 */ )TABLESPACE users PCTFREE 5PCTUSED 80/2/21/202225創(chuàng)建表結(jié)構(gòu)樣例2-存儲分配參數(shù)create table

8、 user_dflow( user_name varchar2(30), /* 用用 戶戶 名名 */ bj_date date, /* 日期及時間日期及時間 */. . . day_fee_in number(16,6), /* 日入流量費用日入流量費用 */ day_fee_out number(16,6) /* 日出流量費用日出流量費用 */ )TABLESPACE users PCTFREE 5 PCTUSED 80STORAGE ( INITIAL 120M NEXT 60M PCTINCREASE 0 )2/21/202226內(nèi)容提要2/21/202227存儲參數(shù)在9i/10g的變

9、化2/21/202228存儲參數(shù)在9i/10g的變化2/21/202229存儲參數(shù)在9i/10g的變化2/21/202230存儲參數(shù)在9i/10g的變化DROP TABLE customer;CREATE TABLE customer ( customerID INT NOT NULL PRIMARY KEY, firstName VARCHAR2(30) NOT NULL, lastName VARCHAR2(30) NOT NULL, phone VARCHAR2(20) NULL, address1 VARCHAR2(30) NULL, address2 VARCHAR2(30) NUL

10、L, city VARCHAR2(30) NULL, state CHAR(2) NULL, zip CHAR(10) NULL ) PCTFREE 5 PCTUSED 85TABLESPACE SYSTEM STORAGE ( INITIAL 4M NEXT 1M PCTINCREASE 0);-DROP TABLE product; CREATE TABLE product ( productID INT NOT NULL PRIMARY KEY, productName VARCHAR2(30) NOT NULL, productVendor VARCHAR2(50) NULL, cos

11、t FLOAT NOT NULL CONSTRAINT chk_cost CHECK (cost 0), price FLOAT NOT NULL CONSTRAINT chk_price CHECK (price 0) ) PCTFREE 5 PCTUSED 85TABLESPACE USERS STORAGE ( INITIAL 4M NEXT 2M );SQL selectSQL select tablespace tablespace_name,table_name,initial_extent,next_extent,pct_free,pct_used_name,table_name

12、,initial_extent,next_extent,pct_free,pct_used 2 from 2 from dba dba_tables where table_name in (PRODUCT,CUSTOMER)_tables where table_name in (PRODUCT,CUSTOMER) 3 / 3 /TABLESPACE_NAM TABLE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_FREE PCT_USEDTABLESPACE_NAM TABLE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_FREE P

13、CT_USED- - - - - - - - - - -SYSTEM CUSTOMER 4194304 5 85SYSTEM CUSTOMER 4194304 5 85USERS PRODUCT 4194304 5USERS PRODUCT 4194304 5-好好觀察上面的結(jié)果好好觀察上面的結(jié)果-2/21/202231存儲參數(shù)在9i/10g的變化2/21/202232存儲參數(shù)在9i/10g的變化2/21/202233存儲參數(shù)在9i/10g的變化2/21/202234存儲參數(shù)在9i/10g的變化2/21/2022352/21/2022362/21/2022372/21/2022382/21/2

14、02239內(nèi)容提要2/21/202240修改表結(jié)構(gòu)-Alter Table2/21/202241修改表結(jié)構(gòu)-Alter Table2/21/202242修改表結(jié)構(gòu)2/21/202243修改表結(jié)構(gòu)2/21/202244刪除表結(jié)構(gòu)2/21/202245刪除表結(jié)構(gòu)2/21/202246釋放不用的空間ALTER TABLE employeesDEALLOCATE UNUSED;2/21/20224710g段收縮-表的存儲空間收縮2/21/20224810g段收縮-表的存儲空間收縮ALTER TABLE emp MOVE TABLESPACE STORAGE ( INITIAL 4M Next 2M m

15、inextents 2 );2/21/20224910g段收縮-表的存儲空間收縮2/21/20225010g段收縮-表的存儲空間收縮A L T E R T A B L E S H R I N K S P A C E COMPACT|CASCADE;10g2/21/20225110g段收縮-表的存儲空間收縮SQL ALTER TABLE emp space;ALTER TABLE emp space *第第 1 行出現(xiàn)錯誤行出現(xiàn)錯誤:ORA-01735: 無效的無效的 ALTER TABLE 選項選項SQL ALTER TABLE emp enable row movement;表已更改。表已更改。SQL A