Oracle常用命令集

第一节 移动表空间数据文件
1、先使该表空间离线：
alter tablespace users offline;
2、在把该表空间的数据文件搬到另外一个地方：
alter tablesapce users rename datafile 'e:\oracle\oradta\users01.dbf' to 'e:\oracle\oradata\mdb\users01.dbf';
3、使该表空间再上线：
alter tablespace users online;

第二节 数据库操作基本命令
1、增加列
Alter TABLE <表名>
ADD <列名><类型>；
2、删基表
Drop TABLE <表名>；
3、补充主键定义
Alter TABLE <表名>
ADD PRIMARY KEY(<列名表>)；
4、撤销主键定义
Alter TABLE <表名>
Drop PRIMARY KEY;

5、查询语句
Select <列名> FROM <表名> Where condition;
6、比较
<> <= >=
7、时间函数
YEAR MONTH DAY HOURE
8、字符串函数
LENGTH
9、聚集函数
AVG MAX MIN SUM COUNT
10、条件
between IN LIKE EXISTS
11、分组
GROUP BY orDER BY HAVING
12、数据库操纵语言
Insert Insert INTO <表名>
VALUES （<列数据>,<列数据>）；

Create TABLE <表名>
( 列名 类型 非空， 列名 类型 非空 )；

Delete FROM <表名>
Where ..

Update <表名>
SET <表名>=?
Where..

第三节 手工创建命令
1、调整临时表空间：
SQL>alter tablespace temp default storage (initial 128k next 128k maxextents 5000 pctincrease 0)；
2、调整回滚表空间 ：
1）、先将建库工具缺省设定的若干个回滚段删除
SQL>alter rollback segment r01 offline；
SQL>drop rollback segment r01；
2）、根据实际需要创建回滚段（如r01-r10），供联机处理和批处理使用
SQL>create rollback segment r01 storage（initial 128k next 128k maxextents 5000 ptimal 5M） tablespace rbs；
SQL>alter rollback segment r01 online； #注意修改$ORACLE_HOME/dbs/initoradb.ora中的激活回滚段段名，如果在创建回滚段时
使用create public rollback segment，则不需要在$ORACLE_HOME/dbs/initoradb.ora中用rollback_
segment=(…)选项激活，推荐使用public方式
3）、另创建一个尺寸无限制的回滚段（r99），供特殊用途
SQL>create rollback segment r99 storage（initial 128k next 128k maxextents 5000） tablespace rbs；
4）、相关系统表：
SQL>select segment_name, initial_extent, next_extent, max_extents, extents,bytes from dba_segments where segment_type=’ROLLBACK’; #回滚段
占用空间状况
SQL>select segment_name, status from dba_rollback_segs; #回滚段状态


第四节 给用户分配表空间
1、使test用户在表空间users的使用限额为100M：
alter user test quota 100m on users;

第五节 给用户赋予权限
GRANT
名称
GRANT — 赋予一个用户，一个组或所有用户访问权限

GRANT privilege [, ...] ON object [, ...] TO { PUBLIC | GROUP group | username }
输入
privilege
可能的权限有：
Select
访问声明的表/视图的所有列/字段．
Insert
向声明的表中插入所有列字段．
Update
更新声明的所有列/字段．
Delete
从声明的表中删除所有行．
RULE
在表/视图上定义规则 （参见 Create RULE 语句）．
ALL
赋予所有权限．
object
赋予权限的对象名．可能的对象是：
table （表）
view （视图）
sequence （序列）
index （索引）
PUBLIC
代表是所有用户的简写．
GROUP group
将要赋予权限的组 group ．目前的版本中，组必须是用下面方法显式创建的．
username
将要赋予权限的用户名．PUBLIC 是代表所有用户的简写．
输出
CHANGE
如果成功，返回此信息．
ERROR: ChangeAcl: class "object" not found
如果所声明的对象不可用或不可能对声明的组或用户赋予权限．
描述
GRANT 允许对象的创建者给某用户或某组或所有用户（PUBLIC）某些特定的权限．对象创建后，除了创建者外，除非创建者赋予（GRANT）权限，其他人没有访问对象的权限．

一旦用户有某对象的权限，他就可以使用那个特权．不需要给创建者赋予（GRANT）对象的权限，创建者自动拥有对象的所有权限，包括删除它的权限．

注意
目前，要想在 Postgres 里面只赋予几列权限，你必须创建一个包含那几列的视图（view），然后把权限赋予那几个视图。

使用 psql \z 命令获取关于现存对象权限的更多信息：

Database = lusitania +------------------+---------------------------------------------+ | Relation | Grant/Revoke Permissions | +------------------+---------------------------------------------+ | mytable | {"=rw","miriam=arwR","group todos=rw"} | +------------------+---------------------------------------------+ Legend: uname=arwR -- privileges granted to a user group gname=arwR -- privileges granted to a GROUP =arwR -- privileges granted to PUBLIC r -- Select w -- Update/Delete a -- Insert R -- RULE arwR -- ALL
小技巧： 目前，要创建一个 GROUP （组）， 你将不得不手工向表 pg_group 中插入数据，像：

Insert INTO pg_group VALUES ('todos');Create USER miriam IN GROUP todos;
参考 REVOKE 语句重新分配访问权限.

用法
给所有用户向表 films 插入记录的权限：

GRANT Insert ON films TO PUBLIC;
赋予用户 manuel 操作视图 kinds 的所有权限：

GRANT ALL ON kinds TO manuel;
兼容性
SQL92
SQL92 GRANT 语法允许对表中的某单独列/字段设置权限，并且允许设置一权限以赋予别人相同权限．

GRANT privilege [, ...] ON object [ ( column [, ...] ) ] [, ...] TO { PUBLIC | username [, ...] } [ WITH GRANT OPTION ]
这些字段与 Postgres 实现是兼容的，除了下面一些例外：

privilege
SQL92 允许声明附加的权限：
Select
　
REFERENCES
允许在一个声明的表的整合约束中使用某些或全部列/字段．
USAGE
允许使用一个域，字符集，集合或事务．如果声明的对象不是表/视图， privilege 只能声明为 USAGE．
object
[ TABLE ] table
SQL92 允许一个附加的非函数关键字 TABLE.
CHARACTER SET
允许使用声明的字符集．
COLLATION
允许使用声明的集合序列．
TRANSLATION
允许使用声明的字符集转换．
DOMAIN
允许使用声明的域．
WITH GRANT OPTION
允许向别人赋予同样权限.

该文章转载自网络大本营：http://www.xrss.cn/Info/14624.Html


第 六 节 给表增加列
1、给已存在的表中增加一列，并可以为空：
SQL>alter table tablename add colum_name colunm_type null;

第七节 多工重做日志
1、确定现有的日志组成员：
SQL>conn / as sysdba
SQL>col member format a30
SQL>select group#,member from v$logfile;
2、增加日志成员：
SQL>alter database add logfile member 'e:\oradata\redo01_2.log' to group 1,'e:\ooradata\redo02_2.log' to group 2,'e:\ooradata\redo03_2.log' to group 3;

第八节 多工控制文件
1、确定现有 控制文件的情况：
SQL>conn / as sysdba
SQL>selct name from v$controlfile;
2、初始化参数control_files：
SQL>alter system set control_files='E:\oradata\orcl\CONTROL01.CTL','E:\oradata\orcl\CONTROL02.CTL','E:\oradata\orcl\CONTROL03.CTL''E:\oradata\orcl\CONTROL04.CTL' s cope=spfile;
3、关闭数据库并添加控制文件：
SQL>shutdown immediate
SQL>host copy E:\oradata\orcl\CONTROL01.CTL E:\oradata\orcl\CONTROL04.CTL;
4、打开数据库：
SQL> startup

