oracle基本操作
看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;
查看用户下所有的表
SQL>select * from user_tables;
查看名称包含log字符的表
SQL>select object_name,object_id from user_objects
where instr(object_name,'LOG')>0;
查看某表的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');
查看索引的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&index_name');
查看函数和过程的源代码
SQL>select text from all_source where ōwner=user and name=upper('&plsql_name');
正常的rollback一般可以通过v$transaction估算rollback进度，
死事务已经无法通过v$transaction来观察，所以必须通过内部表来进行判断。
这个内部表是x$ktuxe
方法1：
select ff.s tablespace_name,
ff.b total,
(ff.b - fr.b) usage,
fr.b free,
round((ff.b - fr.b) / ff.b * 100) || '% ' usagep
from (select tablespace_name s, sum(bytes) / 1024 / 1024 b
from dba_data_files
group by tablespace_name) ff,
(select tablespace_name s, sum(bytes) / 1024 / 1024 b
from dba_free_space
group by tablespace_name) fr
where ff.s = fr.s
方法2：
select a.owner,a.segment_name,segment_type,sum(bytes)/1024 as SizeM From dba_segments a
inner join (
select owner,segment_name from dba_lobs where table_name ='XI_AF_MSG' union all
select owner,index_name as segment_name from dba_indexes where table_name ='XI_AF_MSG' union all
select owner,segment_name from dba_segments where segment_name='XI_AF_MSG'
) b on a.owner=b.owner and a.segment_name=b.segment_name
group by a.owner,a.segment_name,segment_type
order by a.segment_name
select segment_name, bytes
from user_segments
where segment_type = 'TABLE';
或者
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
另一种表实际使用的空间。这样查询：
analyze table emp compute statistics;
select num_rows * avg_row_len
from user_tables
where table_name = 'EMP';
查看每个表空间的大小
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name
ALTER TABLESPACE tablespace_name ADD DATAFILE 'file path & file name' SIZE size;
给表空间增加数据文件。
ALTER DATABASE DATAFILE 'file path & file name' RESIZE size;
把表空间现有的数据文件改变大小
orcle用户连接：
用系统管理员，查看当前数据库有几个用户连接：
SQL> select username,sid,serial# from v$session;
如果要停某个连接用
SQL> alter system kill session 'sid,serial#';
如果这命令不行,找它UNIX的进程数
SQL> select pro.spid from v$session ses,v$process pro where ses.sid=&sid and ses.paddr=pro.addr;
然后用 kill 命令杀此进程号。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15127599/viewspace-623395/ ，如需转载，请注明出处，否则将追究法律责任。
