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;