oracle操作文档

################################################################
--sqlplus登录数据库：(普通用户登录)(管理员登录)
sqlplus账户名/密码
sqlplus / as sysdba
--查看所有用户：
SQL>select * from dba_users;
SQL>select * from all_users;
SQL>select * from user_users;
SQL>drop user bhs_db cascade;删除用户以及用户相关表
SQL>drop table 表名 purge;彻底删除表
SQL>drop user 用户名;删除用户
SQL>create user bhs_db identified by bhs_db;创建用户
SQL>grant connect,dba,resource to bhs_db;给用户授dba权限
SQL>grant connect,resource to bhs_db;收连接权限
SQL>alter user sys identified by oracle;--修改用户密码(修改sys密码为oracle)
SQL>ho--后台挂起数据库
SQL>'exit'or'quit'--退出数据库
SQL>shutdown immediate--关闭数据库
SQL>startup;--启动数据库
SQL>select open_mode from v$database;   --查询实例状态：
SQL>commit;--提交操作
SQL>select t.address from zbx_interface_register t;--查找(表)zbx_interface_register的address字段
SQL>select count(*) from user_tables; --查询用户有多少表
SQL>select userenv ('language') from dual;--查询数据库内的字符集
SQL>UPDATE PROPS$SETVALUE$='ZHS16GBK';--update更新字符,设置为(中文)ZHS16GBK
SQL>set nls_lang=SIMPLIFIEDCHINESE_CHINA.ZHS16GBK；
SQL>Show user;显示当前用户
SQL>/运行上一条语句(就是一个斜杠)
SQL>selec tuser_status from uct_user where user_ename='ZGYH';--查user_status表内uct_user用户
SQL>selec tuse_status from bhs_excar_beian where excarbeian1='ZGYH';--也是查用户
SQL>selec tvalue from v$parameter where name='processes';--数据库允许的最大连接数
SQL>selec t count(*) fromv $session;--当前的session连接数
SQL>show parameter processes;--最大连接
SQL>select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
--给空表分段(segment)，先执行，再复制结果进行执行，便可以给空表分配段
SQL>alter system set deferred_segment_creation=false #11g用exp备份时，空表将不被导出,可通过dba权限运行解决此问题
############################################################################
SQL>exp utr_db/utr_db file=utr_db2016-10-11.dmp full=y--完全导出用户所在的数据库，用户名utr_db密码utr_db导出到当前目录utr_db2016-10-11.dmp中||rows=n只导出表结构，不导出数据(可以和full=y一起使用)
SQL>exp utr_db/utr_db@orcl file=orcl_utr.dmp owner=(utr_db,utrm_db)--同时导出多个用户表，将orcl数据库中utr_db与utrm_db的表导出，导出时owner与full不能同时使用
SQL>exp scott/tiger tables=(emp,dept) file=/directory/scott.dmp grants=y--按表导出,把scott用户里两个表emp,dept导出到文件/directory/scott.dmp grants=y将这些权限导出，tables不能与owner与full同时使用

SQL>imp utr_db/utr_dbfile=/tmp/utrm_db2016-10-11.dmp ignore=y--数据库导入,ignore=y表示忽略创建错误，继续后面的操作（并且如果导入的对象里面有其他的对象，如约束，索引等，会在数据插入后被创建）||indexs=N不加载索引(比如唯一性的索引)||constraints=N忽略加载约束
SQL>imp utr_db/utr_db file=/tmp/utrm_db2016-10-11.dmp full=y statistics=none--数据库导入,full=y表示全库导入,不导入统计信息。
SQL>imp excar0816/excar0816 tables=\(EXCAR_DATA_MONEY\) file=118bak_20170820.dmp ignore=n fromuser=excar touser=excar0816 buffer=1000000 单表导入，设置缓冲区

SQL>exp user/password file=d:\temp.dmp owner=user consistent=y direct=y --Oracle 把一个用户的数据导入另一个用户(导出)
SQL>imp newuser/password file=temp.dmp ignore=y fromuser=olduser touser=newuser --Oracle 把一个用户的数据导入另一个用户(导入)
############################################################################
使用expdp导出表，（注意如果使用expdp导出的数据只能使用impdp导入）
创建导出目录
mkdir /home/oracle/dump
SQL>create directory dump_utrdb as '/home/oracle/dump';
SQL>select * from dba_directories;
SQL>grant read,write on directory dump_utrdb to utr_db;
expdp utr_db/utrm_db directory=dump_utrdb dumpfile=文件名.dmp logfile=文件名.log
expdp user/passwd@orcl directory=dump_utrdb  dumpfile=filename.dmp logfile=logname.log;//远程导出命令
使用impdp导入表
mkdir /u01/utrm
SQL>create directory dump_utrdb as ‘/u01/utrmdb’;
SQL>select * from dba_directories;
SQL>grant read,write on directory dump_utrdb to utr_db;
impdp utr_db/utrm_db directory=dump_utrdb dumpfile=文件名.dmp logfile=文件名.log full=y
impdp user/passwd@orcl dumpfile=filename.dmp logfile=logname.log full=y;//远程导入命令
ignore=y(表存在时加此参数)full=y(空表也导入)
select table_name from user_tables; //当前用户的表
select table_name from all_tables; //所有用户的表
select table_name from dba_tables; //包括系统表
############################################################################
############################################################################
############################################################################