第九节 动态修改processes数
动态修改数据库的processes数：
SQL>alter system set processes=400 scope = spfile;
SQL>shutdown immediate
SQL>startup
然后在查看processes的数量，应该已经修改过
SQL>show parameter processes;

第十节 从数据库备份文件恢复数据文件
back01.dbf为表空间back的文件，现在把它从'e:\oracle\oradata\' 搬到'e:\oracle\oradata\mdb\'下。具体操作如下：
条件：
1、事先有备份好的数据库文件。
2、数据库在archivelog模式下。
3、先把数据库启动到mount状态下，执行下面命令：

RMAN> run{
2> allocate channel d1 type disk;
3> set newname for datafile 'e:\oracle\oradata\back01.dbf' to 'e:\oracle\oradata\mdb\back01.dbf';
4> restore tablespace back;
5> switch datafile 'e:\oracle\oradata\mdb\back01.dbf';
6> recover tablespace back;
7> release channel d1;}

3、然后带开数据库即可：

RMAN> run{sql 'alter database open ';}

第十一 创建表空间
创建表空间：
Create TABLESPACE "BMS_DATA"
DATAFILE '/u01/oradata/bms/bms.ora' SIZE 100M
AUTOEXTEND ON NEXT 100M
SEGMENT SPACE MANAGEMENT AUTO

创建临时表空间：
Create TEMPORARY TABLESPACE "BMS_TEMP"
TEMPFILE '/u01/oradata/bms/dfd.ora' SIZE 100M
AUTOEXTEND ON NEXT 10M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M

第十二 查看某用户的数据对象
1、先用该用户连上数据库：
SQL>conn test/test
2、执行下面语句：
SQL>select * from user_tables;
3、查看该用户的所有对象：
SQL>select object_type,created,status,object_name from user_objects;

第十三 查看需要恢复的数据文件
1、如果某个数据文件的begin scn和end scn号不一致，则该数据文件需要恢复。

SQL>select a.name,a.checkpoint_change# "begin scn",b.checkpoint_change# "end scn"
from v$datafile_header a,v$datafile b
where a.file#=b.file#;

NAME begin scn end scn
---------------------------------------- ---------- ----------
E:\ORADATA\ORCL\SYSTEM01.DBF 846608 846608
E:\ORADATA\ORCL\UNDOTBS01.DBF 846608 846608
E:\ORADATA\ORCL\SYSAUX01.DBF 846608 846608
E:\ORADATA\ORCL\USERS01.DBF 846608 846608
E:\ORADATA\ORCL\EXAMPLE01.DBF 846608 846608
E:\ORADATA\ORCL\MENG01.DBF 846608 846608
E:\ORADATA\ORCL\MENG02.DBF 846608 846608

已选择7行。

第十四 sysdba连接时提示磁盘空间已满解决方案
1、当试图连接到oracle的时候，报以下错误：
SQL> conn / as sysdba
ERROR:
orA-09817: Write to audit file failed.
orA-09945: Unable to initialize the audit trail file
SVR4 Error: 28: No space left on device

2、用df -h查看磁盘空间确实是磁盘空间已满：
[root@sghpubapp03 logs]# df -h
Filesystem 总大小 已用大小 剩余大小剩余空间%
/dev/mapper/VolGroup00-LogVol00
228G 207G 0G 100% /
/dev/cciss/c1d0p1 99M 13M 82M 13% /boot
none 1014M 0 1014M 0% /dev/shm
3、用"du -sk /dirname"来查看目录的大小，找到占用空间特别大的目录，然后删除里面的内容
例：查output目录的占用空间大小
[root@sghpubapp03 logs]# du -sk output/
227530788 output/
4、重新连接数据库
[oracle@sghpubapp03 bin]$ ./sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 29 14:30:24 2007

Copyright (c) 1982, 2005, oracle. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
orACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220820 bytes
Variable Size 184553260 bytes
Database Buffers 415236096 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.

OK，问题解决！！！


第 十 五 r e s t o r e 和 r e c o v e r 的 区 别
restore 是把数据文件覆盖原来的
recover是应用归档日志或者redo log 以及UNDO 回滚


第 十 六 限制select语句查询数量
1、只看100行：
select * from big_table where rownum=100;

第十七 增加表空间大小
alter tablespace users add datafile '/home/oracle/oracle/product/10.2.0/oradata/ams/users02.dbf' size 300m autoextend on next 100m maxsize 500M;

第十八 限制oracle登录用户数
1、alter system set resource_limit = true;
2、SQL> create profile low_limits limit sessions_per_user 2;
配置文件已创建
3、SQL> alter user test profile low_limits;
用户已更改。
4、察看用户test的profile
SQL> select profile from dba_users where username='TEST';
PROFILE
------------------------------
LOW_LIMITS

5、之后开三个窗口分别用test帐户登陆。test帐户分别在3个窗口可以同时登陆
SQL> select sid,username from v$session where username='TEST';

第十九 修改表空间名字
修改一个表空间的名字：
1 alter database datafile 'aaa' offline;
2 in OS mv aaa to bbb
3 alter database rename file 'aaa' to 'bbb';
4 alter database datafile 'bbb' online;

第二十 删除重复纪录
把重复纪录全部删除掉，不保留任何数据

delete from Table
where rowID in
(
select RowID from Table
where Content = 'Duplicated Content'
and RowID <>
(
select RowID from
(
select RowID from Table
where Content = 'Duplicated Content'
order by RowID
) Where RowNum <= 1
)

)

第二十一 不同用户之间相互的exp和imp
10g里可以使用remap_schema和remap_tablespace显示指定，9i的话需要动点脑筋了~

总的来说，Oracle并没有提供什么参数让你将数据导入到指定的表空间中，数据默认还是导入到它原来所在的表空间。
你可以用IMP 的SHOW参数可以看到其创建脚本中会指定TABLESPACE参数，而这个值是原系统该对象所在的表空间，
跟你用户默认的表空间是没有关系的，如下：
"Create TABLE "EMPLOYEES" ("EMPLOYEE_ID" NUMBER(6, 0), "FIRST_NAME" VARCHAR2"
"(20), "LAST_NAME" VARCHAR2(25) NOT NULL ENABLE, "EMAIL" VARCHAR2(25) NOT NU"
"LL ENABLE, "PHONE_NUMBER" VARCHAR2(20), "HIRE_DATE" DATE NOT NULL ENABLE, ""
"JOB_ID" VARCHAR2(10) NOT NULL ENABLE, "SALARY" NUMBER(8, 2), "COMMISSION_PC"
"T" NUMBER(2, 2), "MANAGER_ID" NUMBER(6, 0), "DEPARTMENT_ID" NUMBER(4, 0)) "
"PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIS"
"TS 1 FREELIST GROUPS 1) TABLESPACE "EXAMPLE" LOGGING NOCOMPRESS"
. . 正在跳过表 "EMPLOYEES"
"Create TABLE "JOBS" ("JOB_ID" VARCHAR2(10), "JOB_TITLE" VARCHAR2(35) NOT NU"
"LL ENABLE, "MIN_SALARY" NUMBER(6, 0), "MAX_SALARY" NUMBER(6, 0)) PCTFREE 1"
"0 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREE"
"LIST GROUPS 1) TABLESPACE "EXAMPLE" LOGGING NOCOMPRESS"
. . 正在跳过表 "JOBS"
看其中的红色部分，如果在原系统中你创建EMPLOYEES指定的表空间是EXAMPLE，而此时该用户的默认表空间是USERS，
那么EXP的DMP文件里是EXAMPLE，而不是USERS，当然如果你建表时没特意指定表空间，那么默认的就会是用户的默认表空间。
所以对于这个问题的解决方法是：
1． 如果目标系统中不存在跟原系统一样的表空间，这个一样，是指存储你要导入的数据的表空间
如：你要导入的数据在原系统中是存放在USERS表空间的，而在目标系统并不存在这个表空间，那么你在导入数据时数据就会导入
到目标系统中该用户的默认表空间。
2． 如果存在一样的表空间，则在目标系统中：
a) REVOKE UNLIMITED TABLESPACE FROM 该USER
b) 取消该用户在原系统导出数据所在表空间的配额：
SQL>Alter USER XXX QUOTA 0 ON OLD_TABLESPACE
c) 将你要存储导入数据的表空间设为该用户默认的表空间
d) 添加该用户在其默认表空间中的配额：
SQL>Alter USER XXX QUOTA UNLIMITED ON NEW_TABLESPACE


