Oracle常用命令自查表

工作中常用Oracle数据库，版本为 11g，常见的命令汇总如下：

--创建用户：
create user username identified by password

--解锁用户：
ALTER USER username ACCOUNT UNLOCK;
-----------------创建表空间----------------
/*分为四步 */
/*第1步：创建临时表空间  */
create temporary tablespace username_temp
tempfile 'E:\app\LiJiVV\oradata\orcl\username_temp.dbf'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 
 
/*第2步：创建数据表空间  */
create tablespace username_data 
logging 
datafile 'E:\app\LiJiVV\oradata\orcl\username_data.dbf'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 

/* 在Linux下创建表空间 */
CREATE TABLESPACE PMLF_TCMA_GD_BIG        
DATAFILE '/opt/oracledb/PMLF_TCMA_GD_BIG.dbf'          
SIZE 500 M          
AUTOEXTEND ON NEXT 32 M MAXSIZE UNLIMITED
LOGGING        
DEFAULT NOCOMPRESS        
ONLINE        
PERMANENT        
EXTENT MANAGEMENT LOCAL AUTOALLOCATE

 
/*第3步：创建用户并指定表空间  */
create user username identified by username 
default tablespace username_data 
temporary tablespace username_temp; 
 
/*第4步：给用户授予权限  */
grant connect,resource,dba to username; 

-------------------------------------------

--删除用户
drop user username cascade

--删除表空间
drop tablespace tablespace_name

--重命名表空间
alter tablespaces tablespace_name rename to new_tablespace_name;  

--授予权限：
grant dba to username

--查看oracle数据库的version
select * from v$version

--sql标准格式：

--插入：
insert into 表名 (字段1, 字段2, 字段3, ...) values (值1, 值2, 值3, ...);

--更新：
update 表名  set 字段名 = 插入值  where 条件

--存在即更新，不存在则插入：
begin
	--写 update 语句，记住，分号";"结尾
	if sql%rowcount = 0 then
	--写 insert 语句，记住，分号";"结尾
	end if;
end;

--MERGE 针对同一张表

MERGE INTO mn a
USING (select count(*) co from mn where mn.ID=4) b
ON (b.co<>0)--这里使用了count和<>，注意下，想下为什么！
WHEN MATCHED THEN
UPDATE
SET a.NAME = 'E'
where a.ID=4
WHEN NOT MATCHED THEN
INSERT
VALUES (4, 'E');
 




--复制一张表中的输入到另一张表中
--1】两张表字段完全相同的情况下
insert into tablename1 select * from tablename2
--2】复制部分数据
insert into 表A(字段1,字段2,...) select 字段1,字段2,... from 表B 

--取某一行数据的最大、最小值
greatest（col1, col2, col3...）--取得col1, col2, col3...字段中是大的值
least（col1, col2, col3....）--同理，取得col1,col2,col3...字段中最小的值
--计算某一行数据的平均值
(nvl(col1,0)+nvl(col2,0)+nvl(col3,0)+...+nvl(coln,0))/n

--判断一个列的值，例子如下，加入COL大于95，则为0，否则不变
CASE WHEN COL > 95 THEN 0 ELSE COL END

--Oracle 递归查询
--找子集
select * from Lf_Ctrl_Module start with MODULEID = '01' connect by prior MODULEID=UPMODULEID

--找父集
select * from Lf_Ctrl_Module start with MODULEID = '0101' connect by prior UPMODULEID=MODULEID


--导出多张表：
exp username/password@orcl tables=(table1,table2,table3,table4,...) file=e:\table.dmp


--导出单张表：
exp username/password@orcl tables=table1 file=e:\table.dmp


--导出整个数据库：
--1、本地导出
exp username/password@orcl file=e:\table.dmp owner=username

--2、远程导出
exp username/password@192.168.0.1/orcl file=e:\table.dmp owner=username

--3、导出所有表结构不要数据：
exp username/password@orcl file=e:\table.dmp owner=username rows = n


--导入数据库
imp username/password@orcl file=e:\table.dmp full=y commit = y


--更换数据库某张表的表名(备份时常用)
--直接更换
ALTER TABLE 当前表名字 RENAME TO 新表的名字
--或者
rename 当前表名字 to 新表的名字 ;

--建新表删旧表
create new_table as select * from old_table;drop table old_table;

--修改数据表名 
ALTER TABLE OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME; 

--修改列名 
ALTER TABLE TABLE_NAME RENAME COLUMN OLD_COLUMN_NAME TO NEW_COLUMN_NAME; 

--修改列的数据类型 
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME NEW_DATATYPE; 

--修改VARCHAR2列的字段长度
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME VARCHAR2(你需要的值); 

--删除列 
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

--添加字段 
ALTER TABLE 表名 ADD 字段 NUMBER(15,4);

--删除当前用户下所有表
select 'drop table '||table_name||';' from user_tables;  

-- Oracle用户权限表 --
--一、创建

sys;--系统管理员，拥有最高权限

system;--本地管理员，次高权限

scott;--普通用户，密码默认为tiger,默认未解锁

--二、登陆

sqlplus / as sysdba;--登陆sys帐户