查询表空间
SQL>select username,default_tablespace from user_users;查询当前用户所在的表空间
--1、查看表空间的名称及大小 
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name 
GROUP BY t.tablespace_name; 
--2、查看表空间物理文件的名称及大小 
SELECT tablespace_name, 
file_id, 
file_name, 
round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files 
ORDER BY tablespace_name; 
--3、查看回滚段名称及大小 
SELECT segment_name, 
tablespace_name, 
r.status, 
(initial_extent / 1024) initialextent, 
(next_extent / 1024) nextextent, 
max_extents, 
v.curext curextent 
FROM dba_rollback_segs r, v$rollstat v 
WHERE r.segment_id = v.usn(+) 
ORDER BY segment_name; 
--4、查看控制文件 
SELECT NAME FROM v$controlfile; 
--5、查看日志文件 
SELECT MEMBER FROM v$logfile; 
--6、查看表空间的使用情况 
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name 
FROM dba_free_space 
GROUP BY tablespace_name; 
SELECT a.tablespace_name, 
a.bytes total, 
b.bytes used, 
c.bytes free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name; 
--7、查看数据库库对象 
SELECT owner, object_type, status, COUNT(*) count# 
FROM all_objects 
GROUP BY owner, object_type, status; 
--8、查看数据库的版本　 
SELECT version 
FROM product_component_version 
WHERE substr(product, 1, 6) = 'Oracle'; 
--9、查看数据库的创建日期和归档方式 
SELECT created, log_mode, log_mode FROM v$database;  
#删除表空间及物理文件
drop TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;
#修改默认表空间为NEW_USERS
alter database default tablespace NEW_USERS;
#表空间名字; 创建用户指定默认表空间
create user bhs_db identified by bhs_db default tablespace 表空间名
#创建表空间NEW_USERS
create tablespace CMS
datafile '/home/oracle/oracle/oradata/orcl/CMS.dbf'
size 200M
autoextend on next 5M maxsize 32767M;
############################################################################
针对非大文件方式表空间，允许追加文件进行表空间的扩展，单个文件最大大小是32G 
第一种方式：表空间增加数据文件 
1、alter tablespace USERS add datafile '/home/oracle/oracle/oradata/Qyydb/users02.dbf' size 30000M; 
第二种方式：表空间增加数据文件，设置自增长，限制最大值 
2、alter tablespace USERS add datafile '/home/oracle/oracle/oradata/Qyydb/users02.dbf' size 500M autoextend on maxsize 3072M;
alter tablespace SYSTEM add datafile '/home/oracle/oracle/oradata/Qyydb/system02.dbf' size 500M autoextend on;
第三种方式：已存在表空间数据文件设置自增长 
3、alter database datafile 'G:\spacess01.DBF' autoextend on maxsize 3072M; 
第四种方式：已存在表空间数据文件重新设置大小 
4、alter database datafile 'G:\spacess01.DBF' resize 3072M; 
alter database datafile '/home/oracle/oracle/oradata/orcl/system01.dbf' resize 3072M; 
alter database datafile '/home/oracle/oracle/oradata/orcl/users01.dbf' resize 3072M; 

############################################################################
1.数据文件自动扩展的好处
1）不会出现因为没有剩余空间可以利用到数据无法写入
2）尽量减少人为的维护
3）可以用于重要级别不是很大的数据库中，如测试数据库等

2.数据文件自动扩展的弊端
1）如果任其扩大，在数据量不断变大的过程中会导致某个数据文件异常的大
2）没有人管理的数据库是非常危险的

3.查询当前数据库中表空间SEC_D是否为自动扩展
sec@orcl> select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = 'SYSTEM';

TABLESPACE_NAME   FILE_NAME                                 AUT
----------------- ----------------------------------------- ---
SEC_D             /u01/app/oracle/oradata/orcl/sec_d01.dbf  NO

4.通过修改SEC_D的数据文件为自动扩展达到表空间SEC_D为自动扩展的目的
sec@orcl> alter database datafile '/u01/app/oracle/oradata/orcl/sec_d01.dbf' autoextend on;

Database altered.

