1. 1、查看表空间的名称及大小
2. select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
3. from dba_tablespaces t, dba_data_files d
4. where t.tablespace_name = d.tablespace_name
5. group by t.tablespace_name;
6.
7. 2、查看表空间物理文件的名称及大小
8. select tablespace_name, file_id, file_name,
9. round(bytes/(1024*1024),0) total_space
10. from dba_data_files
11. order by tablespace_name;
12.
13. 3、查看回滚段名称及大小
14. select segment_name, tablespace_name, r.status,
15. (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
16. max_extents, v.curext CurExtent
17. From dba_rollback_segs r, v$rollstat v
18. Where r.segment_id = v.usn(+)
19. order by segment_name ;
20.
21. 4、查看控制文件
22. select name from v$controlfile;
23.
24. 5、查看日志文件
25. select member from v$logfile;
26.
27. 6、查看表空间的使用情况
28. select sum(bytes)/(1024*1024) as free_space,tablespace_name
29. from dba_free_space
30. group by tablespace_name;
31. SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
32. (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
33. FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
34. WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
35.
36. 7、查看数据库库对象
37. select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
38.
39. 8、查看数据库的版本
40. Select version FROM Product_component_version
41. Where SUBSTR(PRODUCT,1,6)='Oracle';
42.
43. 9、查看数据库的创建日期和归档方式
44. Select Created, Log_Mode, Log_Mode From V$Database;
45.
46. 10、捕捉运行很久的SQL
47. column username format a12
48. column opname format a16
49. column progress format a8
50. select username,sid,opname,
51. round(sofar*100 / totalwork,0) || '%' as progress,
52. time_remaining,sql_text
53. from v$session_longops , v$sql
54. where time_remaining <> 0
55. and sql_address = address
56. and sql_hash_value = hash_value
57. /
58.
59. 11。查看数据表的参数信息
60. SELECT partition_name, high_value, high_value_length, tablespace_name,
61. pct_free, pct_used, ini_trans, max_trans, initial_extent,
62. next_extent, min_extent, max_extent, pct_increase, FREELISTS,
63. freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,
64. empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
65. last_analyzed
66. FROM dba_tab_partitions
67. --WHERE table_name = :tname AND table_owner = :towner
68. ORDER BY partition_position
69.
70. 12.查看还没提交的事务
71. select * from v$locked_object;
72. select * from v$transaction;
73.
74. 13。查找object为哪些进程所用
75. select
76. p.spid,
77. s.sid,
78. s.serial# serial_num,
79. s.username user_name,
80. a.type object_type,
81. s.osuser os_user_name,
82. a.owner,
83. a.object object_name,
84. decode(sign(48 - command),
85. 1,
86. to_char(command), 'Action Code #' || to_char(command) ) action,
87. p.program oracle_process,
88. s.terminal terminal,
89. s.program program,
90. s.status session_status
91. from v$session s, v$access a, v$process p
92. where s.paddr = p.addr and
93. s.type = 'USER' and
94. a.sid = s.sid and
95. a.object='SUBSCRIBER_ATTR'
96. order by s.username, s.osuser
97.
98. 14。回滚段查看
99. select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents
100. Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,
101. v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,
102. sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,
103. v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and
104. v$rollstat.usn (+) = v$rollname.usn order by rownum
105.
106. 15。耗资源的进程(top session)
107. select s.schemaname schema_name, decode(sign(48 - command), 1,
108. to_char(command), 'Action Code #' || to_char(command) ) action, status
109. session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,
110. nvl(s.username, '[Oracle process]') user_name, s.terminal terminal,
111. s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process p
112. where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'
113. or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc
114.
115. 16。查看锁(lock)情况
116. select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,
117. decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',
118. 'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,
119. o.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,
120. 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)
121. lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2
122. from sys.dba_objects o, ( select s.osuser, s.username, l.type,
123. l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,
124. v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner
125. <> 'SYS' order by o.owner, o.object_name
126.
127. 17。查看等待(wait)情况
128. SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value
129. FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets',
130. 'consistent gets') group by v$waitstat.class, v$waitstat.count
131.
132. 18。查看sga情况
133. SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC
134.
135. 19。查看catched object
136. SELECT owner, name, db_link, namespace,
137. type, sharable_mem, loads, executions,
138. locks, pins, kept FROM v$db_object_cache
139.
140. 20。查看V$SQLAREA
141. SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,
142. VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,
143. USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,
144. BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA
145.
146. 21。查看object分类数量
147. select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,
148. 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity from
149. sys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3
150. , 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select
151. 'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from
152.
153. 22。按用户查看object种类
154. select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes,
155. sum(decode(o.type#, 2, 1, NULL)) tables, sum(decode(o.type#, 3, 1, NULL))
156. clusters, sum(decode(o.type#, 4, 1, NULL)) views, sum(decode(o.type#, 5, 1,
157. NULL)) synonyms, sum(decode(o.type#, 6, 1, NULL)) sequences,
158. sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))
159. others from sys.obj$ o, sys.user$ u where o.type# >= 1 and u.user# =
160. o.owner# and u.name <> 'PUBLIC' group by u.name order by
161. sys.link$ union select 'CONSTRAINT' , count(*) from sys.con$
162.
163. 23。有关connection的相关信息
164. 1)查看有哪些用户连接
165. select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command),
166. 'Action Code #' || to_char(command) ) action, p.program oracle_process,
167. status session_status, s.terminal terminal, s.program program,
168. s.username user_name, s.fixed_table_sequence activity_meter, '' query,
169. 0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num
170. from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'
171. order by s.username, s.osuser
172.
173. 2)根据v.sid查看对应连接的资源占用等情况
174. select n.name,
175. v.value,
176. n.class,
177. n.statistic#
178. from v$statname n,
179. v$sesstat v
180. where v.sid = 71 and
181. v.statistic# = n.statistic#
182. order by n.class, n.statistic#
183.
184. 3)根据sid查看对应连接正在运行的sql
185. select /*+ PUSH_SUBQ */
186. command_type,
187. sql_text,
188. sharable_mem,
189. persistent_mem,
190. runtime_mem,
191. sorts,
192. version_count,
193. loaded_versions,
194. open_versions,
195. users_opening,
196. executions,
197. users_executing,
198. loads,
199. first_load_time,
200. invalidations,
201. parse_calls,
202. disk_reads,
203. buffer_gets,
204. rows_processed,
205. sysdate start_time,
206. sysdate finish_time,
207. '>' || address sql_address,
208. 'N' status
209. from v$sqlarea
210. where address = (select sql_address from v$session where sid = 71)
211.
212. 24.查询表空间使用情况
213. select a.tablespace_name "表空间名称",
214. 100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",
215. round(a.bytes_alloc/1024/1024,2) "容量(M)",
216. round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",
217. round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
218. Largest "最大扩展段(M)",
219. to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"
220. from (select f.tablespace_name,
221. sum(f.bytes) bytes_alloc,
222. sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
223. from dba_data_files f
224. group by tablespace_name) a,
225. (select f.tablespace_name,
226. sum(f.bytes) bytes_free
227. from dba_free_space f
228. group by tablespace_name) b,
229. (select round(max(ff.length)*16/1024,2) Largest,
230. ts.name tablespace_name
231. from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
232. where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
233. group by ts.name, tf.blocks) c
234. where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name;
235. --还有下面这句
236. SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
237. D.TOT_GROOTTE_MB "表空间大小(M)",
238. D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
239. TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
240. 2),
241. '990.99') "使用比",
242. F.TOTAL_BYTES "空闲空间(M)",
243. F.MAX_BYTES "最大块(M)"
244. FROM (SELECT TABLESPACE_NAME,
245. ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
246. ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
247. FROM SYS.DBA_FREE_SPACE
248. GROUP BY TABLESPACE_NAME) F,
249. (SELECT DD.TABLESPACE_NAME,
250. ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
251. FROM SYS.DBA_DATA_FILES DD
252. GROUP BY DD.TABLESPACE_NAME) D
253. WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
254. ORDER BY 4 DESC;
255.
256. 25. 查询表空间的碎片程度
257. select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
258. having count(tablespace_name)>10;
259. alter tablespace name coalesce;
260. alter table name deallocate unused;
261. create or replace view ts_blocks_v as
262. select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
263. union all
264. select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
265. select * from ts_blocks_v;
266. select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
267. group by tablespace_name;
268.
269. 26.查看有哪些实例在运行:
270. select * from v$active_instances;
271. 其它:
272. 设置一列的宽度:col colname format a10;
273. 设置一页的行数:set pagesize 50;
修改字段名
alter table [表名] rename column 旧的字段名 to 新的字段名;
1.删除表中数据两种方法
a. delete * from My_Table;
b. truncate table My_Table;
2.删除整个表
drop table My_Table;
如何恢复不小心 Drop 掉的表呢,其实 Oracle 中也有类似的 "回收站"
比如不小心删除了表 My_Table
drop table My_Table;
这个时候再用SELECT语句查询此表时,将会提示表或视图不存在.
但可以用如下语句查询到这个表还在Oracle回收站中:
SELECT * FROM user_recyclebin WHERE original_name='My_Table';
那么现在就可以用如下语句进行恢复:
FLASHBACK TABLE My_Table TO BEFORE DROP;
TAG Oracle 删除 恢复
分享到:
相关推荐
oracle 常用命令大全 oracle dba 常用命令 1 运行 SQLPLUS 工具 sqlplus 2 以 OS 的默认身份连接 / as sysdba 3 显示当前用户名 show user 4 直接进入 SQLPLUS 命令提示符 sqlplus /nolog 5 在命令提示符以 OS 身份...
### Oracle 常用命令与操作指南 #### 一、Oracle 安装与卸载注意事项 **1.1 Oracle 的安装** - **安装步骤:** 对于 Oracle 的安装,网络上有很多详细的指导教程。在安装过程中,需要注意的是,管理口令部分确保...
### ORACLE常用命令详解 #### 一、ORACLE的启动和关闭 在ORACLE数据库管理中,正确地启动和关闭数据库是非常重要的操作之一。这些操作不仅涉及到系统的稳定性,还关系到数据的安全性。 ##### 1、在单机环境下 ...
ORACLE常用命令 一、ORACLE的启动和关闭 1、在单机环境下 要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下 su - oracle a、启动ORACLE系统 oracle>svrmgrl SVRMGR>connect internal SVRMGR>startup ...
以下将详细解析基于提供的文件信息中的Oracle常用命令,涵盖日志管理和表空间管理两大核心领域,以帮助数据库管理员更好地理解和运用这些关键指令。 ### 日志管理 #### 强制日志切换 `ALTERSYSTEMSWITCHLOGFILE;` ...
natstat,oracle常用命令常用命令natstat,oracle常用命令常用命令natstat,oracle常用命令常用命令natstat,oracle常用命令常用命令natstat,oracle常用命令常用命令
linux,linux和Oracle常用命令linux环境配置ORACLE常用命令
以下是一些关于Oracle常用命令的详细说明,分为日志管理和表空间管理两大部分。 ### 日志管理 #### 1. 强制日志切换 ```sql alter system switch logfile; ``` 这个命令用于在当前重做日志文件填满前强制进行日志...
### Oracle常用命令详解 #### 一、Oracle的启动与关闭 **1. 在单机环境下的启动与关闭** - **启动Oracle系统** - **切换用户:** 首先需要切换到Oracle用户环境。 ```bash su - oracle ``` - **使用`svrmgrl`...
oracle常用命令收集 oracle常用命令
Oracle常用命令大汇总 Oracle是世界上最流行的关系数据库管理系统之一,由Oracle Corporation开发和维护。作为一个功能强大的数据库管理系统,Oracle提供了许多实用的命令来帮助数据库管理员和开发者更好地管理和...
第一章:日志管理 1.forcing log switches sql> alter system switch logfile; 2.forcing checkpoints sql> alter system checkpoint; 3.adding online redo log groups sql> alter database add log...
Oracle创建删除用户、角色、表空间、导入导出数据库命令行方式总结.pdf
ORACLE常用命令举例 ORACLE常用命令举例 ORACLE常用命令举例 ORACLE常用命令举例 ORACLE常用命令举例
### ORACLE常用命令集 #### 一、日志管理 在Oracle数据库中,日志管理是维护数据库稳定性和完整性的重要组成部分。以下是一些常见的Oracle日志管理命令。 **1. 强制日志切换** - **命令**: `alter system ...
### Oracle常用命令详解 #### 一、Oracle的启动与关闭 在管理Oracle数据库时,启动与关闭数据库是最基本的操作之一。这些操作确保了数据库服务的可用性和安全性。 **1. 单机环境下的启动与关闭** - **切换用户...