所有的语句都是经过本人确认的可用的(在9i和10g上)
1.取表的所有字段信息
select t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH
from all_tab_columns t
where t.OWNER=upper('ww_xrapuser') and t.TABLE_NAME=upper('ry_jbxx')
2.取所有可用用户
select username
from dba_users t
where t.account_status='OPEN' and t.initial_rsrc_consumer_group='DEFAULT_CONSUMER_GROUP'
order by username
3.取用户下的所有表信息
select T.OBJECT_NAME
from SYS.all_objects t
where t.object_type='TABLE' and t.owner='CTXSYS'
order by T.OBJECT_NAME
4.取所有索引信息
select * from sys.all_indexes t where t.owner='CTXSYS' AND T.INDEX_TYPE='DOMAIN';
5. 取数据库字符集信息
select userenv('language') from dual
6.如何查询dmp文件的字符集
用oracle的exp工具导出的dmp文件也包含了字符集信息,dmp文件的第2和第3个字节记录了dmp文件的字符集。如果dmp文件不大,比如只有几M或几十M,可以用UltraEdit打开(16进制方式),看第2第3个字节的内容,如0354,然后用以下SQL查出它对应的字符集:
select nls_charset_name(to_number('0354','xxxx')) from dual
7.建立一个备份控制文件
Alter database backup control file to trace
8.产生随机数,dbms_random带小数,trunc取整
trunc(dbms_random.value(1,1000))
9.授权
grant select any table to giapsys;
grant update any table to giapsys;
grant delete any table to giapsys;
grant insert any table to giapsys;
select 'grant all on '||table_name||' to giapsys' from user_tables;
grant all on bpm_wf_processmodel to giapsys;
grant select on bpm_wf_processmodel to giapsys;
10.操作GLOBAL_NAME,这个设置上true表示数据链路名要和目标数据库名一样
SELECT * FROM GLOBAL_NAME
ALTER SYSTEM SET GLOBAL_NAMES = FALSE
11.查看用户下所有约束
select * from user_constraints t;
select * from user_cons_columns t;
12. 查看系统最大打开游标数
SELECT v.name, v.value value FROM V$PARAMETER v WHERE name = 'open_cursors'
如果value小于300 就用下面的语句增大就行
alter system set open_cursors = 10000
13 回复删除的表,恢复drop的表
FLASHBACK TABLE My_Table TO BEFORE DROP;
14 ORA-12519, TNS:no appropriate service handler found
这个问题,通过以下几步确认
有时候连得上数据库,有时候又连不上。
可能是数据库上当前的连接数目已经超过了它能够处理的最大值。
select count(*) from v$process --当前的连接数
select value from v$parameter where name = 'processes' --数据库允许的最大连接数
修改最大连接数:
alter system set processes = 300 scope = spfile;
重启数据库:
shutdown immediate;
startup;
--查看当前有哪些用户正在使用数据
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
15 查询数据库一个用户下所有表中数据的总数
select sum(num_rows) from user_tables;
16 创建表空间
create tablespace ZHK_BUSINESS datafile '/u01/app/oracle/oradata/qbdb/kk.dbf' size 100M
17 查看用户登录数据库的情况
select username,status,schemaname,osuser,program,service_name,machine,terminal from v$session;
18 造数据
declare
i integer;
begin
for i in 1..200 loop
insert into ErrorLogs
(UserName,UserRealName,Department,FormName,EventName,ErrorInfo,eComment,eflag)
values(i,'林庆涛','中国国家科学院您的设置。','窗体6','Event4','错
误信息3 错误信息3','备注3','1');
end loop;
commit;
end;
--===================================================
declare
i integer;
begin
for i in 1..1000000 loop
insert into temp_table(userid) values(i);
end loop;
commit;
end;
--================================================================
19 管理序列
创建序列
create sequence LQTTEST minvalue 0 maxvalue 99999999 start with 1 increment by 1 cache 20
重建序列
一种方法是重建。另一种方法是改变increment.
可以先select一下nextval,假如为888888,现在要重置为1,假设你原来的increment也是1。
alter sequence seq_name increment by -888887;
然后select seq_name.nextval from dual;
就把currval重置为1了。
这种方法的前提是在操作时不能有其他人在引用该序列。
完了之后应该把increment改回去。
另一种方法,drop序列,recreate序列。
drop sequence LQTTEST
create sequence LQTTEST minvalue 0 maxvalue 99999999 start with 1 increment by 1 cache 20
20. 如何查看Oracle9i客户端的版本号
Microsoft Windows XP [版本 5.1.2600] (C) 版权所有 1985-2001 Microsoft Corp. C:\Documents and Settings\Administrator>sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on 星期二 7月 9 08:29:21 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL>
select * from v$Version;
21. 查询一个字段中包含字母或者数字
select * from aj_jbxx t where REGEXP_LIKE(t.ajbh, '[A-Z]{1}');
22. 创建数据库链路
CREATE PUBLIC DATABASE LINK xrapetl14 CONNECT TO xrapetl identified by xrapetl using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME=orcl)))'
23. 数据库表锁定与解锁
#用这个语句查询出被锁定的sessionid
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial# ;
#用这个语句杀死这个事物
alter system kill session 'sid,serial#';
#实例
alter system kill session '274,472';
24. 取oracle所有的关键字
select * from v$reserved_words;
25. oracle用户被锁定后怎样解锁?
ALTER USER username ACCOUNT UNLOCK;
26. ORACLE中怎么查一条纪录被哪个用户锁了?
select s.sid,s.machine,o.object_name,l.oracle_username,l.locked_mode,
'ALTER SYSTEM KILL SESSION '''||s.sid||', '||s.serial#||''';' Command
from v$locked_object l,v$session s,all_objects o
where l.session_id=s.sid and l.object_id=o.object_id
27. session操作
查看所有的session信息
select * from v$session;
在命令窗口执行
show parameter session;
查看系统设置的参数。
可以看到session的最大数量。
28. 查看行存储的表占用数据库空间
select bytes/1024/1024 MB from DBA_segments where segment_name='AJ_JBXX';
29. 查看oracle 12c列存储表占用空间
select * from v$inmemory_area;
30. 查看表空间的名称及大小
select t.tablespace_name,
round(sum(d.bytes/(1024*1024)),0) tbs_size
from dba_tablespaces t,
dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
若需要查看指定表空间的大小,则可以直接添加where条件指定,否则结果集是所有表空间。
31. 查看表空间物理文件的名称及大小
select tablespace_name,
file_id,
file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
32. 查看表空间剩余空间大小
select tablespace_name,
round(sum(bytes) / 1024 / 1024, 2) AS free_space,
count(*) AS extends,
sum(blocks) AS blocks
from dba_free_space
group by tablespace_name;
33. 查看表空间使用率
SELECT total.tablespace_name,
Round(total.total, 2) AS Total_MB,
Round(total.total - free.free, 2) AS Used_MB,
Round(( 1 - free.free / total.total ) * 100, 2)
|| '%' AS Used_PCT
FROM (SELECT tablespace_name,
sum(bytes) / 1024 / 1024 AS free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
sum(bytes) / 1024 / 1024 AS total
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
34. 查看控制文件
select name from v$controlfile;
35. 查看日志文件
select member from v$logfile;
36. 查看消耗资源最多的SQL
SELECT hash_value,
executions,
buffer_gets,
disk_reads,
parse_calls
FROM V$SQLAREA
WHERE buffer_gets > 10000000
OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC;
37. 捕捉运行很久的SQL
select username,
sid,
opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,
sql_text
from v$session_longops,
v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value;
38. 查看回滚段名称及大小
select segment_name,
tablespace_name,
r.status,
(initial_extent/1024) InitialExtent,
(next_extent/1024) NextExtent,
max_extents,
v.curext CurExtent
from dba_rollback_segs r,
v$rollstat v
where r.segment_id = v.usn(+)
order by segment_name;
39. 查看还没提交的事务
select * from v$locked_object;
select * from v$transaction;
待续