3年前oracle命令行笔记，到现在还常用。

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 SYSDBA
SELECT * FROM V$VERSION;

//查看全局服務名
SELECT GLOBAL_NAME FROM GLOBAL_NAME;


//数据库名
SELECT NAME FROM V$DATABASE;
SHOW PARAMETER DB

//实例名
SELECT INSTANCE_NAME FROM V$INSTANCE
SHOW 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  | 
lsnrctl
start listener


关闭监听器   lsnrctl stop listener  | 


显示监听器状态  lsnrctl
status listener


//查看更详细监听器
services


//测试客户端与服务器连接
tnsping myorcl

//登陆
1 sqlplus
2 system@myorcl
admin

//检查网络故障
ping 127.0.0.1

//sql
;结束
/ 新一行结束


begin

end 
/  新行执行 

在sqlplus命令中执行操作系统命令
host  dir d:\


关闭plus
exit or quit
or ctrl+z

//连接数据库
CONNECT  system/admin@myorcl

//退出数据库
DISCONNECT



//导入sql文件
@F:\banksystem.sql




//配置tomcat 环境变量
catalina_home=H:\Tomcat 6.0
tomcat_home=H:\Tomcat 6.0
classpath=%tomcat_home%\lib\servlet-api.jar;tomcat_home%\lib\jsp-api.jar


//创建表空间
CREATE TABLESPACE userdb
DATATILE 'F:\oracl tool\'

 

//切换用户
sqlplus
system
admin
conn scott/admin  --切换
show user;


//断开当前数据库
disc scott;

//修改用户密码
passw


//运行sql脚本
start e:\a.sql 或者 @ e:\a.sql

//编辑sql
edit 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_USERS
WHERE 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_SPACE
GROUP BY TABLESPACE_NAME;


//創建臨時表空間
CREATE TEMPORARY TABLESPACE USER_TEMP
TEMPFILE 'F:\DATA_TEMP.DBF'
SIZE 50M 
AUTOEXTEND ON
NEXT 50M MAXSIZE 10280M
EXTENT MANAGEMENT LOCAL;
 

//創建表空間
CREATE TABLESPACE USER_DATA
DATAFILE 'F:\USER_DATA.DBF'
SIZE 50M
AUTOEXTEND ON
NEXT 50M MAXSIZE 10280M
EXTENT MANAGEMENT LOCAL; 

 
//創建用戶并指定表空間
CREATE USER MYORCL IDENTIFIED BY ADMIN
DEFAULT TABLESPACE  USER_DATA
TEMPORARY TABLESPACE USER_TEMP;

//CONN SYSTEM/ADMIN
//GRANT CONNECT,RESOURCE TO MYORCL




oracle调整表空间文件大小
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 200m
5、修改表空間名
   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、只复制表结构的sql

　　create table b as select * from a where 1<>1

　　2、即复制表结构又复制表中数据的sql

　　create table b as select * from a

　　3、复制表的制定字段的sql

　　create table b as select row_id,name,age from a where 1<>1//前提是row_id,name,age都是a表的列

　　4、复制表的指定字段及这些指定字段的数据的sql

　　create 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('2011-01-01','yyyy-mm-dd hh24:mi:ss') ,30.0);

//对PERSON 的ID进行自增
//創建序列
CREATE SEQUENCE SEQ_ID
START WITH 1  --啟始值
MAXVAULE 99999999999999  --最大值 99999999999999 
MINVAULE 1   --最小值1  
NOCYCLE      --序列到達最大值以後不再循環
CACHE 100;   --緩存100個序列值



CREATE SEQUENCE SEQ_ID
INCREMENT BY 1
START 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)
BEGIN
SELECT SEQ_ID_PERSON.NEXTVAL INTO : NEW.ID FROM DUAL
END;



//創建觸發器
CREATE OR REPLACE TRIGGER TRIGGER_PERSON
 BEFORE INSERT ON PERSON   --插入前觸發
 FOR EACH ROW              --對每行觸發  自動增加
 BEGIN
 SELECT SEQ_ID.NEXTVAL INTO : NEW.ID FROM DUAL; --序列到下個值
END;


//刪除TRIGGER
DROP TRIGGER TRIGGER_PERSON;


//禁用和启用触发器
alter trigger <trigger_name> disable;
alter trigger <trigger_name> 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;
9i
ALTER 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.ZHS16GBK
AMERICAN_AMERICA.UTF8  英文值

AMERICAN_AMERICA;
AMERICAN_AMERICA.WE8ISO8859P1
SIMPLIFIED 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);

