小白最近把所有经常用到的sql语句整理了一下,如下:
1. 查找有哪些表空间
1. select TABLESPACE_NAME from dba_tablespaces;
2. select TABLESPACE_NAME from user_tablespaces;
3. select name from v$tablespace;
1.1 查看哪些用户在使用数据库:
select username from v$session;
1.2 查询每个用户的权限:
select * from dba_sys_privs where grantee='wsj';
1.3 把屏幕输出地结果导入到一个日志文件中:
spool /home/oracle/wsj/wsj.log;
select * from v$instance;
spool off;
1.4 查询有多少个数据库实例:
select * from v$instance;
1.5 查询执行一个命令所用的时间:
set timing on;
select * from all_users;
2. 查找有哪些用户
1. select * from all_users;
2. select username,account_status from dba_users;
3. 修改数据库的密码:
alter user imw identified by imw
如果密码中有特别的符号,比如说@,需要加双引号“”。
4. 检查数据库实例状态:
select STATUS from v$instance;
5. 查看数据库有哪些用户,或者查询具体用户信息
select username from dba_users(where username=upper('SUPPORT'))
6. 查看实例名:
select name from v$database;
7. 查看数据库用户:
select username,account_status from dba_users;
8. 查看数据库的是OPEN还是CLOSE。
select status from v$instance;
8.1 查看当前用户下的所有表:
select object_name from user_objects where object_type='TABLE';
8.2 如何知道数据库中某个表所在的tablespace?
select tablespace_name from user_tables where table_name='XIAOBAI';
8.3 修改表名:
alter table wushengjuan rename to xiaobaibai;
8.4 清空一个大表:
truncate table xiaobaibai;
8.5 创建索引:
create index jobs on xiaobaibai;
8.6 删除索引:
drop index jobs;
9. 查看数据库的磁盘组的信息:
select name from v$asm_diskgroup;
10. 查看数据库的磁盘组是否处于挂载状态:(需要先切换到grid用户执行asmcmd进入磁盘组挂载查询的)
lsdg;
quit;
11. 挂载磁盘组:
alter diskgroup dg_name mount;
12. 删除数据库的磁盘组:
drop diskgroup dg_name including contents;
13. set verify off: 不显示替代变量被替代前后的语句。
14. set verity on: 显示变量前后语句。
15. set echo on : 显示出文件中的每一条命令和该命令执行的结果,若设为set echo off,只显示命令执行的结果,不显示命令本身。
16. 创建表空间:
create tablespace 表空间名 datafile '表空间文件路径\表空间文件名.dbf' size 32m autoextend on next 32m maxsize 1024m;
17. spool记录日志的文件。
18. 日志文件spool命令,用spool off进行结尾。
19. select username from dba_users where ACCOUNT_STATUS in ('OPEN','LOCKED','EXPIRED') and username not in ('SYS','SYSTEM');
查找那些有状态“open,locked,expired”,并且用户名不为“sys”和“system”的。
20. 循环:
第一种:
loop
statament1;
exit when condition;
statament2;
end loop;
第二种:
for i in 0...n loop
statement1;
exit when condition;
statament;
end loop;
20. select from dual; dual是虚拟表,供测试使用。
21. 清除数据库资源:
srvctl config database
22. 删除数据库实例名:
dbca -silent -deleteDatabase -sourceDB seedb
注意:需要以oracle用户来执行。
23. 解锁用户:
alter user see account unlock;
24. 查询的结果赋值给定义的变量emp_data:
select empno,ename,job into emp_data from emp where empno=7369;
25. 创建一个表 此表的类型与emp表的类型一致
type table_emp is table of emp%rowtype
26. 查看哪些表空间下有哪些表:
select table_name from dba_tables where tablespace_name='WSJTABLESPACE';
27. 查看当前用户有哪些分区表:
select table_name from user_part_tables;
28. 查询当前用户下有哪些分区索引:
SELECT * FROM USER_PART_INDEXES;
29. 扩展表空间大小:
alter sysaux datafile '/opt/oracle/oradata/sdp/systemo1.dbf' resize 1000M;
30. 查看数据库实例的状态:
select status from v$instance;
1. 查看用户的角色对应的权限:
select * from role_sys_privs;
2. 用户默认的表空间:
select username,default_tablespace from dba_users;
3. 查看数据库表空间的大小(占用的,未用的)
select a.tablespace_name,a.bytes/1024/1024 "Sum MB" ,(a.bytes-b.bytes)/1024/1024 "used MB" ,b.bytes/1024/1024 "free MB" ,round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used" from (select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name) a,(select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc;
4. 串接字符串
select concat(A参,B参)||C参 别名 from 表名;
5. 扩容表空间:
alter tablespace WSJTABLESPACE add datafile '/opt/oracle/oradata/WSJ/WSJTableSpace04.dbf' size 1024M autoextend off;
6. 查看触发器:
查all_triggers表得
trigger_name
select trigger_name from all_triggers where table_name='XXX';
7. 查看数据库有哪些角色:
select * from dba_roles;
8. 查看表空间对应的数据文件:
select file_name,tablespace_name from dba_data_files where tablespace_name='WSJIDXTABLESPACE';
9. 查看APP_USER对应了哪些用户:
select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTEE='WSJ';
select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTED_ROLE='WSJ_USER';
10. 查看APP_USER所对应的权限;
select role,privilege from role_sys_privs where role='WSJ_USER';
11. 查看see用户对应哪些权限:
select * from session_privs;
12. 取消权限:
revoke select on sys.v_$session from see;
13. 还原密码复杂度:
alter profile default limit password_verify_function VERIFY_FUNCTION_OTHER;
14. 查看表的分区:
select PARTITION_NAME from ALL_TAB_PARTITIONS where TABLE_NAME='CONTENTINFO_HIS' and TABLE_OWNER='WSJ';
15. 删除锁定表的会话:
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
16. 查看数据库字符集:
select * from nls_database_parameters;
18. 查看数据库中读写目录命令:
select * from dba_directories;
19. 查看数据库系统时间:
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
20. 查询数据库当前进程的连接数:
select count(*) from v$process;
21. 查看数据库当前会话的连接数:
select count(*) from v$session;