3． 其他方法：
a) 可以用IMP的SHOW=Y将创建脚本SPOOL出来，然后修改其创建脚本中的TABLESPACE，将其修改成你所需要的表空间。
b) 用第三方工具，比如TOAD，产生其创建脚本，然后修改TABLESPACE值，然后导入的时候加IGNORE=Y进行导入。
c) 可以先导入数据，然后用TOAD的Rebuild Multi Objects，进行数据转移。


imp icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n
commit=y buffer=65536 feedback=100000 ignore=n volsize=0 file=/dev/rmt0
log=imp_icdmain_yyyymmdd.log
tables=commoninformation,serviceinfo

第二十二 检查索引漏洞
select v$session_longops.time_remaining,v$session_longops.opname, v$session_longops.start_time,v$session_longops.last_update_time,v$session_longops.elapsed_seconds,((v$session_longops.elapsed_seconds / (v$session_longops.elapsed_seconds +v$session_longops.time_remaining))*100) pct_complete, V$SQL.SQL_TEXT
from v$session_longops, V$SQL
where v$session_longops.SQL_ADDRESS=V$SQL.ADDRESS and v$session_longops.time_remaining <> 0order by V$SQL.SQL_TEXT, v$session_longops.last_update_time;

第二十三 检查表的替代
Actually, try checking this table instead:
select sql_text, count(sql_text) from v$open_cursor group by sql_text order by count(sql_text);

第二十四 给表增加空间
给表emplees分配100K空间，空间来自'e:\oracle\oradata\mdb\users02.dbf'数据文件：

alter table test.emplees
allocate extent (size 100K datafile 'e:\oracle\oradata\mdb\users02.dbf');

第二十五 查月份
select add_months(sysdate,-1) from dual;-----查当前时间上一个月
select add_months(sysdate,-0) from dual;-----查当前月时间

第二十六 查询表空间和数据文件对应关系
查询数据文件和表空间的对应情况：
select t.name tablespace_name,d.status,d.name file_directory from v$tablespace t,v$datafile d where t.ts#=d.file#;

第二十七 windows下启动emctl
1、先设定ORACLE_SID
SET orACLE_SID=ORCL
2、再启动emctl：
emctl start dbconsole

第二十八 查看各表空间使用情况
select ff.s tablespace_name,
ff.b total,
(ff.b - fr.b) usage,
fr.b free,
round((ff.b - fr.b) / ff.b * 100) || '%' usagep
from (select tablespace_name s, sum(bytes) / 1024 / 1024 b
from dba_data_files
group by tablespace_name) ff,
(select tablespace_name s, sum(bytes) / 1024 / 1024 b
from dba_free_space
group by tablespace_name) fr
where ff.s = fr.s;

第二十九 oracle自动杀session
1、把概要文件的两个参数：连接时间和空闲时间设置为需要的值，单位为分钟。
2、把参数：resource_limit=false设为true；
alter system set resource_limit=true;
3、在$ORACLE_HOME/network/admin/sqlnet.ora文件中添加：
sqlnet.expire_time = 10 --10分钟


第三十 oracle字符集相关参数
1、查看字符集命令：
select * from v$nls_parameters;

结果：
sys@ORCL>select * from v$nls_parameters;

PARAMETER VALUE
---------------------------------------------------------------- -------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ￥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY ￥
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

已选择19行。

第三十一 oracle重建passwoed文件
背景情况：
E:\oracle\database下的PWDorcl.ora文件被删除，数据库无法从emctl控制台登陆。
Linux下在$ORACLE_HOME/dbs下，文件名为：orapwSID
步骤：
1、先把参数remote_login_passwordfile设为none，
show parameter remote_login_passwordfile可以看到系统默认的值为：exclusive
然后修改成none：
alter system set remote_login_passwordfile=none scope=spfile;
2、关闭数据库：
shutdown immediate
3、在操作系统命令行重建password文件：
orapwd file=e:\oracle\database\pwdorcl.ora password=admin entries=20
4、再把参数remote_login_passwordfile改为exclusive
alter system set remote_login_passwordfile=exclusice;

第三十二 oracle用户密码丢失
如果oralce用户的密码丢失，可以用一下方法解决：

1、先用sqlpllus登陆到oracle
$sqlplus /nolog

2、用管理员用户登陆
sql>conn / as sysdba

3、用下面的sql语句设定新密码
alter user username identified by newpassword;

第三十三 oracle修改系统表架构
1、把参数o7_dictionary_accessibility设为false：
alter system set o7_dictionary_accessibility=false;
2、修改成功后，系统表架构不允许修改。
注意：是o7,不是07。

第三十四 设置新创建数据文件存放路径
1、设置新创建数据文件的存放地址：
alter system set db_create_file_dest='e:\';

第三十五 10G收回回收站数据
10G中恢复放到回收站里的表：
flashback table tablename to before drop;

第三十六 创建位图索引
1、建立位图索引，说明该列的值不经常改动：
create bitmap index on test(x);

第三十七 创建job
declare
jobnum integer;
begin
sys.dbms_job.submit(
job=>jobnum, -- 作业号
what=>'your_procedure;', -- 作业执行的存储过程
next_date=>sysdate, -- 首次执行的时间
interval=>'trunc(sysdate)+1/24' -- 时间间隔
);
commit;
end;


第三十八 查看数据库dbname
show parameter db_name

第三十九 查看sql执行时间
1、设置参数：
set timing on;
2、执行SQL语句时即可看到执行时间。


第四十 给表增加extent
1、给表加一个extent：
alter table tablename allocate extent;
2、在某个数据文件上给表加一个extent：
alter table tablename allocate extent(datafile '………' 10M);

第四十一 oracle copy命令
有这个命令，不过效果没有CTAS好，占用资源也比ctas多，唯一的好处是不需要dblink.还有就是对一些特殊类型的数据做转换比如long.



SQL> copy from mesw/test@test_nexus to mesw/test@test_nexus create test_tab_dst(name) using select name from sys.test_tab_sor

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table TEST_TAB_DST created.

257 rows selected from mesw@test_nexus.
257 rows inserted into TEST_TAB_DST.
257 rows committed into TEST_TAB_DST at mesw@test_nexus.

SQL>

SQL> SET ARRAYSIZE 1000
SQL> SET COPYCOMMIT 1000
SQL> SET COPYTYPECHECK OFF
SQL> SET LONG 100000

