oracle维护常用命令收集
查询数据库表空间容量和数据量
创新互联服务项目包括衡阳县网站建设、衡阳县网站制作、衡阳县网页制作以及衡阳县网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，衡阳县网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到衡阳县省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！
SELECT a.tablespace_name ,
total,
free,
(total - free),
total / (1024 * 1024 * 1024) "TS(G)",
free / (1024 * 1024 * 1024) "TSfree(G)",
(total - free) / (1024 * 1024 * 1024) "TSuse(G)",
round((total - free) / total, 4) * 100 "BaiFenBi %"
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
ASMCMD查询存储使用情况
ls -s
lsdg
du
导出数据
create or replace directory data as '/home/oracle/imp'
Grant read,write on directory data to test1;
expdp test1/test1@pdborcl directory=data dumpfile=test1.dump logfile=test1.log schemas=test1
impdp test1/test1@pdborcl directory=data dumpfile=test1.dump logfile=test1.log tables=tab_t1
拼接sql语句
select 'select * from dba_tab_privs where grantee='|| username ||';' from dba_users;
select 'select * from dba_tab_privs where grantee='''||username||''';' from dba_users;
select 'insert into table_name values('''||field_1||''','''||field_2||''');'from table_name where field_n=some_conditon;
http://www.cppblog.com/fwxjj/archive/2011/10/17/158567.html查询非系统用户
select username from dba_users where username not in
('SYS'
,'SYSTEM'
,'OUTLN'
,'FLOWS_FILES'
,'MDSYS'
,'ORDSYS'
,'EXFSYS'
,'DBSNMP'
,'WMSYS'
,'APPQOSSYS'
,'APEX_030200'
,'ORDDATA'
,'CTXSYS'
,'ANONYMOUS'
,'XDB'
,'ORDPLUGINS'
,'SI_INFORMTN_SCHEMA'
,'OLAPSYS'
,'ORACLE_OCM'
,'XS$NULL'
,'MDDATA'
,'DIP'
,'APEX_PUBLIC_USER'
,'SPATIAL_CSW_ADMIN_USR'
,'SPATIAL_WFS_ADMIN_USR');
把某用户的对象授权给另一用户及撤销
select 'grant all on A1.'||table_name||' to B1;' from dba_tables where owner='A1';
select 'revoke all on A1.'||table_name||' from B1;' from dba_tables where owner='A1';
alter user A1 default_tablespace='B1';
本文标题：oracle维护常用命令收集
网址分享：http://shouzuofang.com/article/gphihg.html