Fork me on GitHub

数据库操作语句2

常用的SQL语句,汇总,以便之后查阅。

备份

MongoDB 备份

备份生产数据:

/db/mongodb-3.4.7/bin/mongodump -h 127.0.0.1:9430 -d daochufilename -o /db/mongodb-3.4.7/data/mongo_bak/180414

删除老数据:

进入测试mongdb: ./mongo 127.0.0.1:9430
查看所有的库: show dbs
选择待删除的库: use databaseName
删除当前库: db.dropDatabase()
退出: exit

导入数据

/db/mongodb-3.4.7/bin/mongorestore –host=127.0.0.1 –port 9430 –db databasename /db/mongodb-3.4.7/data/mongo_bak/180414/daochufilename

Oracle 备份

11G 导出, 没有数据的表无法导出

共有多少张表:
  • select count(1) from user_tables ;
  • select ‘drop table ‘||table_name||’ cascade constraints;’ from user_tables;
  • select ‘drop trigger ‘ || trigger_name || ‘;’ from user_triggers;
  • select ‘drop sequence ‘ || sequence_name || ‘;’ from user_sequences;

1、先查询一下当前用户下的所有空表

select table_name from user_tables where NUM_ROWS=0;

2、用以下这句查找空表

1
2
select 'alter table '||table_name||' allocate extent;' 
from user_tables where num_rows = 0 ;

或者

1
2
select ‘alter table ‘||table_name||’ allocate extent;’ 
from user_tables where num_rows=0;

alter table table_name allocate extent;

原因:

Oracle 11G 出现EXP-00003: 未找到段 (0,0) 的存储定义
oracle 11g数据库表中没有数据在导出数据的时候会遇到的问题,alter system set deferred_segment_creation=false; 该参数意思是当创建对象(如表),初始没有数据,是否立即创建segment。默认是true。这会导致在exp时,没有segment的对象不会导出。
但此命令只对以后的表有效,之前的表没有Segment的还是没有。可以创建表的时候声明立即创建Segment ,create table XXX (XXX XXX) SEGMENT CREATION IMMEDIATE;
对于已经创建但是还没有Segment的表来说,可以执行alter table TabelName allocate extent来使其创建出Segment,当然也可以插入一条数据,使其创建Segment .
解决方法:
1、先查询一下当前用户下的所有空表
select table_name from user_tables where NUM_ROWS=0;
2、用以下这句查找空表
select ‘alter table ‘||table_name||’ allocate extent;’ from user_tables where num_rows=0 ;
3、把查询结果导出,执行导出的语句 如:
alter table table_name allocate extent;

exp username/password@192.168.5.214/ORCL file=d:\daochu.dmp FULL=y

exp XNJD20171225/12345678@192.168.2.240/ORCL file=d:\daochu.dmp owner=(XNJD20171225,sys) 导出对应用户的数据库中的内容

exp username/pwd@sid file=d:/data/bak.dmp owner=(user)rows=n 不导出数据

导入数据库

imp username/password@192.168.5.214/ORCL tablespaces=xxx fromuser=cccccc touser=zzzzz file=D:\daoru.dmp FULL=y

导出数据库 注意版本要一致,10g 对10g 11g对11g 否则会出错。因为有exp版本和server版本之分

exp_dir=/fo

管理员

sqlplus / as sysdba

sqlplus /nolog
conn username/password as sysdba

设置directory

–查路径
select * from dba_directories;

1
2
SYS                EXP_IMP_DATA
/opt/oracle/oradata/forp/db_bakup

create or replace directory dmp_dir as ‘/data/testdata/‘;

grant read,write on directory dmp_dir to username;

设置directory文件路径

    1. su - oracle
  • 1.导出生产库 oracle数据

    进入127.0.0.1服务器
    导出: expdp TEST1/PITAYA_7Na1 file=/20180922.dmp directory=exp_dir schemas=TEST1;

  • 2.导入Oracle数据到测试库

    impdp TEST/PITAYA_7Na1_TEST file=/20180922.dmp directory=exp_dir REMAP_SCHEMA=TEST1:TEST;

设置plsql的快捷方式

sqlplus TEST/PITAYA_7Na1_TEST@orcl

打开PL/SQL,在Tools->Perferences->Editor中Autoreplaces选择配置的short.txt文件 ,然后重启一下

1
2
3
4
5
s = select * from 
w = where
sf = select t.*, t.rowid from
sc = select count(1) from
d = delete from

查询外键