其中每次读取数据数组大小有SET的ARRAYSIZE参数决定。每次提交的数据量由SET的COPYCOMMIT参数控制。COPY是否进行类型检查由SET的COPYTYPECHECK参数控制。COPY执行时LONG类型截取长度由SET的LONG参数控制。如果不希望LONG类型被截断，则保证LONG的值超过表中LONG类型的最长值。


第四十二 清空回收站
清空某用户的回收站：purge recyclebin;要用回收站的所属用户登录，清空当前用户的回收站。


第 四 十 三 授权使用表空间
1、授权用户使用表空间大小：
alter user jeff quto 2M on users;

第四十四 常用sql语句
0、设置参数：
set timing on;
执行SQL语句时即可看到执行时间。

1、检查系统IO是否存在问题：
[root@localhost ~]#sar -u 2 10

2、关注一下内存
[root@localhost ~]# vmstat

3、找到点用系用资源特别大的Oracle的session及其执行的语句
Select a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text from v$session a,v$process b,v$sqltext c Where b.spid='ORCL' AND b.addr=a.paddr AND a.sql_address=c.address(+)order BY c.piece

4、查找前十条性能差的sql
Select * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10；

5、查看占io较大的正在运行的session
Select se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE, se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes FROM v$session se, v$session_wait st,v$sess_io si,v$process pr
　　Where st.sid=se.sid AND st.sid=si.sid
　　AND se.PADDR=pr.ADDR
　　AND se.sid>6
　　AND st.wait_time=0
　　AND st.event NOT LIKE '%SQL%'
　　ORDER BY physical_reads DESC

