Oracle 基本操作命令學習

---------------------------------------------------------------------------

---- 本文為andkylee個人原創，請在尊重作者勞動成果的前提下進行轉載；

---- 轉載務必註明原始出處
:
http://blog.csdn.net/andkylee

---


2010-07-28
16:06:16

---- 關鍵字：  oracle 基本命令 tablespace user table purge analyze

----------------------------------------------------------------------------

建立資料表空間

create tablespace dultest datafile 'e:/oracle/oradata/orcl/dultest.dbf'
size 100m autoextend on next 100m maxsize unlimited
default storage(

                        initial 20M

                        next 20M

                        minextents 1

                        maxextents unlimited

                        pctincrease 0

);

資料表空間僅有一個資料檔案，資料檔案初始大小為100m，以後增長幅度為100m，不限制上限。資料表空間dultest內的對象的預設的空間分配資訊為：對象初始大小為20m，後續空間增長幅度為20m。

通過系統檢視表查看錶空間的資訊：

SQL> set linesize 2000<br />SQL> col tablespace_name format a16<br />SQL> col datafile_name format a40<br />SQL> select ts.ts#,ts.name tablespace_name,file#, df.name datafile_name,block_size,blocks,bytes,status,enabled<br /> 2 from v$tablespace ts,v$datafile df<br /> 3 where ts.ts# = df.ts# and ts.name='DULTEST';<br /> TS# TABLESPACE_NAME FILE# DATAFILE_NAME BLOCK_SIZE BLOCKS BYTES STATUS ENABLED<br />---------- ---------------- ---------- ---------------------------------------- ---------- ---------- ---------- ------- ----------<br /> 8 DULTEST 6 E:/ORACLE/ORADATA/ORCL/DULTEST.DBF 8192 25600 209715200 ONLINE READ WRITE<br />SQL><br />

刪除資料表空間及其資料表空間內所有的對象

drop tablespace dultest including contents and datafiles;

建立使用者dultest並授權

create user dultest identified by "db"
default tablespace "DULTEST"
temporary tablespace temp
profile default
account unlock;

SQL> select user_id,username,account_status,lock_date,expiry_date,default_tablespace,temporary_tablespace,<br /> 2 created,profile from dba_users where username='TEST';<br /> USER_ID USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE<br />---------- ------------------------------ -------------------------------- -------------- -------------- ------------------------------ ------------------------------ -------------- -----------<br /> 78 TEST OPEN DULTEST TEMP 28-7月 -10 DEFAULT<br />

將角色connect和resoure授權給dultest使用者

grant connect,resource to dultest;

授予dultest建立表的許可權

grant create table to dultest;

SQL> SELECT * FROM DBA_ROLE_PRIVS<br /> 2 WHERE GRANTEE='TEST';<br />GRANTEE GRANTED_ROLE ADM DEF<br />------------------------------ ------------------------------ --- ---<br />TEST RESOURCE NO YES<br />TEST CONNECT NO YES

SQL> SELECT * FROM DBA_SYS_PRIVS<br /> 2 WHERE GRANTEE='TEST';<br />GRANTEE PRIVILEGE ADM<br />------------------------------ ---------------------------------------- ---<br />TEST UNLIMITED TABLESPACE NO<br />

刪除使用者dultest

drop user dultest cascade;

如果被刪除的使用者線上，提示：<br />drop user dultest cascade<br />*<br />第 1 行出現錯誤:<br />ORA-01940: 無法刪除當前已串連的使用者<br />

等待dultest退出時，再執行刪除操作。

合并資料表空間的片段

alter tablespace dultest coalesce;

查詢系統資源回收筒的被刪除的對象

select * from sys.recyclebin$;

清楚資源回收筒的對象

purge table dultest.aaa;

把用exp匯出的使用者dultest的對象匯入到使用者test中

imp system/db@192.168.2.178  file=c:/dultest_tblspace.dmp fromuser=dultest touser=test ignore=y

查看錶的一些資訊

SELECT o.owner, t.tablespace_name,o.object_id,o.object_name table_name,o.created,o.last_ddl_time,o.status,
t.num_rows,t.blocks,t.empty_blocks,t.avg_space,t.avg_row_len,  /*空間使用資訊*/
t.pct_free,t.pct_used,t.ini_trans,t.max_trans,t.initial_extent,t.next_extent,
t.min_extents,t.max_extents,t.pct_increase
from dba_objects o,dba_tables t
WHERE o.object_name =  t.table_name and t.OWNER='TEST';

SQL> col owner format a10<br />SQL> col tablespace_name format a15<br />SQL> col table_name format a30<br />SQL> SELECT o.owner, t.tablespace_name,o.object_id,o.object_name table_name,<br /> 2 t.pct_free,t.pct_used,t.ini_trans,t.max_trans,t.initial_extent,t.next_extent,<br /> 3 t.min_extents,t.max_extents,t.pct_increase<br /> 4 from dba_objects o,dba_tables t<br /> 5 WHERE o.object_name = t.table_name and t.OWNER='TEST';<br />OWNER TABLESPACE_NAME OBJECT_ID TABLE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE<br />---------- --------------- ---------- ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------<br />TEST DULTEST 56181 PM_HOU_J_RESMSSQLINSTANCE 10 1 255 20971520 20971520 1 2147483645 0<br />TEST DULTEST 56179 PM_DAY_J_RESDATAFILE 10 1 255 20971520 20971520 1 2147483645 0<br />TEST DULTEST 56180 PM_DAY_S_RESCPU 10 1 255 20971520 20971520 1 2147483645 0<br />TEST DULTEST 56182 PM_HOU_S_RESPROCESS 10 1 255 20971520 20971520 1 2147483645 0<br />SQL><br />

