据库常用SQL语句汇总
1.数据库管理
1.1查询物理表空间大小
select * from (
Select a.tablespace_name,
to_char(a.bytes/1024/1024,'999,999,999.999') total_bytes,
to_char(b.bytes/1024/1024,'999,999,999.999') free_bytes,
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'999,999,999.999') use_bytes,
to_char((1 - b.bytes/a.bytes)*100,'999,999.99') || '%' use
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes/1024/1024,'999,999,999.999') total_bytes,
to_char( (c.bytes-d.bytes_used)/1024/1024,'999,999,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'999,999,999.999') use_bytes,
to_char(d.bytes_used*100/c.bytes,'999,999.99') || '%' use
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
) order by 5 desc;
1.2实时查询临时表空间大小
select ss.tablespace_name "表空间名称"
,sum((ss.used_blocks*ts.blocksize))/1024/1024/1024 "使用大小(G)"
from GV$SORT_SEGMENT ss,sys.ts$ ts
where ss.tablespace_name=ts.name
group by ss.tablespace_name
order by 1 desc;
1.3查询物理表空间位置
SELECT tablespace_name, file_id, file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
1.4创建表空间
/ora_ursp/ursp_tbl/URSP_ODM_DATA_01 为物
CREATE TABLESPACE URSP_ODM_DATA DATAFILE
'/ora_ursp/ursp_tbl/URSP_ODM_DATA_01' size 20280M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
1.5删除表空间及其物理文件
DROP TABLESPACE URSP_ODM_DATA INCLUDING CONTENTS AND DATAFILES;
删除表空间的时需要先将表空间处于离线状态。
1.6扩展数据库物理表空间
引号内为数据库表空间物理文件存放绝对路径，size为这次扩展的表空间大小。
ALTER TABLESPACE SLSADMIN ADD DATAFILE '/u01/oradata/bsbczreport/slsadmin4.dbf' SIZE 2048m;
1.7查看表空间物理文件使用情况
select /*+ ordered use_hash(a,b,c) */
a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize canshrinksize
from (select file_id, file_name, round(bytes / 1024 / 1024) filesize
from dba_data_files) a,
(select file_id, round(sum(dfs.bytes) / 1024 / 1024) freesize
from dba_free_space dfs
group by file_id) b,
(select file_id, round(max(block_id) * 8 / 1024) HWMsize
from dba_extents
group by file_id) c
where a.file_id = b.file_id
and a.file_id = c.file_id
order by unsedsize_belowhwm desc
1.8收缩数据库物理表空间
收缩要先检查表空键物理文件使用情况，收缩空间不能小于已经被使用的空间，不然收缩回报错。
ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 7772M;
1.9创建用户
创建用户时可以执行用户的默认表空间及默认临时表空间
create user user_name identified by user_pwd;
create user zhangsan identified by zhangsan default tablespace myspace;
1.10修改用户默认表空间
ALTER DATABASE DEFAULT TABLESPACE tablespace_name;
ALTER DATABASE DEFAULT TEMPRORY TABLESPACE tablespace_name;
1.11赋权
grant 后接权限名称 to 后面接权限被赋用户
grant create session to user_name;
grant create table to user_name;
1.12去除用户权限
revoke 后接权限名称 to 后面接权限被去除用户
revoke create table from user_name;
1.13删除用户
此操作需要注意权限问题
drop user user_name;
1.14删除用户及用户所属对象
drop user user_name cascade;
1.15查看用户当前状态
select * from dba_users;
OPEN表示账户为解锁状态；EXPIRED表示账户为过期状态(需要设置口令才能解除此状态)；3LOCKED表示账户为锁定状态
1.16解锁用户
ALTER USER user_name ACCOUNT UNLOCK;
1.17查询表占的物理空间大小
select g.segment_name,g.bytes from user_segments g
where g.segment_type='TABLE'
order by g.bytes desc
1.18查询是否存在锁
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待以上的语句可以查询到谁锁了表，而谁在等待。以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN找出谁锁定的记录，kill掉就行了。
1.19 kill session语句
alter system kill session'50,492';
1.20使用imp/dump导入导出
export LANG=zh_CN.gbk
export NLS_LANG=AMERICAN_AMERICA.ZHS16GB
imp aml/aml@10.1.51.53/repdb file=ods_trandt_20170114.dmp ignore=y full=y
exp aml/xjnx_ursp_aml1@10.1.48.53/dwursp file=aml_autosumwran_20170925.dmp log=aml_autosumwran.log tables=aml_rule_autoslay,aml_warn_suminfo,aml_warn_wrule
注意字符集
2.数据库操作
2.1创建表
create table table_name
(
column_name colum_type,
column_name colum_type not null,
column_name colum_type primary key
);
2.2创建与已知表表结构相同的表
2.3创建分区表
2.4对表添加注释
comment on table table_name is '中文注释';
2.5对字段添加注释
comment on column table_name.column_name is '中文注释';
2.6对表添加主键
alter table table_name add constraint primary_key_name primary key (column_name1,column_name2);
2.7对标删除主键
alter table table_name drop constraint primary_key_name;
2.8对表创建索引
create index index_name on table_name(column_name1,column_name2);
2.9删除索引
drop index index_name on table_name;
2.10对表增加列
alter table table_name add column_name column_type;
alter table table_name add tab_size varchar2(20);
2.11对表增加多个列
alter table table_name add tab_size varchar2(20),tab_sex char(1);
2.12对表删除列,删除时保证该列为空
alter table table_name drop column_name;
2.13对表修改列的长度
alter table table_name modify (column_name colum_cycle);
2.14对表修改列的名称
alter table table_name rename old_column to new_column;
网友评论