个人oracle初学笔记

//修改会话格式；
alter session set nls_date_format='yyyy-mm-dd';

//用系统管理员，查看当前数据库有几个用户连接：

SQL> select username,sid,serial# from v$session;

//如果要停某个连接用

SQL> alter system kill session sid,serial#;


//启动或停止监听；
lsnrctl start/stop;

//启动或停止实例；
net start/stop oracleserviceORA10G;

//约束字段输入内容
alter table stu modify sex char（2）check（sex in ('男'，‘女’)）；

------------------------------------------------------------------------------------------------------------------------------------------------------------
//删除表中的一个字段；
alter table stu drop column XX;

//修改表中的一个字段
alter table [表名] rename column 旧的字段名 to 新的字段名;
alter table [表名] modify column not null、、、;
//修改表所属表空间
alter table table_name move tablespace tablespace_name;
-------------------------------------------------------------------------------------------------------------------------------------------------------------

//用户加锁就是不让被加锁的用户连接数据库;
alter user test account lock;

//约束(constraint)
主键约束 -- 每个表要有主键，唯一的标识一行数据 primary key;
非空约束 not null;
唯一性约束 unique;
外键约束 references 外键表的字段；
检查约束 check(字段 in/between and);

//在加约束的同时给约束取名：
alter table student add constrait 约束名 check(sex in('男','女'))

//删除约束：
alter table student drop constraint 约束名;

//撤销对象权限
revoke select on stu from ll;

//创建用户并分配表空间；
create user mven identified by OracleUserMven
default tablespace mven
temporary tablespace mven_temp;

//删除用户；
drop user army cascade

//查看一个用户有哪些权限；
select * from dba_tab_privs/dba_role_privs where grantee=upper('ll');

GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO 用户名字 ----------------------------------------用户可以赋于他什么权限；


//decode用法
select a.studentid,studentname,subjectname,decode(sign(mark-60),1,'及格',-1,'不及格',0,'刚好') from 表;

//删除表时。应该注意的要点；
drop table books;的指令会将表放到回收站里用 。
flashback table "BIN$1Oiy3qm/QJubov1BwBUOgw==$0" to before drop;//就能恢复原来被你删除的表。
删除表：
drop table books purge;是绕过回收站，彻底删除
建议你先
purge recyclebin;
清除当前用户的回收站，不会影响其他用户的回收站

//创建序列（sequence）-----------------------------------------------------------------------------------------------第三天里有讲；
create sequence seq1 with start(初始值) 0 increment(步长) by 1 maxvalue 100 minvalue 0 cycle（到100后回到1开始）；

用法：select seq1.nextval,studentid from student;

//删除序列；
drop sequence seq1；

//同义词
create synonym mtest for mk.test; //为表建立的
//可以为任何对象建立同义词
select * from mtest; //相当于select * from mk.test;

//查数据库中所有的用户；
select user#,name,password from user$;

------------------------------------------------------------------------------------------------------------------------------------------------------------
//数据导出：
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)

4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"

上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面 加上 compress=y 来实现。

-------------------------------------------------------------------------------------------------------------------------------------------------------------
//数据的导入
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:\daochu.dmp
imp aichannel/aichannel@HUST full=y file=file= d:\data\newsmgnt.dmp ignore=y
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 将d:\daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:\daochu.dmp tables=(table1)

//通过explain plan for可以查看到语句的执行速度；-----------------------------------------
exp: explain plan for select * from stu;
select * from table(DBMS_XPLAN.DISPLAY);


SET SERVICEOUTPUT ON; ------------使用DBMS_OUTPUT.PUT_LINE();先启动这个服务；
-------------------------------------------------------------------------------------------------------------------------------------------------------------

设置保存点：SAVEPOINT 名字； 回退保存点：ROLLBACK TO 名字；


查看过的源码：SELECT text FROM user_source WHERE name='过程名';

查看当前用户所有信息用:user_objects 包括：表、视图、索引、过程、函数和包等。查询类别：object_type

用show errors可以显示错误的原因和位置。如show errors procedure 过程名；

//恢复本机某个时间点的数据库
recover database until time '2008-06-19 13:45:02'; /*恢复*/

//配置归档文件位置：

查看系统是否处于归档模式：archive log list;

//切换归档模式
alter database archivelog;---开启归档；
alter database noarchivelog;----关闭归档；

1.可以配置LOG_ARCHIVE_DEST和LOG_ARCHIVE_DUPLEX_DEST，最多只能配置两个归档位置；
2.可以配置LOG_ARCHIVE_DEST_n，最多可以配10个。可以是本地的，也可以是远程的。但不能和上面两个一起使用。
例子：
ALTER SYSTEM SET log_archive_duplex_dest='';
ALTER SYSTEM SET log_archive_dest='';
ALTER SYSTEM SET log_archive_dest_1='location=/oracle/oracle10g/log/archive_log';
ALTER SYSTEM SET log_archive_dest_2='location=/oracle/oracle10g/log2/archive_log';
ALTER SYSTEM SET log_archive_dest_4='service=standby'; //配置远程归档位置时,SERVICE选项需要指定远程数据库的网络服务名(在tnsnames.ora文件中配置);

数据库只读设置：
1.在打开时用：startup open read only;
2.在mount下设置（不能在open下修改）:alter database open read only;

查看某表空间中包含有哪些表:
select table_name from dba_tables where tablespace_name=upper('表空间名');

查看字符集：
select * from sys.nls_database_parameters;--查看数据库参数
select * from sys.nls_instance_parameters; --查看客户端参数
select * from sys.nls_session_parameters; --查看会话的参数

如何禁止某IP访问ORACLE？

　　在$ORACLE_HOME/network/admin/中创建protocol.ora文件╋其?容如下?

　　tcp.validnode_checking=yes

　　#下面两行取其一：

　　tcp.invited_nodes=(ip1,ip2,....) #允许访问的IP

　　tcp.excluded_nodes=(ip1,ip2,...) #禁止访问的IP

关闭操作系统认证方式：
在sqlnet.ora中开一行
sqlnet.authentication_services=(none);

oracle 优化器优化方式：
4种方式：rule,choose,first_rows,all_rows；
含义：
Rule:不用多说,即走基于规则的方式。

　　Choolse:这是我们应观注的,默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。

　　First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。

　　All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。
可通过两种方式设置：
1.修改init.ora文件中的optimizer_mode=rule;
2.alter session set optimizer_mode=rule;