sqlplus sys as sysdba;---同上

sqlplus scott/tiger;--登陆普通用户scott

--三、管理用户

--在管理员帐户下，创建用户username
create user username;

--修改密码
alert user scott identified by tiger;

--修改用户默认表空间
alter user username default tablespace tablespace_name;

--四，授予权限
--1、默认的普通用户scott默认未解锁，不能进行那个使用，新建的用户也没有任何权限，必须授予权限
grant create session to username;--授予username用户创建session的权限，即登陆权限

grant unlimited tablespace to username;--授予username用户使用表空间的权限

grant create table to username;--授予创建表的权限

grant drop table to username;--授予删除表的权限

grant insert table to username;--插入表的权限

grant update table to username;--修改表的权限

grant all to public;--这条比较重要，授予所有权限(all)给所有用户(public)

--2、oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权
grant select on tablename to username;--授予username用户查看指定表的权限

grant drop on tablename to username;--授予删除表的权限

grant insert on tablename to username;--授予插入的权限

grant update on tablename to username;--授予修改表的权限

grant insert(id) on tablename to username;

grant update(id) on tablename to username;--授予对指定表特定字段的插入和修改权限，注意，只能是insert和update

grant alert all table to username;--授予username用户alert任意表的权限

--五、撤销权限

--基本语法同grant,关键字为revoke

--六、查看权限

select * from user_sys_privs;--查看当前用户所有权限

select * from user_tab_privs;--查看所用用户对表的权限

--七、操作表的用户的表

select * from username.tablename

--八、权限传递

--即用户A将权限授予B，B可以将操作的权限再授予C，命令如下：

grant alert table on tablename to username with admin option;--关键字 with admin option

grant alert table on tablename to username with grant option;--关键字 with grant option效果和admin类似

--九、角色

--角色即权限的集合，可以把一个角色授予给用户

create role myrole;--创建角色

grant create session to myrole;--将创建session的权限授予myrole

grant myrole to username;--授予username用户myrole的角色

drop role myrole;删除角色

--赋予某个用户操作此用户的权限
GRANT SELECT/update/insert/delete on PMLF_TCMA_GD_XC.Lf_Ctrl_Trade TO PMLF_GD;


--十、Oracle 查看表空间的大小及使用情况sql语句

--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;
--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name 

--十一、oracle回收站问题及解决方案
--1、查询回收站
SELECT t.object_name,t.type ,t.original_name FROM user_recyclebin t;

SELECT count(*) FROM user_recyclebin t;

--2、回收站清理
purge recyclebin ;

--3、禁用回收站：
ALTER SESSION SET recyclebin = OFF; -- 作用于会话状态
ALTER SYSTEM SET recyclebin = OFF; -- 作用于系统状态

--4、启用回收站：
ALTER SESSION SET recyclebin = ON; -- 作用于会话状态
ALTER SYSTEM SET recyclebin = ON;-- 作用于系统状态

--5、单独删除
PURGE TABLE BIN$5l669dZGTlGbDXj0fB80Gw==$0;

--6、恢复回收站中的表
flashback table BIN$5l669dZGTlGbDXj0fB80Gw==$0 to before drop; 


--十二、Oracle 11g空表不能导出问题解决
--Oracle 11g的新特性，数据条数是0时不分配segment，所以就不能被导出。
--解决方案：
--1、先执行此语句生成分析表的SQL，copy出来，然后打开新的SQL窗口执行
select 'analyze table '||table_name||' compute statistics;' from user_tables;
--2、执行sql，查看有哪些是空表
select table_name from user_tables where NUM_ROWS=0; 
--3、通过select 来生成修改语句
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0; 
--4、将上面生成的修改语句，copy出来，然后打开新的SQL窗口执行即可 

--十三、查看所有用户
select * from dba_users;   
select * from all_users;   
select * from user_users;

--十四、删除所有表
select 'drop table '||table_name||';' from cat where table_type='TABLE' 

--十五、修改用户过期时间为永久
select * from Dba_Profiles A WHERE A.profile = 'DEFAULT'  AND A.resource_name = 'PASSWORD_LIFE_TIME';

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

--十六、查询某个表的字段都被哪些表引用为外键
select c.TABLE_NAME tablename,c.constraint_name foreignname,u.column_name columnname
from all_constraints p, all_constraints c,user_cons_columns u
where p.table_name = 'TABLE_NAME'
and p.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
and c.OWNER=SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
and c.constraint_type = 'R'
and c.CONSTRAINT_NAME=u.constraint_name
and p.CONSTRAINT_NAME = c.R_CONSTRAINT_NAME

--十七、修改最大连接数
--查看连接线程数
SELECT COUNT(*) FROM V$PROCESS;
--查询连接线程数配置表
select * from v$parameter where name ='processes';
--统计数据库连接的消耗情况
select  b.MACHINE, b.PROGRAM, b.USERNAME, count(*) from v$process a, v$session b where a.ADDR = b.PADDR and  b.USERNAME is not null  group by  b.MACHINE, b.PROGRAM, b.USERNAME order by count(*) desc
--修改最大连接数，重启监听和服务后生效
alter system set processes = 1000 scope = spfile;