oracle查询3年前数据,3年前oracle命令行笔记，到现在还常用

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 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;

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';