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';
地瓜园
