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

删除表空间及其表空间内所有的对象

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, 2 created,profile from dba_users where username='TEST'; USER_ID USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE ---------- ------------------------------ -------------------------------- -------------- -------------- ------------------------------ ------------------------------ -------------- ----------- 78 TEST OPEN DULTEST TEMP 28-7月 -10 DEFAULT

将角色connect和resoure授权给dultest用户

grant connect,resource to dultest;

授予dultest创建表的权限

grant create table to dultest;

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

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

删除用户dultest

drop user dultest cascade;

如果被删除的用户在线，提示： drop user dultest cascade * 第 1 行出现错误: ORA-01940: 无法删除当前已连接的用户

等待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 SQL> col tablespace_name format a15 SQL> col table_name format a30 SQL> SELECT o.owner, t.tablespace_name,o.object_id,o.object_name table_name, 2 t.pct_free,t.pct_used,t.ini_trans,t.max_trans,t.initial_extent,t.next_extent, 3 t.min_extents,t.max_extents,t.pct_increase 4 from dba_objects o,dba_tables t 5 WHERE o.object_name = t.table_name and t.OWNER='TEST'; 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 ---------- --------------- ---------- ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ TEST DULTEST 56181 PM_HOU_J_RESMSSQLINSTANCE 10 1 255 20971520 20971520 1 2147483645 0 TEST DULTEST 56179 PM_DAY_J_RESDATAFILE 10 1 255 20971520 20971520 1 2147483645 0 TEST DULTEST 56180 PM_DAY_S_RESCPU 10 1 255 20971520 20971520 1 2147483645 0 TEST DULTEST 56182 PM_HOU_S_RESPROCESS 10 1 255 20971520 20971520 1 2147483645 0 SQL>

由上面的数据可以看出，四张表的initial_extent和next_extent都是20971520（20M）。这是因为在创建表的时候没有指定表的存储属性，而是继承了所属表空间dultest的空间属性。

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

这时的表信息为：

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

可以看到表的这些列NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN的数据都被更新了。

blocks+empty_blocks为2560的整数倍。

行数也是目前最准确的了。

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