5.确认是否已经修改成功
sec@orcl> select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = 'SEC_D';

TABLESPACE_NAME   FILE_NAME                                 AUT
----------------- ----------------------------------------- ---
SEC_D             /u01/app/oracle/oradata/orcl/sec_d01.dbf  YES

6.总结修改语句语法
开启自动扩展功能语法：
alter database datafile '对应的数据文件路径信息' autoextend on;
关闭自动扩展功能语法：
alter database datafile '对应的数据文件路径信息' autoextend off;

--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;
############################################################################
1查看用户的proifle是哪个，一般是default：
sql>SELECT username,PROFILE FROM dba_users;
2查看指定概要文件（如default）的密码有效期设置：
sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
3将密码有效期由默认的180天修改成“无限制”：
sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
修改之后不需要重启动数据库，会立即生效。
4修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示；
已经被提示的帐户必须再改一次密码，举例如下：
 $sqlplus / as sysdba
sql> alter user 用户名 identified by <原来的密码> ----不用换新密码
alter user qyybidtest identified by QyyZbxDb2017;
alter user unibid identified by QyyZbxDb2017;
alter user unibid_bid identified by QyyZbxDb2017;
alter user qyytest identified by QyyZbxDb2017;
alter user cms_test identified by QyyZbxDb2017;
alter user cms identified by QyyZbxDb2017;
alter user ZABBIX identified by zabbix;

unibid
unibid_bid

CMS
QYYBIDTEST
QYYTEST
CMS_TEST

UTRM
UTRZ
UTRG
ZABBIX
############################################################################
Alert log XML文件位置：
SQL> select value from v$diag_info where name ='Diag Alert';
Alert log文本文件位置：
SQL> select value from v$diag_info where name ='Diag Trace';
############################################################################
select count(*) from v$process --当前的连接数
select value from v$parameter where name = 'processes'; --数据库允许的最大连接数
修改最大连接数:
alter system set processes = 2000 scope = spfile;
重启数据库:
shutdown immediate;
startup;
############################################################################
删除用户提示有连接
select sid,serial# from v$session;
alter system kill session'532,4562' 
drop user name cascade
create user excar0824 identified by excar0824;
grant connect,dba,resource to excar0824;
############################################################################
给一个用户授予增删改查其他用户的指定表的权限
create user excar_kh identified by excar_kh default tablespace USERS; 
=============创建用户excar_kh
sqlplus excarh_kh/excar_kh

grant connect,create synonym to excar_kh;
！！！！给excar_kh授权 连接、创建同义词的权限

grant select on EXCAR.BEIQI to excar_kh;
=============把EXCAR.XXXXX这些表的select权限给utr_cqxh

create synonym BEIQI for EXCAR.BEIQI;
=============用excar_kh用户创建EXCAR.UTRG_CEMS_PROJECT的同义词为BEIQI

revoke create synonym from excar_kh;
=============用EXCAR用户去掉excar_kh创建同义词的权限

SELECT * FROM BEIQI;
SELECT * FROM utrm_cems_project;
SELECT * FROM utrz_cems_project;
=============用excar_kh用户查刚刚创建同义词的表

--【修改验证】使用excar_kh用户登录数据库，应提示“权限不足”
update utrg_cems_project t set t.projectid = t.projectid;
update utrm_cems_project t set t.projectid = t.projectid;
update utrz_cems_project t set t.projectid = t.projectid;
--【删除验证】使用excar_kh用户登录数据库，应提示“权限不足”
delete from utrg_cems_project t where t.projectid = '11111111';
delete from utrm_cems_project t where t.projectid = '11111111';
delete from utrz_cems_project t where t.projectid = '11111111';
--【创建同义词验证】，应提示“权限不足”
create synonym testsynonym for UTRM_DB_LOCAL.utrz_cems_appraisal;

1.创建一个ORACLE 的用户

create user excar_kh identified by excar_kh;

2.给用户授权 
 grant connect,resource to excar_kh;
 grant select on 表或视图名 to excar_kh;
############################################################################
删除当前用户下所有的表
select 'drop table '||table_name||';' from cat where table_type='TABLE';
将会输出一批删除表的sql语句，这些SQL语句执行一下就可以了。（需要有drop table的权限）
############################################################################
#查询库中表内有数据的并将表名和记录数显示
declare
v_count number(10);
t_count number(10) default 0;
cursor t_cur is
select table_name from user_tables;
begin
for t_rec in t_cur
loop
execute immediate 'select count(*) from ' || t_rec.table_name  into  v_count;
if v_count >0 then
dbms_output.put_line(t_rec.table_name ||'|' ||to_char(v_count));
end if;
end loop;
dbms_output.put_line(to_char(t_count));
end;
############################################################################
############################################################################