oracle常用sql语句整理

最新推荐文章于 2022-05-31 09:23:35 发布

最新推荐文章于 2022-05-31 09:23:35 发布 · 223 阅读

文章标签：

#SQL #Oracle

学习专栏收录该内容

43 篇文章

订阅专栏

本文提供了一系列Oracle数据库管理的实用技巧，包括查询表字段信息、获取可用用户、恢复已删除的表等，帮助管理员高效地进行数据库维护。

所有的语句都是经过本人确认的可用的（在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;

待续