// PROCEDURE
CREATE PROCEDURE PROL(ID NUMBER)
 AS
 V NUMBER;
 BEGIN
 INSERT INTO CT
 VALUES(V,'ZHANGSAN',1,1);
 COMMIT;
 END;
 /

//execute執行 
//EXECUTE PROL(2)
//塊執行
DECLARE
BEGIN
PROL(2);
END 

//常量定義constant
ID CONSTANT NUMBER :=30;


//無參存儲過程
CREATE OR REPLACE PROCEDURE PRO_PERSON(PARAMENT1 VARCHAR2,PARAMENT2 NUMBER,PARAMENT3 DATE,PARAMENT4 FLOAT) AS
BEGIN
INSERT INTO PERSON(NAME,AGE,BRITHDAY,MONEY) VALUES(PARAMENT1,PARAMENT2,PARAMENT3,PARAMENT4);
END;

//返回一個參數
CREATE OR REPLACE PROCEDURE PRO_PERSON_RETURE(PARAMENT1 VARCHAR2,PARAMENT2 OUT NUMBER) AS
BEGIN
SELECT 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 AS
TYPE 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这些数据库对象的定义都可以用类似的方法得到。


//TRANSACTION
SAVEPOINT A
SELECT * FROM PERSON;
UPDATE PERSON SET AGE=30 WHERE NAME='ZHANGSAN';
SELECT * FROM PERSON;
ROLLBACK TO A
SELECT * FROM PERSON;


//TRANSACTION'S NAME
SET TRANSACTION NAME 'INSERT INTO TABLE TEST_TRANSACTION'
//INSERT INTO DATABASE
INSERT INTO TABLE TEST_TRANSACTION VALUES('KKKK',20,TO_DATE('2011-10-10','YYYY-MM-DD HH24:MI:SS'),20.8);
// SELECT TRANSACTION
SELECT NAME FROM V$TRANSACTION;

//COMMIT;

SELECT NAME FROM V$TRANSACTION;


//事务一致性(TRANSACTION-LEVEL READ CONSISTENCY)
//会话1 窗口1
//READ ONLY
SET TRANSACTION READ ONLY;

//2
SELECT * FROM PERSON;
//会话2 窗口2
INSERT INTO TABLE TEST_TRANSACTION VALUES('KKKK',20,TO_DATE('2011-10-10','YYYY-MM-DD HH24:MI:SS'),20.8);

COMMIT;

//会话1 窗口1
SELECT * FROM PERSON;

//COMMIT;
SELECT * FROM PERSON;


//INTEGRITY CONSTRAINTS 完整性约束

//CREATE TABLE
CREATE TABLE INTEGRITY_TEST(
ID INT,
NAME VARCHAR2(10));

//ALTER COLUMN OF TABLE FOR PRIMARY KEY 
ALTER TABLE INTEGRITY_TEST ADD PRIMARY KEY(ID,NAME);
//DELETE
ALTER TABLE INTEGRITY_TEST DROP PRIMARY KEY CASCADE;

//ALTER COLUMN OF TABLE FOR PRIMARY KEY AND CONSTRAINT NAME
ALTER TABLE INTEGRITY_TEST ADD CONSTRAINT PK1 PRIMARY KEY(ID,NAME);
//DELETE 
ALTERT TABLE INTEGRITY_TEST DROP PK1;


//CREATE UNIQUE CONSTRAINT
DROP TABLE INTEGRITY_TEST;
 

//1 
CREATE TABLE INTEGRITY_TEST(ID INT UNIQUE,NAME VARCHAR2(10));

//2
ALTER TABLE INTEGRITY_TEST ADD UNIQUE(ID,NAME);
//DELETE
ALTER TABLE INTEGRITY_TEST DROP UNIQUE(ID,NAEM);

//3
ALTER TABLE INTEGRITY_TEST ADD CONSTRAINT PK3 UNIQUE(ID,NAME);

//DELETE
ALTER TABLE INTEGRITY_TEST DROP CONSTRAINT PK3 CASCADE;

//CREATE CHECK CONSTRAINTS
DROP TABLE INTEGRITY_TEST;

//CREATE CHECK CONSTRAINT OF TABLE
CREATE 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 COLLEGE
CREATE TABLE COLLEGE(
CID INT,
CNAME VARCHAR2(10),
CADDRESS VARCHAR2(100),
CONSTRAINT PK2 PRIMARY KEY(CID));


//CONSTRAINTS FOREIGN KEY
ALTER TABLE STUDENT ADD
CONSTRAINT FK1
FOREIGN KEY(CID)
REFERENCES COLLEGE(CID);

//OR CONSTRAINT FOREIGN KEY
ALTER TABLE STUDENT ADD
CONSTRAINT FK1
FOREIGN 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';