1
2
3
select a.constraint_name, a.table_name, b.constraint_name  from user_constraints a, user_constraints b
where a.constraint_type = 'R' and b.constraint_type = 'P' and a.r_constraint_name = b.constraint_name
and a.constraint_name = 'FK_CRM_7'

锁表

查询锁表的对象

这个两个sql都是要在具有dba权限的用户下执行的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select t2.username,
t2.sid,
t2.serial#,
t3.object_name,
t2.OSUSER,
t2.MACHINE,
t2.PROGRAM,
t2.LOGON_TIME,
t2.COMMAND,
t2.LOCKWAIT,
t2.SADDR,
t2.PADDR,
t2.TADDR,
t2.SQL_ADDRESS,
t1.LOCKED_MODE
from v$locked_object t1, v$session t2, dba_objects t3
where t1.session_id = t2.sid
and t1.object_id = t3.object_id
order by t2.logon_time;

kill 锁表操作

1
ALTER system KILL session 'SID,serial#';

锁的级别

v$locked_object中的LOCKED_MODE字段表示锁的模式,oracle中锁的模式有如下几种:
0:none
1:null 空
2:Row-S 行共享(RS):共享表锁,sub share
3:Row-X 行独占(RX):用于行的修改,sub exclusive
4:Share 共享锁(S):阻止其他DML操作,share
5:S/Row-X 共享行独占(SRX):阻止其他事务操作,share/sub exclusive
6:exclusive 独占(X):独立访问使用,exclusive

数字越大锁级别越高, 影响的操作越多。

1级锁有:Select,有时会在v$locked_object出现。
2级锁有:Select for update,Lock For Update,Lock Row Share
select for update当对话使用for update子串打开一个游标时,所有返回集中的数据行都将处于行级(Row-X)独占式锁定,其他对象只能查询这些数据行,不能进行update、delete或select for update操作。
3级锁有:Insert, Update, Delete, Lock Row Exclusive
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
4级锁有:Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
00054, 00000, “resource busy and acquire with NOWAIT specified”
// Cause: Resource interested is busy.
//
Action: Retry if necessary.
5级锁有:Lock Share Row Exclusive
具体来讲有主外键约束时update / delete … ; 可能会产生4,5的锁。
6级锁有:Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive


查某session 正在执行的sql语句,从而可以快速定位到哪些操作或者代码导致事务一直进行没有结束等.

1
2
3
4
5
6
7
8
9
SELECT /*+ ORDERED */ 
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.sid = '67') /* 此处67 为SID*/
ORDER BY piece ASC;

plsql 工具 Tools —–>Sessions———> 查看

数据库比对工具

plsql Tools ———->Compare User Objects 选择另外一个库

Oracle 数据库表、字段级别,

select from user_tables t;–所有表
select
from all_tab_columns t;–系统
select from ALL_TAB_COLS t;–系统表
select
from cols t;–字段
select from USER_TAB_COLS t;–用户字段
select
from user_tab_comments t–所有表字段
select * from user_col_comments t;–所有表注释

SELECT INSTANCE_NAME FROM V$INSTANCE; –查询sid s_name

Oracle 方法等

  • t.createdate < to_date(‘2018-09-01’,’yyyy-mm-dd hh24:mi:ss’) 创建时间小于2018-09-01
  • to_char( t.CreateDate,’yyyyMMdd’)=to_char(sysdate,’yyyyMMdd’) 当天,类似当月yyyyMM当年yyyy
  • case f.state when 0 then ‘第壹’ when 2 then ‘第贰’ when 3 then ‘第叁’ else null end as 状态,
  • 逗号存ID,查询 “ INSTR((SELECT ‘,’||(deptId)||’,’ FROM Pop_Dept_DutyPeo WHERE userId=? AND grade=1 GROUP BY userId), ‘,’||prj.FK_DeptID||’,’) > 0 “
  • 列传行 select listagg(to_char(b.id),’,’) within group(order by b.orderId)
文章目录
  1. 1. 备份
    1. 1.0.1. MongoDB 备份
    2. 1.0.2. Oracle 备份
      1. 1.0.2.1. 共有多少张表:
    3. 1.0.3. 管理员
    4. 1.0.4. 设置directory
  • 2. 设置plsql的快捷方式
  • 3. 查询外键
  • 4. 锁表
    1. 4.0.1. 数据库比对工具
    2. 4.0.2. Oracle 数据库表、字段级别,
    3. 4.0.3. Oracle 方法等
  • ,