1、用户及表空间

--创建用户

create user jiading identified by jiading

default tablespace TS_BE3U

--授权

grant dba to jiading

--删除用户

drop user jkm cascade

--表空间扩容

alter database datafile 'D:\drgs\DRGSQY' resize 20480m;

2：创建数据表空间

create tablespace user_data 

logging 

datafile 'Q:\oracle\product\10.2.0\oradata\Test\xyrj_data.dbf'

size 50m 

autoextend on 

next 50m maxsize 20480m 

extent management local; 

--查看表空间使用

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;

--查看表空间使用

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;

----查看用户占用的表空间大小

select owner, tablespace_name, sum(bytes) / 1024 / 1024

  from dba_segments where tablespace_name='DRGSQY'

 group by owner, tablespace_name;

扩大表空间

alter database datafile 'D:\ORACLE\ORADATA\LYGL\SDE.DBF' resize 20480m;

增加新的数据文件

alter tablespace SDE add datafile 'D:\ORACLE\ORADATA\LYGL\SDE01.DBF' size 20480m;

设置表空间的自动增长

alter database datafile '? D:\ORACLE\ORADATA\LYGL\SDE01.DBF ? '?? autoextend on next 100m maxsize 20480m;

----查看表所在的表空间

 SELECT

  TABLE_NAME,

  TABLESPACE_NAME

FROM

  USER_TABLES;

  -----修改表所在的表空间

   alter table anhui.DEPARTMENT_20171013  move tablespace drgsqy

----   查看一个表所占用的空间

SELECT TABLESPACE_NAME,

       TO_CHAR(SUM(BYTES) / (1024 * 1024), '999G999D999') CNT_MB

  FROM DBA_EXTENTS

 WHERE OWNER = 'GUILIN'

   AND SEGMENT_NAME = 'T_DRG_ZY_MEDICALRECORD'

   AND SEGMENT_TYPE LIKE 'TABLE%'

 GROUP BY TABLESPACE_NAME;

2、锁表

查看锁表进程SQL语句1：

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;

查看锁表进程SQL语句2：

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

杀掉锁表进程：

如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCK

alter system kill session '738,1429';

3、查询正在执行的sql语句

SELECT b.sid oracleID,

       b.username 登录Oracle用户名,

       b.serial#,

       spid 操作系统ID,

       paddr,

       sql_text 正在执行的SQL,

       b.machine 计算机名

FROM v$process a, v$session b, v$sqlarea c

WHERE a.addr = b.paddr

   AND b.sql_hash_value = c.hash_value

4、查找重复数据

select * from t_drg_zy_medicalrecord a

where (a.usercode,a.bah,a.zycs) in

(select usercode,bah,zycs from t_drg_zy_medicalrecord

group by usercode,bah,zycs having

count(*) > 1 )

order by bah

--删除重复数据

delete from t_drg_zy_medicalrecord a

where (a.usercode,a.bah,a.zycs) in

(select usercode,bah,zycs from t_drg_zy_medicalrecord group by usercode,bah,zycs having

count(*) > 1)

and rowid not in

(select min(rowid) from t_drg_zy_medicalrecord group by usercode,bah,zycs having count(*)>1)

5、根据身份证号更新性别

--假设表test,字段sex是性别，cert_number是身份证号码，都是varchar2类型

--15位 最后一位奇数或偶数代表一个人的性别

update test t set t.sex = decode(mod(to_number(t.cert_number),2),0,'男','女')

where upper(t.cert_number) not like '%X' and length(t.cert_number) = 15;

--最后一位是X，且长度为15的，无法判断性别

update test t set t.sex = '最后一位是X，无法判断性别！'

where upper(t.cert_number) like '%X' and length(t.cert_number) = 15;

--18位 倒数第=位的奇数或偶数代表男性或女性

update test t set t.sex = decode(mod(to_number(substr(t.cert_number,17,1)),2),0,'男','女')

where length(t.cert_number) = 18;

6、导出dmp时空表无法导出

方法1、

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

方法2、

select 'alter table ' || table_name || ' allocate extent(size 64K);'

  from tabs t

 where not exists (select segment_name

          from user_segments s

         where s.segment_name = t.TABLE_NAME);

将结果语句导出，再执行导出语句

7、查询用户下的所有表及表说明

select t.table_name, f.comments

  from user_tables t

 inner join user_tab_comments f

on t.table_name = f.table_name;

8、锁用户

system登录

用户被锁可能原因：尝试多次失败登陆造成的被锁注：一般数据库默认是10次尝试失败后锁住用户

执行这个语句看看是那个ip多次访问导致的锁库：

select sessionid,userid,userhost,comment$text,spare1,ntimestamp# from sys.aud$ where returncode=1017

查看用户是否被锁：

select LOCK_DATE,username from dba_users where username='RCZYY'

解锁：

ALTER USER RCZYY ACCOUNT UNLOCK

9、查询各个表的数据量

select t.table_name,t.num_rows from all_tables t

查看记录数，num_rows不是实时的，存储的是上次分析后的值，不准确，要使用该方法，必须分析后才可以使用。

--1、创建oracle 函数---统计各个表的数据

create or replace function count_rows(table_name in varchar2,  

owner in varchar2 default null) 

return number 

authid current_user

IS

   num_rows number; 

   stmt varchar2(2000); 

begin

   if owner is null then

      stmt := 'select count(*) from "'||table_name||'"'; 

   else

      stmt := 'select count(*) from "'||owner||'"."'||table_name||'"'; 

   end if; 

   execute immediate stmt into num_rows; 

   return num_rows; 

end;

--2 、统计各个表的数据

select t.table_name, count_rows(t.table_name)  nrows from all_tables t

where t.OWNER='CDR'

order by nrows desc;