oracle数据库常用操作

------------------sql语句的操作和帮助----------------------------查看某个命令的帮助信息help show;--查看所有系统变量值信息show all;--查看当前用户show user;--查看SGA大小show sga;--查看错误信息show errors;--查看数据库版本信息show rel;--查看系统初始化参数信息show parameters;/*使用save命令将缓冲区的sql语句保存到单独的文件中  可选参数：cre 默认，创建文件     rep 如果文件存在，则覆盖，不存在则创建文件     app 如果文件存在，则在结尾追加，不存在则创建文件 */save '路径\文件名' cre/rep/app;/*使用get命令将指定的文件中的sql语句读取到sqlplus缓冲区  可选参数： list 默认，列出缓冲区的语句      nolist 不列出缓冲区的语句  */get '路径\文件名' list/nolist;/*使用start命令读取并运行文件的内容 */start '路径和文件名';sta '路径和文件名';@ '路径和文件名'; --三种方法效果一样,单引号也可以省略/*使用edit命令可以将sqlplus缓冲区的内容复制到afiedt.buf的文件中  并启动操作系统默认的编辑工具打开 */edit/ed '文件名' --文件名可省略，默认为afiedt.buf/*使用spool命令将sqlplus中的输出结果复制到指定的文件中  或者把查询结果发送到打印机中，直到使用spool off命令为止  可选参数：cre 默认，创建文件     rep 如果文件存在，则覆盖，不存在则创建文件     app 如果文件存在，则在结尾追加，不存在则创建文件     off 停止将sqlplus中的输出结果复制到指定文件，并关闭该文件     out 启动该功能，经sqlplus的输出结果复制到指定文件*/spool/spo '路径\文件名' cre/rep/app/;start/@ '路径\文件名';spool/spo off;------------------------------变量的声明--------------------------------------用&声明临时变量，用&&声明表示使用同一个临时变量(以emp表为例)select &temp from emp where &temp>2000; --表示两个变量，需要输入两次select &&temp from emp where &&temp>2000 --表示一个变量，输入一次--使用临时变量时，是否显示“原值新值”信息set verify off; --不显示set verify on;  --显示--已定义变量的两种声明方法--用define声明变量define temp=100;--用accept声明acc[ept] 变量名 变量类型 [format/for 指定格式] [default/def default] [prompt '提示内容'] ompt/hide例句：accept test number format 9999 prompt '请为test输入一个值：' hide; --hide表示隐藏输入的值--删除已定义变量undefine 变量名;------------------------------表空间的操作----------------------------------------查询表空间使用情况SELECT UPPER(F.TABLESPACE_NAME) "表空间名",　　D.TOT_GROOTTE_MB "表空间大小(M)",　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",　　F.TOTAL_BYTES "空闲空间(M)",　　F.MAX_BYTES "最大块(M)"　　FROM (SELECT TABLESPACE_NAME,　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES　　FROM SYS.DBA_FREE_SPACE　　GROUP BY TABLESPACE_NAME) F,　　(SELECT DD.TABLESPACE_NAME,　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB　　FROM SYS.DBA_DATA_FILES DD　　GROUP BY DD.TABLESPACE_NAME) D　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME　　ORDER BY 1--查询数据库现在的表空间select tablespace_name 表空间名称, file_name 文件路径, sum(bytes)/1024/1024 文件大小 from dba_data_files group by tablespace_name,file_name;--查看当前实例select name from v$database;--查看当前数据库实例名和主机名select instance_name,host_name from v$instance;column host_name format a20; --对齐显示，可有可无--查看所有表空间select tablespace_name from dba_tablespaces;--查看用户下的表select * from user_tables;--查看系统所有用户select username,account_status from dba_users;--查看具体的用户权限，sys管理员登录时：select * from dba_tab_privs where grantee= 'SH';  select * from TABLE_PRIVILEGES where GRANTEE='HLL1';--查看当前用户的权限，当前用户登录时：select * from user_sys_privs;--创建表空间create tablespace 表空间名datafile '路径\表空间名.dbf'  /*表空间存放路径*/size 10m /*初始容量*/autoextend on next 2m maxsize 100m  /*表空间自动扩展，每次扩展2m,最大限制100m,无最大限制可以设置为unlimited*/loggingextent management local /*表空间中的盘区管理采用本地化管理方式*/segment space management auto; /*表空间中段的管理方式为自动管理方式*/--删除表空间drop tablespace 表空间名 including contents and datafiles;--建立临时表空间create temporary tablespace 临时表空间名 tempfile '/oracle/oradata/db/临时表空间名.dbf' SIZE 50M --1.使表空间脱机alter tablespace 表空间名 offline;--如果是意外删除了数据文件，则必须带有RECOVER选项alter tablespace 表空间名 offline for recover;--2.使表空间联机alter tablespace 表空间名 online;--3.使数据文件脱机alter database datafile 3 offline;--4.使数据文件联机alter database datafile 3 online;--5.使表空间只读alter tablespace 表空间名 read only;--6.使表空间可读写alter tablespace 表空间名 read write; /*扩展表空间修改文件大小：*/alter database datafile 'd:\fatafile.dbf' resize 300M;--添加数据文件：alter dataspace userdb add datafile 'd:\datafile2.dbf' size ...;--将表空间的数据文件设成自动扩展：alter database datafile 'd:\datafile2.dbf' autoextend on next 5M maxsize unlimited;--表空间重命名alter tablespace 原表空间名 rename to 新表空间名;--------------------------用户及权限--------------------------------------创建用户create user 用户名identified by 密码default tablespace 默认的表空间名;/*给用户分配权限或角色connect:临时用户resource：正式数据库用户，可以创建表、触发器、过程等dba：数据库管理员角色，最高权限*/grant connect,resource,dba to 用户名;--撤销用户的权限或角色revoke connect from qy;--锁定用户帐户alter user 用户名 account lock;--解除用户帐户锁定alter user 用户名 account unlock;/*忘记密码（仅限在本机使用）可以使用 conn /@实例名 as sysdba先登录再修改密码： alter user 用户名 identified by 密码;  */---------------------------数据表的操作------------------------------------允许用户查看某个表中的记录grant select on 表名 to 用户名;--允许用户更新某个表中的记录grant update on 表名 to 用户名;desc 表名;  --查看表结构select table_name from user_tables; --查询当前用户下的数据表的名称create index 索引名 on 表名(列名);  --创建索引drop index 索引名; --删除索引select index_name from user_indexes; --查询所有索引名create view 视图名 as sql查询语句;  --创建视图create sequence 序列名; --创建序列序列名.nextval   --使用序列--创建数据表create table master(       id number(6,0) primary key,       loginid nvarchar2(50) not null,       password nvarchar2(20) not null,       status char(1) default 1 not null);--删除数据表drop table 表名;-------------------------约束----------------------------------添加带主键及约束的语法：--alter table  表名--add constraint  约束名  约束类型  具体的约束说明--添加主键约束(将StudentNo作为主键)alter table Studentadd constraint PK_stuNo primary key (StudentNo)--添加唯一约束(身份证号唯一)alter table Studentadd constraint UQ_stuID unique (身份证号列名)--添加默认约束(如果地址不填，默认为“地址不详”)alter table Studentadd constraint DF_stuAddress default ('地址不详') for Address--添加检查约束(要求出生日期在1980年1月1日之后)alter table Studentadd constraint CK_stuBornDate check (BornDate >= '1980-01-01')--添加外键约束(主表Student和从表Result建立关系，关联列为StudentNo)alter table Resultadd constraint FK_stuNo foreign key (stuNo) references Student(stuNo)--删除约束：alter table 表名drop constraint 约束名-----------------------序列------------------------------------/*创建序列,序列用来为表的主键生成唯一的序列值.可选参数：Increment by 指定序列值每次增长的值Start with 指定序列的第一个值Maxvalue 指定产生的序列的最大值Minvalue 指定产生的序列的最小值Cycle 指定当序列值逵到最大或最小值时,该序列是否循环.Cache 指定序列生成器一次缓存的值的个数Order 指定序列中的数值是否按访问顺序排序. */ create sequence 序列名         increment by 4         start with 50         maxvalue 60         minvalue 50         cycle          cache 3;--查看序列,dual为虚拟表select 序列名.nextval from dual;select 序列名.currval from dual;-----------------------column命令简单使用----------------------------------------使用column命令格式化指定的列(以emp表为例)column empon heading '员工编号' format 9999;column ename heading '员工姓名' format a10;column mgr heading '上级编号' format 9999;column hiredate heading '受雇日期' justify center;column sal heading '员工工资' format $999,999,99;select * from emp;---------待更新。。。