col 列名 for(mat) 格式 ：可对列进行格式化（a100 是针对varchar 的格式，显示100个字符，可以a60,a80），例如：col file_name format a100;col 列名：显示当前列的格式clear column:清除所有列的格式//主機名host hostname;//幫助help index;//顯示當前環
col 列名 for(mat) 格式 ：可对列进行格式化（a100 是针对varchar 的格式，显示100个字符，可以a60,a80），例如：col file_name format a100;col 列名：显示当前列的格式clear column:清除所有列的格式//主機名host hostname;//幫助help index;//顯示當前環境變量值show all//顯示當前錯誤show err;//顯示數據庫版本show rel //查看oracle版本conn /as sysdbaselect * from v$version;//查看全局服務名select global_name from global_name;//数据库名select name from v$database;show parameter db//实例名select instance_name from v$instanceshow parameter instance //数据库域名select value from v$parameter where name='db_domain';show parameter domain;//数据库服务名select value from v$parameter where name='service_name';show parameter service_name;登陆监听器 lsnrctl启动监听器 lsnrctl start listener | lsnrctlstart listener关闭监听器 lsnrctl stop listener | 显示监听器状态 lsnrctlstatus listener//查看更详细监听器services//测试客户端与服务器连接tnsping myorcl//登陆1 sqlplus2 system@myorcladmin//检查网络故障ping 127.0.0.1//sql;结束/ 新一行结束beginend / 新行执行 在sqlplus命令中执行操作系统命令host dir d:\关闭plusexit or quitor ctrl+z//连接数据库connect system/admin@myorcl//退出数据库disconnect//导入sql文件@f:\banksystem.sql//配置tomcat 环境变量catalina_home=h:\tomcat 6.0tomcat_home=h:\tomcat 6.0classpath=%tomcat_home%\lib\servlet-api.jar;tomcat_home%\lib\jsp-api.jar//创建表空间create tablespace userdbdatatile 'f:\oracl tool\' //切换用户sqlplussystemadminconn scott/admin --切换show user;//断开当前数据库disc scott;//修改用户密码passw//运行sql脚本start e:\a.sql 或者 @ e:\a.sql//编辑sqledit e:\a.sql;//截取屏幕select 数据到指定文件spool d:\b.sql;select * from emp;spool off;//sql语句的 '&a' 变量值替换select * from emp where ename='&a'; //调整行的顯示set linesize 90;select * from emp;//行显示 show linesize;//分页set pagesize 5;select * from emp;//创建数据库用户create user nokey identified by orcl;//orcl是密码//修改其他数据库用户密码alter user nokey//删除用户drop user nokey;如果nokey 里面存在表，那么需要带一个参数cascade才能删除 //为表空间指定用户 alter user demo default tablespace demotsdata; //给指定用户授权( 表操作 connect )grant connect to nokey;//给指定用户授权( 表空间操作 resource )grant resource to nokey;//操作數據庫grant dba to nokey;//查看表结构desc test;//赋予对象权限查看表grant select on emp to nokey;//查看被赋权的表select * from scott.emp;//soctt 表示角色//赋予对象权限修改表grant update on emp to nokey;//把emp访问权限都给test (修改/删除delete \查询 \添加 soctt的emp 表 )grant all on emp to test//收回nokey对emp表的权限revoke select on emp from nokey;//权限维护//nokey 查询scott emp表的权限传递给其他用户// 查看所有用戶select username from dba_users;//刪除用戶drop user myorcl cascade;//刪除表空間及數據庫文件（刪除臨時表空間和數據庫文件也是一樣的）drop tablespace tablespace_name including contents and datafiles;drop tablespace tablespace_name including contents and datafiles;//查看所有表 select table_name from all_tables //查看當前用戶所有表select table_name from all_tables where owner='myorcl';//查看前用戶的表空间select username,default_tablespace defspace from dba_userswhere username='myorcl'; select username,default_tablespace,temporary_tablespace from dba_users where username='myorcl';//查詢所有表空間select tablespace_name from dba_tablespaces;//查詢表空間分佈的用戶信息select tablespace_name,owner,sum(bytes) from dba_segments group by tablespace_name,owner;//查詢當前用戶所有表空間 select tablespace_name from user_tablespaces;//查看表空間大小 select a.name,sum(b.bytes)/1024/1024 from v$tablespace a,v$datafile b where a.ts#=b.ts# group by a.name;//查詢表空間空閒空間select tablespace_name,sum(bytes)/1024/1024 from dba_free_spacegroup by tablespace_name;//創建臨時表空間create temporary tablespace user_temptempfile 'f:\data_temp.dbf'size 50m autoextend onnext 50m maxsize 10280mextent management local; //創建表空間create tablespace user_datadatafile 'f:\user_data.dbf'size 50mautoextend onnext 50m maxsize 10280mextent management local; //創建用戶并指定表空間create user myorcl identified by admindefault tablespace user_datatemporary tablespace user_temp;//conn system/admin//grant connect,resource to myorcloracle调整表空间文件大小1、调整表空间文件大小 alter database datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' resize 15m;2、调整表空间文件自动扩展 alter database datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' autoextend on next 20m maxsize 1g;3、新增磁盘 alter database datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' autoextend on next 20m maxsize 1g; alter tablespace userdb add datafile '/u03/oradata/users02.dbf' size 50m autoextend on next 10m maxsize 200m5、修改表空間名 alter tablespace userdb rename to userdb2//增加列alter table emp01 add eno number(4);//修改列定义alter table emp01 modify job varchar2(15) ;//删除列alter table emp01 drop column dno;//修改列名alter table emp01 rename column eno to empno;//修改表名rename emp01 to employee;//修改默认表空间到指定表空间alter database default tablespace user_data;//增加注释comment on table employee is '存放雇员信息';comment on table employee.name is '描述雇员姓名';　1、只复制表结构的sqlcreate table b as select * from a where 112、即复制表结构又复制表中数据的sqlcreate table b as select * from a3、复制表的制定字段的sqlcreate table b as select row_id,name,age from a where 11//前提是row_id,name,age都是a表的列4、复制表的指定字段及这些指定字段的数据的sqlcreate table b as select row_id,name,age from a以上语句虽然能够很容易的根据a表结构复制创建b表，但是a表的索引等却复制不了，需要在b中手动建立。5、insert into 会将查询结果保存到已经存在的表中insert into t2(column1, column2, ....) select column1, column2, .... from t1//sql插入时间date类型 insert into person (id,name,age,brithday,money) values( 1,'张三',22, to_date('2011-01-01','yyyy-mm-dd hh24:mi:ss') ,30.0);insert into person (name,age,brithday,money) values( '张三',22, to_date(&#【本文来自鸿网互联 (http://www.68idc.cn)】39;2011-01-01','yyyy-mm-dd hh24:mi:ss') ,30.0);//对person 的id进行自增//創建序列create sequence seq_idstart with 1 --啟始值maxvaule 99999999999999 --最大值 99999999999999 minvaule 1 --最小值1 nocycle --序列到達最大值以後不再循環cache 100; --緩存100個序列值create sequence seq_idincrement by 1start with 1 nomaxvalue nocycle nocache; create or replace trigger tri_userbean before insert on usertest for each row begin if(:new.id is null)then select seq_userbean.nextval into:new.id from dual; end if; end; / create or replace trigger tri_person before insert on person for each row when(new.id is null) begin select seq_id_person.nextval into :new.id from dual end; /create trigger tri_ins_person before insert on person for each row when(new.id is null)beginselect seq_id_person.nextval into : new.id from dualend;//創建觸發器create or replace trigger trigger_person before insert on person --插入前觸發 for each row --對每行觸發 自動增加 begin select seq_id.nextval into : new.id from dual; --序列到下個值end;//刪除triggerdrop trigger trigger_person;//禁用和启用触发器alter trigger disable;alter trigger enable;//查看當前用戶當前表觸發器 select * from user_triggers where table_owner='myorcl' and table_name =upper('person');select * from user_triggers where table_owner = 'xxx' and table_name = upper('table_name'); //查看这个用户的所有触发器select * from dba_triggers where owner=用户名；//查看这个用户中所有的外键约束，table_name显示了建立在哪个表上select * from dba_constraints t where owner=用户名 and t.constraint_type='f';//清空緩存10g以上alter system flush buffer_cache;9ialter session set events 'immediate trace name flush_cache';// 將中文報錯環境更改為英文alter session set nls_language='american'//select * from nls_database_parameters;alter database nls_characterset = zhs16gbk;//註冊表 nls_lang simplified chinese_china.zhs16gbk 中文值chinese_china.zhs16gbkamerican_america.utf8 英文值american_america;american_america.we8iso8859p1simplified american_america.utf8 //提示為英文，編碼為中文american_america.zhs16gbk//ct table create table ct( id number primary key not null, name varchar(10) not null, age number not null, dep number not null);// procedurecreate procedure prol(id number) as v number; begin insert into ct values(v,'zhangsan',1,1); commit; end; ///execute執行 //execute prol(2)//塊執行declarebeginprol(2);end //常量定義constantid constant number :=30;//無參存儲過程create or replace procedure pro_person(parament1 varchar2,parament2 number,parament3 date,parament4 float) asbegininsert into person(name,age,brithday,money) values(parament1,parament2,parament3,parament4);end;//返回一個參數create or replace procedure pro_person_reture(parament1 varchar2,parament2 out number) asbeginselect age into parament2 from person where name=parament1;end;//插入 时取得当前id//第一种 create or replace procedure pro_last_id_person( para5 out number,para1 varchar2,para2 number,para3 date,para4 float) as begin insert into person(id,name,age,brithday,money) values( seq_id_person.nextval,para1,para2,para3,para4); select seq_id_person.currval into para5 from dual; end; ///第二种 create or replace procedure pro_last_id_person1( para5 out number,para1 varchar2,para2 number,para3 date,para4 float) as begin insert into person(name,age,brithday,money) values(para1,para2,para3,para4); select seq_id_person.currval into para5 from dual; end; ///存儲過程返回列表//造包create or replace package person_package astype person_cursor is ref cursor;procedure pro_person_get(c_ref out person_cursor);end;///存儲過程 create or replace package body person_package as procedure pro_person_get(c_ref out person_cursor) is begin open c_ref for select * from person; end pro_person_get; end person_package; ///查看触发器 set long 50000; set heading off; set pagesize 2000; select 'create or replace trigger ' || trigger_name || '' || chr(10)|| decode( substr( trigger_type, 1, 1 ), 'a', 'after', 'b', 'before', 'i', 'instead of' ) || chr(10) || triggering_event || chr(10) || 'on ' || table_owner || '.' || table_name || '' || chr(10) || decode( instr( trigger_type, 'each row' ), 0, null, 'for each row' ) || chr(10) , trigger_body from user_triggers; // 查看函数和过程的状态 select object_name,status from user_objects where object_type='function'; select object_name,status from user_objects where object_type='procedure'; select object_name from user_objects where object_type='trigger'; // 查看函数和过程的源代码 select text from all_source where owner=user and name=upper('名稱'); select text from all_source where type='package body' and owner='system' order by name,line; procedure,function, package, trigger这些数据库对象的定义都可以用类似的方法得到。//transactionsavepoint aselect * from person;update person set age=30 where name='zhangsan';select * from person;rollback to aselect * from person;//transaction's nameset transaction name 'insert into table test_transaction'//insert into databaseinsert into table test_transaction values('kkkk',20,to_date('2011-10-10','yyyy-mm-dd hh24:mi:ss'),20.8);// select transactionselect name from v$transaction;//commit;select name from v$transaction;//事务一致性(transaction-level read consistency)//会话1 窗口1//read onlyset transaction read only;//2select * from person;//会话2 窗口2insert into table test_transaction values('kkkk',20,to_date('2011-10-10','yyyy-mm-dd hh24:mi:ss'),20.8);commit;//会话1 窗口1select * from person;//commit;select * from person;//integrity constraints 完整性约束//create tablecreate table integrity_test(id int,name varchar2(10));//alter column of table for primary key alter table integrity_test add primary key(id,name);//deletealter table integrity_test drop primary key cascade;//alter column of table for primary key and constraint namealter table integrity_test add constraint pk1 primary key(id,name);//delete altert table integrity_test drop pk1;//create unique constraintdrop table integrity_test; //1 create table integrity_test(id int unique,name varchar2(10));//2alter table integrity_test add unique(id,name);//deletealter table integrity_test drop unique(id,naem);//3alter table integrity_test add constraint pk3 unique(id,name);//deletealter table integrity_test drop constraint pk3 cascade;//create check constraintsdrop table integrity_test;//create check constraint of tablecreate table integrity_test(id int,name varchar2(10),num number constraint c_check check(num between 10 and 20));//add check constraint of table alter table integrity_test add constraint c_check check(num between 1 and 20);//delete alter table integrity_test drop constraint c_check;//alter column values is null or not null of table alter table integrity_test modify (id null);alter table integrity_test modify (id not null);//foreign key//create student create table student(sid int,cid int,name varchar2(20),constraint pk1 primary key(sid));//create collegecreate table college(cid int,cname varchar2(10),caddress varchar2(100),constraint pk2 primary key(cid));//constraints foreign keyalter table student addconstraint fk1foreign key(cid)references college(cid);//or constraint foreign keyalter table student addconstraint fk1foreign key(cid)references college(cid) on delete cascade;//delete constraints foreign key//if table is college's delete then alter table college drop constraint fk1 //or useing cascade constraints to delete drop table college cascade constraints;//all tables and all constraints select table_name,constraint_name from user_constraints;// 查看表约束 select constraint_name,constraint_type from user_constraints where table_name=upper('department') and owner=user;//查看值 select column_name,position from user_cons_columns where constraint_name='sys_c0011056';