6、查看buffer中的命中率：
select 1-(sum(decode(name, 'physical reads', value, 0))/(sum(decode(name, 'db block gets', value, 0))+(sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio" from v$sysstat;

7、查询Shared Pool空闲率：
select to_number(v$parameter.value) value, v$sgastat.BYTES,
(v$sgastat.bytes/v$parameter.value)*100 "percent free"
from v$sgastat, v$parameter
where v$sgastat.name= 'free memory'
and v$parameter.name = 'shared_pool_size'
and v$sgastat.pool='shared pool';

8、找到点用系用资源特别大的Oracle的session
Select a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text
from v$session a,v$process b,v$sqltext c
Where b.spid='ORCL' AND b.addr=a.paddr AND a.sql_address=c.address(+)order BY c.piece

9、删除表中的重复数据
delete from table_name a where rowid > ( select min(rowid) from table_name b where b.pk_column_1 = a.pk_column_1 and b.pk_column_2 = a.pk_column_2 );

10、怎样计算一个表占用的空间的大小？
select owner，table_name, num_rows,blocks*aaa/1024/1024 "size m",empty_blocks,last_analyzed
from dba_tables
where table_name= xxx ;

here: aaa is the value of db_block_size ;
xxx is the table name you want to check

11、如何查看最大会话数：
show parameter processes
select * from v$license;
其中sessions_highwater纪录曾经到达的最大会话数

12、 快速做一个和原表一样的备份表
create table new_table as (select * from old_table);

13、chr()的反函数是ascii()
select char(65) from dual;
select ascii( a ) from dual;

14、字符串的连接
select concat(col1，col2) from table ;
select col1''col2 from table ;

15、修改表名
alter table old_table_name rename to new_table_name;



1、查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
from dba_data_files
order by tablespace_name;

3、查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024)
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;

4、查看控制文件
select name from v$controlfile;

5、查看日志文件
select member from v$logfile;

6、查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;

Select A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
Where A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、查看数据库库对象
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

8、查看数据库的版本　
Select version FROM Product_component_version
Where SUBSTR(PRODUCT,1,6)='Oracle';

9、查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;

10、捕捉运行很久的SQL
column username format a12
column opname format a16
column progress format a8
select username,sid,opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,sql_text
from v$session_longops , v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value

11、查看数据表的参数信息
Select partition_name, high_value, high_value_length, tablespace_name,
pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extent, max_extent, pct_increase, FREELISTS, freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size, last_analyzed
FROM dba_tab_partitions
Where table_name = :tname AND table_owner = :towner
orDER BY partition_position

12、查看还没提交的事务
select * from v$locked_object;
select * from v$transaction;

13、查找object为哪些进程所用
select p.spid, s.sid, s.serial# serial_num, a.type object_type,
s.osuser os_user_name, a.owner, a.object object_name, decode(sign(48 - command), 1, to_char(command), 'Action Code #' || to_char(command) ) action,
p.program oracle_process, s.terminal terminal, s.program program, s.status session_status
from v$session s, v$access a, v$process p
where s.paddr = p.addr and
s.type = 'USER' and
a.sid = s.sid and
a.object='SUBSCRIBER_ATTR'
order by s.username, s.osuser

14、回滚段查看
select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,
v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes, sys.dba_rollback_segs.status status
from v$rollstat, sys.dba_rollback_segs, v$rollname
where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and
v$rollstat.usn (+) = v$rollname.usn order by rownum

15、耗资源的进程（top session）
select s.schemaname schema_name, decode(sign(48 - command), to_char(command), 'Action Code #' || to_char(command) ) action, status session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num, nvl(s.username, '[Oracle process]') user_name, s.terminal terminal, s.program program, st.value criteria_value
from v$sesstat st, v$session s , v$process p
where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL' or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

16、查看锁（lock）情况
select /*+ RULE */ ls.osuser os_user_name, ls.username user_name, decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type, o.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null) ,lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2
from sys.dba_objects o, ( select s.osuser, s.username, l.type, l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s, v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner <> 'SYS' order by o.owner, o.object_name

17、查看等待（wait）情况
Select v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value
FROM v$waitstat, v$sysstat Where v$sysstat.name IN ('db block gets', 'consistent gets') group by v$waitstat.class, v$waitstat.count

18、查看sga情况
Select NAME, BYTES FROM SYS.V_$SGASTAT orDER BY NAME ASC

19、查看catched object
Select owner, name, db_link, namespace, type, sharable_mem, loads, executions, locks, pins, kept
FROM v$db_object_cache

20、查看V$SQLAREA
Select SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS, VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS, USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS, BUFFER_GETS, ROWS_PROCESSED
FROM V$SQLAREA

21、查看object分类数量
select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity
from sys.obj$ o
where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select
'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from

22、按用户查看object种类
select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes, sum(decode(o.type#, 2, 1, NULL)) tables, sum(decode(o.type#, 3, 1, NULL)) clusters, sum(decode(o.type#, 4, 1, NULL)) views, sum(decode(o.type#, 5, 1,NULL)) synonyms, sum(decode(o.type#, 6, 1, NULL)) sequences, sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))
from sys.obj$ o, sys.user$ u
where o.type# >= 1 and u.user# = o.owner# and u.name <> 'PUBLIC' group by u.name order by sys.link$ union select 'CONSTRAINT' , count(*) from sys.con$

23、有关connection的相关信息
1）查看有哪些用户连接
select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command), 'Action Code #' || to_char(command) ) action, p.program oracle_process,
status session_status, s.terminal terminal, s.program program, s.username user_name, s.fixed_table_sequence activity_meter, '' query, 0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num
from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'
order by s.username, s.osuser
2）根据v.sid查看对应连接的资源占用等情况
select n.name, v.value, n.class, n.statistic#
from v$statname n, v$sesstat v
where v.sid = 71 and v.statistic# = n.statistic#
order by n.class, n.statistic#
3）根据sid查看对应连接正在运行的sql
select /*+ PUSH_SUBQ */ command_type, sql_text, sharable_mem, persistent_mem, runtime_mem, sorts, version_count, loaded_versions, open_versions, users_opening, executions, users_executing, loads, first_load_time, invalidations, parse_calls, disk_reads, buffer_gets, rows_processed, sysdate start_time, sysdate finish_time, '>' || address sql_address, 'N' status
from v$sqlarea
where address = (select sql_address from v$session where sid = 71)

24、查询表空间使用情况
select a.tablespace_name "表空间名称",
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",
round(a.bytes_alloc/1024/1024,2) "容量(M)",
round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
Largest "最大扩展段(M)",
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"
from (select f.tablespace_name, sum(f.bytes) bytes_alloc, sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes from dba_data_files f group by tablespace_name) a, (select f.tablespace_name, sum(f.bytes) bytes_free from dba_free_space f
group by tablespace_name) b, (select round(max(ff.length)*16/1024,2) Largest, ts.name tablespace_name from sys.fet$ ff, sys.file$ tf,sys.ts$ ts where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts# group by ts.name, tf.blocks) c where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name

25、 查询表空间的碎片程度
select tablespace_name,count(tablespace_name)
from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;

26、启动TNS监听器：
./lstnctl start

27、测试服务名(在/u01/app/oracle.............../bin下执行)
./tnsping 服务名

28、启动和关闭oracle10g enterprise manager consle
启动和关闭oracle10g enterprise manager consle
要从客户端访问ORACLE enterprise manager console ,服务器端的dbconsole进程必须运行.

启动和闭 dbconsole进程命令:

进入ORACLE_HOME/bin目录
启动:./emctl start dbconsole
关闭:./emctl stop dbconsole
查看状态:./emctl status dbconsole
访问oracle Enterprise Manager Datebase Control

在浏览器中输入URL http://hostname:portnumber/em(端口号纪录在$ORACLE_HOME/install/portlist.ini文件中)
使用数据库帐户登入

29、启动数据库
$ sqlplus /nolog
SQL> connect system/change_on_install as sysdba
SQL> startup

30、自动启动与关闭

编辑 /etc/oratab ，把所有的 instance 的重启动标志设置成 'Y'，如:
orcl:/oracle/product/dbse:Y

做一个启动脚本 /etc/init.d/dbora ，如下所示:

#!/bin/sh
# description: oracle auto start-stop script.
# chkconfig: - 20 80
#
# Set orA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set orA_OWNER to the user id of the owner of the
# oracle database in orA_HOME.

orA_HOME=/oracle/product/dbse
orA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start')

# Start the oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values

su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
;;
'stop')

# Stop the oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
;;
'restart')
$0 stop
$0 start
;;
esac

赋予执行权限
chmod 750 /etc/init.d/dbora

作成以下链接:
ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora
ln -s /etc/init.d/dbora /etc/rc3.d/S99dbora

执行以下命令:
chkconfig --level 345 dbora on

这样就OK了。下次开关机的时候，Oracle也会随之启动/停止。

31、在ORACLE_HOME/bin下启动dbconsole：
./emctl start dbconsole

32、 sqlplus “/ as sysdba”
SQL>startup mount

33、--先把列加上
alter table 你的表 add 列名 int

--再生成该列的值
declare @i int
set @i=0
update 你的表 set @i=@i+1,列名=@i

--注意,这样的方法仅对表中现有的数据有效,新增加的数据不会自动赋值.

34、表空间相邻碎片的接合:
alter tablespace 表空间名 coalesce;

第四十五 学习总结
2006.11.28
您应该掌握的内容：
? oracle引入逻辑结构的目的。
? oracle物理结构和逻辑结构的区别。
? oracle主要存储结构之间的关系。
? 表空间的分类。
? 数据字典管理的表空间的维护和管理。
? 本地管理的表空间的维护和管理。
? 还原表空间的维护和管理。
? 临时表空间的维护和管理。
? 默认临时表空间的设置与管理。
? 表空间的联机与脱机状态之间的切换。
? 表空间的只读与正常状态之间的切换。
? 怎样改变表空间的存储设置。
? 怎样重置表空间的大小。
? 怎样移动可以脱机的数据文件。
? 怎样移动不能脱机的数据文件。
? 数据字典与本地管理的表空间之间的迁移。
? 怎样删除表空间。
? 怎样在删除表空间语句中使用INCLUDING CONTENTS AND DATAFILES子句。
? 怎样利用OMF来管理和维护表空间。

一、表空间的创建和检查（举例中表空间名为：jinlian）

1、创建一个非系统表空间:Create TABLESPACE 表空间名
[DATAFILE子句]
[MINIMUM EXTENT 正整数[K|M]]
[BLOCKSIZE正整数[K]]
[LOGGING|NOLOGGING]
[DEFAULT 存储子句]
[ONLINE|OFFLINE]
[PERMANENT|TEMPORARY]
[区段管理子句]
[段管理子句]

2、数据库中究竟有多少个表空间以及它们的状态
SQL> col tablespace_name for a15

SQL> select tablespace_name, status, contents
2 from dba_tablespaces;

3、创建名为jinlian(金莲)的表空间。
例SQL> Create TABLESPACE jinlian
2 DATAFILE 'J:\DISK2\MOON\JINLIAN01.DBF' SIZE 50 M,
3 'J:\DISK4\MOON\JINLIAN02.DBF' SIZE 50 M
4 MINIMUM EXTENT 50K EXTENT MANAGEMENT DICTIONARY
5 DEFAULT STORAGE (INITIAL 50K NEXT 50K MAXEXTENTS 100 PCTINCREASE 0);
注：当需要磁盘空间的自动分配时第一次分配为50K（INITIAL 50K），第二次也为50K（NEXT 50K），所分配的最大磁盘空间为100个 EXTENTS（MAXEXTENTS 100）。从第三次分配开始按如下的公式进行分配：NEXT * (1+PCTINCREASE/100)(n-2)。其中n为分配的 次数。

4、对输出进行了格式化
SQL>SET LINE 120
SQL> COL TABLESPACE_NAME FOR A15

5、查某表空间是否是一个数据字典管理的表空间
SQL> select tablespace_name, initial_extent, next_extent,
2 max_extents, pct_increase, min_extlen
3 from dba_tablespaces;

6、验证表空间与文件有关的信息
SQL> select file_id, file_name, tablespace_name
2 from dba_data_files
3 order by file_id;

7、创建名为jinlian_index的索引表空间
SQL> Create TABLESPACE jinlian_index
2 DATAFILE 'J:\DISK6\MOON\jinlian_index.dbf'
3 SIZE 50 M
4 EXTENT MANAGEMENT LOCAL
5 UNIFORM SIZE 1M;

8、验证所创建的jinlian_index索引表空间是不是本地管理
SQL> Select tablespace_name, block_size, extent_management, segment_space_management
2 FROM dba_tablespaces
3 Where tablespace_name LIKE 'JIN%';

9、验证所创建的jinlian_index索引表空间的磁盘存储参数
SQL> select tablespace_name, initial_extent, next_extent,
2 max_extents, pct_increase, min_extlen
3 from dba_tablespaces
4 Where tablespace_name LIKE 'JIN%';

10、验证所创建的jinlian_index索引表空间与文件有关的信息
SQL> Select file_id, file_name, tablespace_name, autoextensible
2 FROM dba_data_files
3 Where file_id > 5
4 order by file_id;

二、表空间的还原

1、创建所需的还原表空间
SQL> Create UNDO TABLESPACE jinlian_undo
2 DATAFILE 'J:\DISK7\MOON\jinlian_undo.DBF'
3 SIZE 20 M;

2、使用SQL查询语句来获取相关的信息，验证所创建的还原表空间jinlian_undo到底是数据字典管理还是本地管理
SQL> Select tablespace_name, block_size, extent_management, segment_space_management
2 FROM dba_tablespaces
3 Where tablespace_name LIKE 'JIN%';

3、确认表空间jinlian_undo到底是不是还原表空间
SQL> Select tablespace_name, status, contents
2 FROM dba_tablespaces
3 Where tablespace_name LIKE 'JIN%';

三、临时表空间

1、查看临时表空间和对应的数据文件
SQL> col file for a50
SQL> Select f.file#, t.ts#, f.name "File", t.name "Tablespace"
2 FROM v$tempfile f, v$tablespace t
3 Where f.ts# = t.ts#;

2、创建所需的临时表空间
SQL> Create TEMPORARY TABLESPACE jinlian_temp
2 TEMPFILE 'J:\DISK8\MOON\jinlian_temp.dbf'
3 SIZE 10 M
4 EXTENT MANAGEMENT LOCAL
5 UNIFORM SIZE 2 M;

3、验证该表空间是否已经建立，是否为临时表空间，以及所对应的数据文件是否也被创建
SQL> select tablespace_name, status, contents
2 from dba_tablespaces
3 where tablespace_name LIKE 'JIN%';

SQL> Select f.file#, t.ts#, f.name "File", t.name "Tablespace"
2 FROM v$tempfile f, v$tablespace t
3 Where f.ts# = t.ts#;

SQL> Select tablespace_name, block_size, extent_management,
2 segment_space_management, min_extlen
3 FROM dba_tablespaces
4 Where tablespace_name LIKE 'JIN%';

4、查询当前的默认临时表空间
SQL> COL PROPERTY_NAME FOR A25
SQL> COL PROPERTY_VALUE FOR A16
SQL> COL DESCRIPTION FOR A38

SQL> Select *
2 FROM DATABASE_PROPERTIES
3 Where PROPERTY_NAME LIKE 'DEFAULT%';

5、将jinlian_temp临时表空间设为默认临时表空间
SQL> Alter DATABASE
2 DEFAULT TEMPORARY TABLESPACE jinlian_temp;

6、验证现在的默认临时表空间是否为jinlian_temp
SQL> Select *
2 FROM DATABASE_PROPERTIES
3 Where PROPERTY_NAME LIKE 'DEFAULT%';

7、将默认临时表空间再改回为TEMP表空间
SQL> Alter DATABASE
2 DEFAULT TEMPORARY TABLESPACE temp;

8、验证一下现在的默认临时表空间是否为temp
SQL> Select *
2 FROM DATABASE_PROPERTIES
3 Where PROPERTY_NAME LIKE 'DEFAULT%';

四、设置表空间为脱机

1、首先获取该表空间和它对应的数据文件现在的状态
SQL> select tablespace_name, status, contents
2 from dba_tablespaces
3 where tablespace_name LIKE 'JIN%';

SQL> col name for a55
SQL> Select file#, name, status
2 FROM v$datafile
3 Where file# >= 8;

2、将表空间JINLIAN置为脱机状态
SQL> Alter TABLESPACE jinlian OFFLINE;

3、查看该表空间和它对的数据文件现在的状态
SQL> select tablespace_name, status, contents
2 from dba_tablespaces
3 where tablespace_name LIKE 'JIN%';

SQL> Select file#, name, status
2 FROM v$datafile
3 Where file# >= 8;

4、将表空间JINLIAN置回为联机状态
SQL> Alter TABLESPACE jinlian ONLINE;

5、验证一下以上命令是否成功
SQL> select tablespace_name, status, contents
2 from dba_tablespaces
3 where tablespace_name LIKE 'JIN%';

SQL> Select file#, name, status
2 FROM v$datafile
3 Where file# >= 8;

五、只读（Read-only）表空间

1、将jinlian表空间的状态改为只读
SQL> Alter TABLESPACE jinlian READ ONLY;

2、验证一下以上命令是否成功
SQL> select tablespace_name, status, contents
2 from dba_tablespaces
3 where tablespace_name LIKE 'JIN%';

3、需要重新向JINLIAN表空间中输入数据时，将表空间的状态改回为可读可写（正常）
SQL> Alter TABLESPACE jinlian READ WRITE;

4、验证一下以上命令是否成功地将表空间JINLIAN改回为可读可写（正常）状态
SQL> select tablespace_name, status, contents
2 from dba_tablespaces
3 where tablespace_name LIKE 'JIN%';

注：以上操作可在报警文件中查阅。

六、改变表空间的配置参数

1、Alter TABLESPACE 表空间名
[MINIMUM EXTENT 正整数[K|M]
|DEFAULT 存储子句 ]

2、将jinlian表空间的存储参数做如下的修改：MINIMUM EXTENT为100K；INITIAL和NEXT也都为100K,而MAXEXTENTS为200
SQL> Alter TABLESPACE jinlian MINIMUM EXTENT 100 K;

SQL> Alter TABLESPACE jinlian
2 DEFAULT STORAGE ( INITIAL 100 K NEXT 100 K MAXEXTENTS 200 );

3、查看修改的表空间JINLIAN的磁盘存储参数是否成功
SQL> SET LINE 120

SQL> COL tablespace_name FOR a15

SQL> select tablespace_name, initial_extent, next_extent,
2 max_extents, pct_increase, min_extlen
3 from dba_tablespaces
4 Where tablespace_name LIKE 'JIN%';

七、重置表空间的大小

1、先确定哪些表空间或数据文件可以自动扩展
SQL> Select file_id, tablespace_name, file_name, autoextensible
2 FROM dba_data_files
3 Where file_id >= 8
4 orDER BY file_id;

2、让JINLIAN_INDEX表空间的大小在达到最大值时可以自动扩展
SQL> Alter DATABASE DATAFILE
2 'J:\DISK6\MOON\JINLIAN_INDEX.DBF' AUTOEXTEND ON
3 NEXT 1 M;

3、查看JINLIAN_INDEX表空间和所对应的数据文件是否已经可以自动扩展
SQL> Select file_id, tablespace_name, file_name, autoextensible
2 FROM dba_data_files
3 Where file_id >= 8
4 orDER BY file_id;

八、手工重置数据文件大小

1、查看JINLIAN表空间所对应的数据文件的尺寸
SQL> Select file_id, file_name, tablespace_name,
2 bytes/(1024*1024) MB
3 FROM dba_data_files
4 Where tablespace_name LIKE 'JIN%'
5 orDER BY tablespace_name;

2、将数据文件J:\DISK2\MOON\JINLIAN01.DBF的尺寸加大到100M
SQL> Alter DATABASE DATAFILE 'J:\DISK2\MOON\JINLIAN01.DBF' RESIZE 100 M;

3、查看JINLIAN表空间所对应的数据文件J:\DISK2\MOON\JINLIAN01.DBF的尺寸是否已经成功地改为100M
SQL> Select file_id, file_name, tablespace_name,
2 bytes/(1024*1024) MB
3 FROM dba_data_files
4 Where tablespace_name LIKE 'JIN%'
5 orDER BY tablespace_name;

4、通过向JINLIAN表空间中添加一个新的数据文件的方式来增加该表空间的尺寸
SQL> Alter TABLESPACE jinlian
2 ADD DATAFILE 'J:\DISK6\MOON\JINLIAN03.DBF'
3 SIZE 80 M;

5、检查是否成功向JINLIAN表空间中加入了一个大小为80M的新数据文件J:\DISK6\MOON\JINLIAN03.DBF
SQL> Select file_id, file_name, tablespace_name,
2 bytes/(1024*1024) MB
3 FROM dba_data_files
4 Where tablespace_name LIKE 'JIN%'
5 orDER BY tablespace_name;

九、移动数据文件的方法

1、把脱机状态下没有活动的还原数据或临时段的非系统表空间中的数据文件移动到其他盘（只修改控制文件中指向数据文件的指针( 地址)）
Alter TABESPACE 表空间名
RENAME DATAFILE ‘文件名'[, ‘文件名']...
TO ‘文件名'[, ‘文件名']...

