script_88.txt

orale培训教材包括了所有的sql说明和实例

---------- tablespace.txt ----------
/*
 * 范例名称：学习对tablespace的管理
 * 文件名称：tableslpace.txt
 */

--以system/manager登录

--create tablespace
linux
/*
CREATE TABLESPACE app_data
DATAFILE '/ora8/app01.dbf' SIZE 1M AUTOEXTEND  ON NEXT 100K MAXSIZE 2M,
         '/ora8/app02.dbf' SIZE 1M AUTOEXTEND  OFF
DEFAULT STORAGE (INITIAL 100K NEXT 100K MAXEXTENTS 50 PCTINCREASE 0)
ONLINE;
*/


--create on windows
CREATE TABLESPACE app_data
DATAFILE 'c:\app01.dbf' SIZE 1M AUTOEXTEND  ON NEXT 100K MAXSIZE 2M,
         'c:\app02.dbf' SIZE 1M AUTOEXTEND  OFF
DEFAULT STORAGE (INITIAL 100K NEXT 100K MAXEXTENTS 50 PCTINCREASE 0)
ONLINE;



--query tablespace,看看新加入的tablespace
SELECT * FROM v$tablespace;
--在OS上看看linux  cd /ora8  ,ls -l

--test app_data,create a table on it;
CREATE TABLE tab_on_app
(ename  VARCHAR2(20),
sal  NUMBER(7,2))
TABLESPACE  app_data;

--察看tab_on_app
DESC tab_on_app;
INSERT INTO tab_on_app VALUES ( 'TOM',100.11);
COMMIT;
SELECT * FROM tab_on_app;

--OFFLINE THE TABLESPACE
ALTER TABLESPACE app_data OFFLINE;
SELECT * FROM tab_on_app;
/*
ERROR 位于第 1 行:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/ora8/app02.dbf'
*/

--ONLINE THE TABLESPACE
ALTER TABLESPACE app_data ONLINE;
SELECT * FROM tab_on_app;

--MAKE THE TABLESPACE READONLY
ALTER TABLESPACE app_data READ ONLY;
SELECT * FROM tab_on_app;
INSERT INTO tab_on_app VALUES ( 'TOM2',200.11);
/*
RROR 位于第 1 行:
RA-00372: file 9 cannot be modified at this time
RA-01110: data file 9: '/ora8/app02.dbf'
*/

--DROP THE TABLESPACE
DROP TABLESPACE app_data 
INCLUDING CONTENTS;
--query tablespace,看看新加入的tablespace还有么？
SELECT * FROM v$tablespace;
--在OS上看看linux  cd /ora8  ,ls -l

--重新建立app_data表空间
--VIEW SYSTEM DD
DESC DBA_TABLESPACES ;
SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='APP_DATA'

DESC DBA_DATA_FILES;

SELECT * FROM DBA_DATA_FILES WHERE FILE_NAME = 'C:\APP01.DBF';
SELECT * FROM DBA_DATA_FILES WHERE FILE_NAME = 'c:\app02.dbf';



SELECT * FROM DBA_DATA_FILES WHERE FILE_NAME = '/ora8/app01.dbf';
SELECT * FROM DBA_DATA_FILES WHERE FILE_NAME = '/ora8/app02.dbf';

--QUERY FROM V$TABLESPACE AND V$DATAFILE
SELECT * FROM V$TABLESPACE , V$DATAFILE WHERE V$TABLESPACE.TS#=V$DATAFILE.TS#;


---------- ROWID.txt ----------
/*
 * 范例名称：
 * 文件名称：ROWID.txt
 */


SELECT  ROWID,NAME FROM SM_EMP;

select ROWID,ename FROM emp ;

--用ROWID做WHERE条件,最快的定位方法！
select ROWID,ename FROM emp where ROWID ='AAAGDaAABAAAIRKAAA';

--显示记录的物理位置为更可读的方法
SELECT ROWID, 
       SUBSTR(ROWID,1,6) "OBJECT", 
       SUBSTR(ROWID,7,3) "FIL", 
       SUBSTR(ROWID,10,6) "BLOCK", 
       SUBSTR(ROWID,16,3) "ROW" 
       FROM emp; 




---------- table_storage.txt ----------
/*
 * 范例名称：
 * 文件名称：table_storage.txt
 */

CREATE TABLE test(a number(10,2))
PCTFREE 10
PCTUSED 40
STORAGE(
INITIAL 100K 
NEXT 100K 
MINEXTENTS 2 
MAXEXTENTS 100 
PCTINCREASE 100); 

/*解释：
PCTFREE 10%
PCTUSED 40%

初始给test表分配两个Extent，
第一个Extent是100K，因INITIAL=100K
第二个Extent是100K，因NEXT=100K
如果因表内数据增长，需要分配第三个Extent，因PCTINCREASE是100，则
第三个Extent是200K=100K+100K
第四个Extent是400K=200K+200K
*/

--察看storage参数.
select table_name,initial_extent,next_extent,min_extents,max_extents,pct_increase from user_tables
where table_name='TEST';