oracle常用脚本

• 查看表空间

 SELECT * FROM DBA_DATA_FILES;

• 创建表空间

create tablespace ts_dev datafile 'f:\oracle\product\10.2.0\oradata\baseorcl\ts_dev.dbf' size 100m autoextend on next 10m;

• 创建临时表空间：

create temporary tablespace tablespaceName
tempfile 'E:\oracle\product\10.2.0\oradata\orcl\temp_tablespace01.dbf
size 50m 
autoextend on 
next 50m maxsize 2048m 
extent management local;  

• 给表空间添加数据文件(多个)：

alter tablespace tablespaceName
add datafile 'E:\oracle\product\10.2.0\oradata\orcl\tablespace02.dbf'
size 100M
autoextend on next 100M
maxsize 500M,
'E:\oracle\product\10.2.0\oradata\orcl\tablespace03.dbf'
size 100M
autoextend on next 100M
maxsize 500M ;

• 查看表空间使用情况

SELECT A.TABLESPACE_NAME "表空间名",
       TOTAL "表空间大小",
       FREE "表空间剩余大小",
       (TOTAL - FREE) "表空间使用大小",
       TOTAL / (1024 * 1024) "表空间大小(MB)",
       FREE / (1024 * 1024) "表空间剩余大小(MB)",
       (TOTAL - FREE) / (1024 * 1024) "表空间使用大小(MB)",
       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;

• 删除表空间（同时删除内容和数据文件）

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

• 创建用户

create user tbtest identified by tbtest default tablespace ts_dev temporary tablespace ts_dev_temp;

• 常见授权

grant connect,resource to tbtest;  
grant debug connect session to tbtest;  
grant create session to tbtest;  
grant unlimited session to tbtest;  
grant create view to tbtest;  
grant create table to tbtest;  
grant create SEQUENCE to tbtest;  
grant select any dictionary to tbtest;  
grant SELECT ANY SEQUENCE to tbtest;  
Grant SELECT ANY TABLE TO tbtest;  
GRANT INSERT ANY TABLE TO tbtest;  
GRANT UPDATE ANY TABLE TO tbtest;  
GRANT DELETE ANY TABLE TO tbtest;  
GRANT EXECUTE ANY PROCEDURE TO tbtest;  
GRANT CREATE ANY PROCEDURE TO tbtest;  
GRANT DBA TO tbtest;  
select * from user_sys_privs;--查看当前用户所有权限  
select * from user_tab_privs;--查看所有用户对表的权限  

• 授予用户操作表空间的权限

grant unlimited tablespace to xxx;  
grant create tablespace to xxx;  
grant alter tablespace to xxx;  
grant drop tablespace to xxx;  
grant manage tablespace to xxx;  

• 撤销用户操作表空间的权限

revoke unlimited tablespace from xxx;  
revoke create tablespace from xxx;    
revoke alter tablespace from xxx;   
revoke drop tablespace from xxx;  
revoke manage tablespace from xxx;  

• exp与imp
  exp “‘sys/密码@ip:port/sid as sysdba’” query="‘WHERE ROWNUM<10’" file=/home/oracle/db/tableName.dmp tables=tableName
  imp “‘用户名/密码@ip:port/sid’” file=/home/oracle/db/tableName.dmp tables=tableName
• 导出dmp文件：
  exp 用户名/密码@ip地址:端口/sid file=导出至文件
  full=y --导出所有用户的数据
  owner=(用户1,用户2) --导出指定用户的数据
  tables=(表1,表2) --导出指定表的数据;
• 导入dmp文件：
  imp 用户名/密码@ip地址:端口/sid file=要导入的文件
  ignore=y（忽略错误）
  owner=(用户1,用户2) --导入指定用户的数据
  tables=(表1,表2) --导入指定表的数据;
• expdp与impdp

expdp "'sys/密码@ip:port/sid as sysdba'" TABLES=tableName dumpfile=tableName.dmp DIRECTORY=dp1 EXCLUDE=TABLE:\"IN(\'table1\',\'table2\')\";
impdp "'用户名/密码@ip:port/sid'" DIRECTORY=dp1 DUMPFILE=tableName.dmp SCHEMAS=schemaName;
impdp "'用户名/密码@ip:port/sid'" DIRECTORY=dp1 DUMPFILE=tableName.dmp FULL=y;
impdp "'用户名/密码@ip:port/sid'" DIRECTORY=dp1 DUMPFILE=tableName.dmp tables=tableName remap_schema=oldSchemaName:newSchemaName remap_tablespace=oldTableSpaceName:newTableSpaceName;
select * from dba_directories;--查看directory
create or replace directory DUMP_DIR as 'D:\DATA_TEMP\dir';--创建directory
grant read,write on directory dp1 to 用户名;--授权用户操作directory

• delete后恢复数据

SELECT * FROM tableName AS OF TIMESTAMP TO_TIMESTAMP('2018-11-20 16:00:32', 'YYYY-MM-DD HH24:MI:SS') where id in ('102625','102626'); 

• 表死锁解锁

SELECT SESS.SID,
       SESS.SERIAL#,
       LO.ORACLE_USERNAME,
       LO.OS_USER_NAME,
       AO.OBJECT_NAME,
       LO.LOCKED_MODE
  FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS
 WHERE AO.OBJECT_ID = LO.OBJECT_ID
   AND LO.SESSION_ID = SESS.SID;  
alter system kill session 'sid,serial#';

• 定位到具体异常(since oracle10g)
  dbms_utility.format_error_backtrace，比SQLCODE、SQLERRM更准确
  没有字符长度限制，必须在异常处理模块中使用。
• 查看日志模式

1.select name,log_mode from v$database；
2.archive log list;

• 降水位
  alter table t enable row movement;
  alter table t shrink space;