2、将系统表空间和不能置为脱机的表空间中的数据文件移动到其他盘（要求在使用这条语句时，数据库必须运行在加载（MOUNT）状态而且 目标数据文件必须存在。因为该语句只修改控制文件中指向数据文件的指针(地址)）
Alter DATABASE [数据库名]
RENAME FILE ‘文件名'[, ‘文件名']...
TO ‘文件名'[, ‘文件名']...

十、移动非系统表空间USERS和INDX数据文件的应用实例

1、以SYSTEM用户登录数据库系统，用SQL*Plus的格式化命令使显示更清晰
SQL> col file_name for a45
SQL> set line 120

2、查数据文件的分布情况，看看它们是否存在I/O竞争(所有的数据文件存在一个盘上时，该盘I/O会很大)
SQL> select file_id, file_name, tablespace_name
2 from dba_data_files
3 where file_name LIKE '%ORADATA%'
4 order by file_id;

3、了解表空间当前的状态
SQL> Select tablespace_name, status, contents
2 FROM dba_tablespaces
3 Where tablespace_name NOT LIKE '%JIN%';

4、将USERS表空间和INDX表空间的状态改为脱机（OFFLINE）
SQL> Alter TABLESPACE USERS OFFLINE;

SQL> Alter TABLESPACE INDX OFFLINE;

