oracle相关常用命令整合+补充

数据库相关命令整合补充
1. 用户相关
创建用户：create user username identified by passwd
通过授权的方式创建用户：grant connect,resource to test identified by test default tablespace users;
删除用户：drop user username cascade
给普通用户授权调试存储过程：GRANT debug any procedure, debug connect session TO usename;
查询用户密码有效期，LIMIT字段是密码有效天数：SELECT * FROM dba_profiles WHERE profile = 'DEFAULT’ AND resource_name = 'PASSWORD_LIFE_TIME';
将密码改成永不过期：alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
账号锁定后解锁：
Alter user username account unlock/lock;
将尝试登陆失败次数由默认的10次修改成“无限制”
Alter profile default limit FAILED_LOGIN_ATTEMPTS UNLIMITED
导出用户：exp name/password@ODBC owner=name file=name.dmp（保存数据）
exp name/password@ODBC owner=name file=name.dmp rows=n (不保存数据)
exp name/password@ODBC file=name.dmp fully=y(全部导出)
导入用户：imp name/password@mycon file=name.dmp full=yes
2. 表相关
创建表：create table tablename(字段，字段类型)
修改表：alter table tablename add 列名 字段类型
Drop column 列名
Alter column 列名 列名类型
查找表数据：select * from tablename [where column=x]
插入表数据：insert into tablename valuse (x,x)
删除表数据：delete from tablename [where column=x]
更新数据：update tablename set column=x [where column=y]
导出表: exp name/password@mycon tables=(table1,table2) file=tab1.dmp
导入表:imp system/password@mycon file= tab1.dmp tables=(table1,table2) touser=name
3. 表空间相关
//数据文件表：dba_data_files
//表空间表：dba_tablespaces
创建表空间：create tablespace name
删除表空间：drop tablespace name including contents and datafiles
查看表空间及对应的用户：select username,default_tablespace from dba_users
删除表空间同时删除文件：
DROP TABLESPACE ECS_DATA0412 INCLUDING CONTENTS AND DATAFILES;
查询表空间及文件：select * from v$datafile;
创建表空间：
create tablespace tableSpaceName datafile '< data_file_name >' size 1000M;
重新定义表空间数据文件大小：
alter database datafile '' resize 5000M;
改变表空间大小：
alter tablespace tableSpaceName add datafile '< data_file_name >' resize 1024M;
查询表空间与数据文件对应关系：
select tablespace_name, bytes, file_name from dba_data_files;
查询用户缺省表空间:
select username, default_tablespace from dba_users;
查询表与存储该表的表空间:
select table_name, tablespace_name from user_tables;
修改用户缺省表空间:
alter user username default tablespace tablespace_name;
查看那些表被锁住
select b.OWNER,b.OBJECT_NAME,a.SESSION_ID,a.LOCKED_MODE
from v$locked_object a,dba_objects b
where b.OBJECT_ID = a.OBJECT_ID;
查看被锁住表的sid和SERIALID
select b.USERNAME,b.SID,b.SERIAL#,b.LOGON_TIME
from v$locked_object a,v$session b
where a.session_id=b.SID
order by b.LOGON_TIME
将数据从一个表空间移动到另一个表空间:
alter table table_name move tablespace tablespace_name;
检查是否有业务用户的默认表空间设置在了system上。
select * from dba_users t where t.default_tablespace='SYSTEM';
检查是否有业务用户的表被创建在了system上：
select * from dba_tables w where w.owner not in ('SYSTEM', 'SYS', 'MGMT_VIEW','OUTLN') and
w.tablespace_name='SYSTEM';
查询表空间的数据文件大小
select tablespace_name,(bytes/1024/1024) M from dba_data_files;
查询数据文件已使用空间
select file_name, sum(e.bytes)/1024/1024 as MB from dba_extents e join dba_data_files f on e.file_id=f.file_id
group by file_name;
表空间使用率
select a.tablespace_name,
total / 1024 / 1024 / 1024,
free / 1024 / 1024 / 1024,
(total - free) / 1024 / 1024 / 1024,
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;
====
select b.tablespace_name,
total / 1024 / 1024 / 1024,
free / 1024 / 1024 / 1024,
(total - free) / 1024 / 1024 / 1024,
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;
====
select f.tablespace_name,
a.total,
u.used,
f.free,
round((u.used / a.total) * 100) "% used",
round((f.free / a.total) * 100) "% Free"
from (select tablespace_name, sum(bytes / (1024 * 1024)) total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) used
from dba_extents
group by tablespace_name) u,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) free
from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name
and (a.tablespace_name = '表空间1名' or a.tablespace_name = '表空间2名');
系统表空间占用TOP9
SELECT *
FROM (SELECT BYTES, segment_name, segment_type, owner
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY BYTES DESC)
WHERE ROWNUM < 10;
system空间中具体是什么对象占用较高：
select sum(bytes) as bt, s.owner, s.segment_name from dba_segments s where s.tablespace_name='SYSTEM'
group by s.owner, s.segment_name
order by sum(bytes) desc;
将审计功能关闭：
alter system set audit_trail='NONE' scope=spfile;
查询剩余表空间大小：
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
4. 启动、关闭、查看数据库监听
启动、关闭数据库：
sqlplus '/as sysdba'
shutdown abort;
startup;
启动、关闭、查看数据库监听进程：
lsnrctl status
lsnrctl start
杀进程中的会话
alter system kill session 'sid,SERIALID';