由上面的資料可以看出，四張表的initial_extent和next_extent都是20971520（20M）。這是因為在建立表的時候沒有指定表的儲存屬性，而是繼承了所屬資料表空間dultest的空間屬性。

SQL> col owner format a10<br />SQL> col tablespace_name format a15<br />SQL> col table_name format a30<br />SQL> SELECT o.owner, t.tablespace_name,o.object_id,o.object_name table_name,o.created,o.last_ddl_time,o.status,<br /> 2 t.num_rows,t.blocks,t.empty_blocks,t.avg_space,t.avg_row_len /*空間使用資訊*/<br /> 3 from dba_objects o,dba_tables t<br /> 4 WHERE o.object_name = t.table_name and t.OWNER='TEST';<br />OWNER TABLESPACE_NAME OBJECT_ID TABLE_NAME CREATED LAST_DDL_TIME STATUS NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN<br />---------- --------------- ---------- ------------------------------ -------------- -------------- ------- ---------- ---------- ------------ ---------- -----------<br />TEST DULTEST 56181 PM_HOU_J_RESMSSQLINSTANCE 28-7月 -10 28-7月 -10 VALID 31982 172 0 0 33<br />TEST DULTEST 56179 PM_DAY_J_RESDATAFILE 28-7月 -10 28-7月 -10 VALID 165823 905 0 0 32<br />TEST DULTEST 56180 PM_DAY_S_RESCPU 28-7月 -10 28-7月 -10 VALID 17860 100 0 0 32<br />TEST DULTEST 56182 PM_HOU_S_RESPROCESS 28-7月 -10 28-7月 -10 VALID 1536573 8601 0 0 34<br />SQL>

由上面可以看出各個表的行數、使用的blocks、空塊、以及每行的平均長度。但是，目前看到的這些資訊可能是不準確的。

由於表PM_HOU_J_RESMSSQLINSTANCE的預設initial_extent和next_extent都是20M（2560 blocks）。而blocks和empty_blocks的總和不是2560的整數倍。

更行表的統計資訊：

analyze table test.PM_HOU_J_RESMSSQLINSTANCE compute statistics;<br />analyze table test.PM_DAY_J_RESDATAFILE compute statistics;<br />analyze table test.PM_DAY_S_RESCPU compute statistics;<br />analyze table test.PM_HOU_S_RESPROCESS compute statistics;

這時的表資訊為：

SQL> col owner format a10<br />SQL> col tablespace_name format a15<br />SQL> col table_name format a30<br />SQL> SELECT o.owner, t.tablespace_name,o.object_id,o.object_name table_name,o.created,o.last_ddl_time,o.status,<br /> 2 t.num_rows,t.blocks,t.empty_blocks,t.avg_space,t.avg_row_len /*空間使用資訊*/<br /> 3 from dba_objects o,dba_tables t<br /> 4 WHERE o.object_name = t.table_name and t.OWNER='TEST';<br />OWNER TABLESPACE_NAME OBJECT_ID TABLE_NAME CREATED LAST_DDL_TIME STATUS NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN<br />---------- --------------- ---------- ------------------------------ -------------- -------------- ------- ---------- ---------- ------------ ---------- -----------<br />TEST DULTEST 56181 PM_HOU_J_RESMSSQLINSTANCE 28-7月 -10 28-7月 -10 VALID 32400 214 2346 2299 36<br />TEST DULTEST 56179 PM_DAY_J_RESDATAFILE 28-7月 -10 28-7月 -10 VALID 164550 918 1642 944 38<br />TEST DULTEST 56180 PM_DAY_S_RESCPU 28-7月 -10 28-7月 -10 VALID 17860 150 2410 3358 38<br />TEST DULTEST 56182 PM_HOU_S_RESPROCESS 28-7月 -10 28-7月 -10 VALID 1576338 8828 1412 1009 38<br />SQL><br />

可以看到表的這些列NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN的資料都被更新了。

blocks+empty_blocks為2560的整數倍。

行數也是目前最準確的了。

SQL> select count(*) from test.PM_HOU_J_RESMSSQLINSTANCE;<br /> COUNT(*)<br />----------<br /> 32400<br />SQL> select count(*) from test.PM_DAY_J_RESDATAFILE;<br /> COUNT(*)<br />----------<br /> 164550<br />SQL> select count(*) from test.PM_DAY_S_RESCPU;<br /> COUNT(*)<br />----------<br /> 17860<br />SQL> select count(*) from test.PM_HOU_S_RESPROCESS;<br /> COUNT(*)<br />----------<br /> 1576338<br />