5、验证对这两个表空间状态的修改是否真的成功
SQL> Select tablespace_name, status, contents
2 FROM dba_tablespaces
3 Where tablespace_name NOT LIKE '%JIN%';

6、使用操作系统命令将表空间USERS所对应的数据文件D:\ORACLE\ORADATA\ORACLE9I\USERS01.DBF复制为D:\DISK2\ORADATA\USERS01.DBF， 和例6-70的操作系统命令将D:\ORACLE\ORADATA\ORACLE9I\INDX01.DBF复制为D:\DISK4\ORADATA\INDX01.DBF

SQL> HOST COPY D:\ORACLE\ORADATA\ORACLE9I\USERS01.DBF D:\DISK2\ORADATA
SQL> HOST COPY D:\ORACLE\ORADATA\ORACLE9I\INDX01.DBF D:\DISK4\ORADATA

7、重新命名表空间USERS和INDX所对应的数据文件名（在控制文件中修改了指向这些文件的地址或指针）
SQL> Alter TABLESPACE users RENAME
2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\USERS01.DBF
3 TO 'D:\DISK2\ORADATA\USERS01.DBF';

SQL> Alter TABLESPACE indx RENAME
2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\INDX01.DBF'
3 TO 'D:\DISK4\ORADATA\INDX01.DBF';

8、将USERS表空间和INDX表空间的状态重新改回为联机（ONLINE）

SQL> Alter TABLESPACE users ONLINE;
SQL> Alter TABLESPACE indx ONLINE;

9、验证一下对这两个表空间状态的修改是否真的成功
SQL> Select tablespace_name, status, contents
2 FROM dba_tablespaces
3 Where tablespace_name NOT LIKE '%JIN%';

10、验证表空间USER和INDX所对应的操作系统数据文件是否已经指向了新的文件
SQL> select file_id, file_name, tablespace_name
2 from dba_data_files
3 where file_name LIKE '%ORADATA%'
4 order by file_id;

11、利用操作系统工具，如NT资源管理器找到了旧的应该废弃的物理文件。之后在操作系统中删除了这些垃圾文件

十一、移动系统表空间数据文件的应用实例

1、切换到SYSDBA用户关闭数据库（否则无法关闭数据库）

SQL> CONNECT SYS/ORACLE AS SYSDBA
SQL> SHUTDOWN IMMEDIATE

2、启动实例（例程）并将数据库置为加载（MOUNT）状态

SQL> STARTUP MOUNT

3、将真正的物理数据文件D:\ORACLE\ORADATA\ORACLE9I\SYSTEM01.DBF复制为D:\DISK1\ORADATA\SYSTEM01.DBF

SQL> HOST COPY D:\ORACLE\ORADATA\ORACLE9I\SYSTEM01.DBF D:\DISK1\ORADATA

4、验证真正的物理文件是否真的生成（直接查看该文件即可）

5、重新命名表空间SYSTEM所对应的数据文件名（在控制文件中修改了指向这个文件的地址或指针）

SQL> Alter DATABASE RENAME
2 FILE 'D:\ORACLE\ORADATA\ORACLE9I\SYSTEM01.DBF'
3 TO 'D:\DISK1\ORADATA\SYSTEM01.DBF';

6、将数据库的状态置为打开（OPEN）

SQL> Alter DATABASE OPEN;

7、使用SQL查询语句来验证他所作的移动是否成功

SQL> select file_id, file_name, tablespace_name
2 from dba_data_files
3 where file_name LIKE '%ORADATA%'
4 order by file_id;

8、利用操作系统工具，如NT资源管理器来找到旧的应该废弃的物理文件SYSTEM01.DBF。之后在操作系统中删除了这些垃圾文件

十二、迁移数据字典和本地管理的表空间

1、以SYSDBA角色登录数据库

SQL> CONNECT SYS/ORACLE AS SYSDBA

2、看一下哪些表空间是数据字典管理的


SQL> Select tablespace_name, block_size, extent_management,
2 segment_space_management, min_extlen
3 FROM dba_tablespaces
4 Where tablespace_name LIKE 'JIN%';

3、使用过程调用将数据字典管理的JINLIAN表空间迁移为本地管理的表空间

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('JINLIAN');

4、验证一下您是否已经成功地将数据字典管理的JINLIAN表空间迁移成了本地管理的表空间

SQL> Select tablespace_name, block_size, extent_management,
2 segment_space_management, min_extlen
3 FROM dba_tablespaces
4 Where tablespace_name LIKE 'JIN%';

5、又可以使用TABLESPACE_MIGRATE_FROM_LOCAL过程调用将本地管理的JINLIAN表空间重新迁移为数据字典管理的表空间

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('JINLIAN');

6、查询语句验证一下您是否已经成功地将本地管理的JINLIAN表空间迁移成了数据字典管理的表空间

SQL> Select tablespace_name, block_size, extent_management,
2 segment_space_management, min_extlen
3 FROM dba_tablespaces
4 Where tablespace_name LIKE 'JIN%';

十三、删除表空间

1、删除表空间的SQL命令格式

Drop TABLESPACE tablespace
[INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]]

2、使用SQL查询语句找到要删除的表空间和它们所对应的数据文件

SQL> Select file_id, file_name, tablespace_name
2 FROM dba_data_files
3 Where file_id > 5
4 orDER BY file_id;

3、删除表空间JINLIN

SQL> Drop TABLESPACE jinlian;

4、验证一下您是否真的成功地删除表空间JINLIN（删除的只是控制文件中指向数据文件的指针，而不是数据文件本身）

SQL> Select file_id, file_name, tablespace_name
2 FROM dba_data_files
3 Where file_id > 5
4 orDER BY file_id;

5、利用操作系统工具，如NT资源管理器来找到旧的应该废弃的物理文件。之后在操作系统中删除了这三个垃圾文件。

6、使用带有INCLUDING CONTENTS AND DATAFILES子句的Drop TABLESPACE命令来删除表空间JINLIAN_INDEX和JINLIAN_UNDO

SQL> Drop TABLESPACE JINLIAN_INDEX INCLUDING CONTENTS AND DATAFILES;
SQL> Drop TABLESPACE JINLIAN_UNDO INCLUDING CONTENTS AND DATAFILES;

7、验证一下您是否真的成功地删除了表空间JINLIAN_INDEX和JINLIAN_UNDO

SQL> Select file_id, file_name, tablespace_name
2 FROM dba_data_files
3 Where file_id > 5
4 orDER BY file_id;

8、使用SQL查询语句来获得临时表空间和它们对应的数据文件

SQL> col file for a50
SQL> col tablespace for a15
SQL> set line 120

SQL> Select f.file#, t.ts#, f.name "File", t.name "Tablespace"
2 FROM v$tempfile f, v$tablespace t
3 Where f.ts# = t.ts#;

9、使用带有INCLUDING CONTENTS AND DATAFILES子句的Drop TABLESPACE命令删除表空间JINLIN_TEMP

SQL> Drop TABLESPACE JINLIAN_TEMP INCLUDING CONTENTS AND DATAFILES;

10、使用查询语句来验证一下您是否真的成功地删除了临时表空间JINLIAN_TEMP

SQL> Select f.file#, t.ts#, f.name "File", t.name "Tablespace"
2 FROM v$tempfile f, v$tablespace t
3 Where f.ts# = t.ts#;

注：INCLUDING CONTENTS AND DATAFILES子句是Oracle9i引入的，带有INCLUDING CONTENTS AND DATAFILES子句删除表空间的命令在删 除表空间的同时也删除真正的操作系统文件。

十四、利用 OMF来管理表空间

1、先应该以SYSTEM或SYS用户登录,之后就可以使用SQL命令设定数据文件存放的目录

SQL> Alter SYSTEM SET
2 db_create_file_dest = 'D:\DISK5\ORADATA';

2、使用不带文件名子句的创建表空间语句来创建一个新的表空间guifei

SQL> Create TABLESPACE guifei;

3、接下来为表空间guifei添加一个新文件，其大小为50M,也与上一例中的数据文件放在同一目录下

SQL> Alter TABLESPACE guifei ADD DATAFILE SIZE 50 M;

4、验证表空间GUIFEI和它的两个数据文件是否都已生成

SQL> Select file_id, file_name, tablespace_name
2 FROM dba_data_files
3 Where file_id > 5
4 orDER BY file_id;

5、获得所生成表空间GUIFEI和她所对应的两个数据文件大小等信息

SQL> Select file_id, file_name, tablespace_name,
2 bytes/(1024*1024) MB
3 FROM dba_data_files
4 Where tablespace_name LIKE 'GUI%';

6、将刚刚创建的表空间GUIFEI从数据库中删除掉

SQL> Drop TABLESPACE guifei;

7、验证表空间GUIFEI和它的两个数据文件是否真的都已经被删除了

SQL> Select file_id, file_name, tablespace_name,
2 bytes/(1024*1024) MB
3 FROM dba_data_files;

十五、创建表空间的应用实例：为先驱工程创建四个相应的表空间。数据表空间pioneer_data、索引表空间pioneer_indx、还原表空间 pioneer_undo、临时表空间pioneer_temp；与这些表空间所对应的操作系统文件分别是J:\DISK2\MOON\pioneer_data.dbf，J:\DISK4 \MOON\pioneer_indx.dbf，J:\DISK6\MOON\pioneer_undo.dbf，和J:\DISK8\MOON\pioneer_temp.dbf；其大小分别为：100M, 100M, 50M,和50M；而且都为本地管理的表空间

1、首先获得当前数据库中所有表空间和它们所对应的操作系统文件，以及它们的大小的信息

SQL> Select file_id, file_name, tablespace_name,
2 bytes/(1024*1024) MB
3 FROM dba_data_files;

2、查看这些表空间是数据字典管理的还是本地管理，以及它们的数据块大小等信息

SQL> Select tablespace_name, block_size, extent_managemen
2 segment_space_management, min_extlen
3 FROM dba_tablespaces;

3、获得当前数据库中每个临时表空间和与之对应的临时数据文件的信息

SQL> Select f.file#, t.ts#, f.name "File", t.name "Tablespace"
2 FROM v$tempfile f, v$tablespace t
3 Where f.ts# = t.ts#;

4、使用开始创建本地表空间的SQL语句创建所需的表空间

SQL> Create TABLESPACE pioneer_data
2 DATAFILE 'J:\DISK2\MOON\pioneer_data.dbf'
3 SIZE 100 M
4 EXTENT MANAGEMENT LOCAL
5 UNIFORM SIZE 1M;

SQL> Create TABLESPACE pioneer_indx
2 DATAFILE 'J:\DISK4\MOON\pioneer_indx.dbf'
3 SIZE 100 M
4 EXTENT MANAGEMENT LOCAL
5 UNIFORM SIZE 1M;

SQL> Create UNDO TABLESPACE pioneer_undo
2 DATAFILE 'J:\DISK6\MOON\pioneer_undo.dbf
3 SIZE 50 M
4 EXTENT MANAGEMENT LOCAL;

SQL> Create TEMPORARY TABLESPACE pioneer_temp
2 TEMPFILE 'J:\DISK8\MOON\pioneer_temp.dbf'
3 SIZE 50 M
4 EXTENT MANAGEMENT LOCAL
5 UNIFORM SIZE 2 M;

5、查看刚刚所创建的表空间和它们所对应的操作系统文件，以及它们的大小的信息

SQL> Select file_id, file_name, tablespace_name,
2 bytes/(1024*1024) MB
3 FROM dba_data_files
4 Where tablespace_name LIKE 'PION%';

6、刚刚创建的四个表空间是数据字典管理的还是本地管理，以及它们的数据块大小等信息

SQL> Select tablespace_name, block_size, extent_management,
2 segment_space_management, min_extlen, contents
3 FROM dba_tablespaces
4 Where tablespace_name LIKE 'P%';

7、刚刚创建的临时表空间和与之对应的临时数据文件的信息

SQL> Select f.file#, t.ts#, f.name "File", t.name "Tablespace"
2 FROM v$tempfile f, v$tablespace t
3 Where f.ts# = t.ts#
4 AND t.name LIKE 'P%';

第四十六 数据库操作基本命令
1、增加列
Alter TABLE <表名>
ADD <列名><类型>；
2、删基表
Drop TABLE <表名>；
3、补充主键定义
Alter TABLE <表名>
ADD PRIMARY KEY(<列名表>)；
4、撤销主键定义
Alter TABLE <表名>
Drop PRIMARY KEY;

5、查询语句
Select <列名> FROM <表名> Where condition;
6、比较
<> <= >=
7、时间函数
YEAR MONTH DAY HOURE
8、字符串函数
LENGTH
9、聚集函数
AVG MAX MIN SUM COUNT
10、条件
between IN LIKE EXISTS
11、分组
GROUP BY orDER BY HAVING
12、数据库操纵语言
Insert Insert INTO <表名>
VALUES （<列数据>,<列数据>）；

Create TABLE <表名>
( 列名 类型 非空， 列名 类型 非空 )；

Delete FROM <表名>
Where ..

Update <表名>
SET <表名>=?
Where..