注：从网上搜集的，备用
拷贝表结构
create table root.m_shipinfo2 as select * from root.m_shipinfo where 1=2
改表名
rename m_shipinfo2 to m_shipinfo3;
查看当前用户默认表空间
select username,default_tablespace from user_users;
查看表空间信息
select * from dba_data_files;
统计表空间大小
select tablespace_name, sum(bytes) / 1024 / 1024 from dba_data_files
group by tablespace_name;
增加数据文件到表空间并打开自动
alter tablespace USERS ADD DATAFILE '/oracle/oradata/orcl/USER02.DBF' size 5000m AUTOEXTEND ON;
原文件扩容并打开自动
alter tablespace datafile '/oracle/oradata/orcl/USER02.DBF' autoextend on
查看所有表空间文件路径（sysdba）
select tablespace_name, file_id,file_name,
round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
SELECT tablespace_name 表空间,sum(blocks*8192/1000000) 剩余空间M FROM dba_free_space GROUP BY tablespace_name;
select a.a1 表空间名称, c.c2 类型, c.c3 区管理,
b.b2/1024/1024 表空间大小M,
(b.b2-a.a2)/1024/1024 已使用M,
substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from
(select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1;
select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name
----用户
查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;
查看当前用户的角色
SQL>select * from user_role_privs;
查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
查看用户下所有的表
SQL>select * from user_tables;
----表
查看用户下所有的表
SQL>select * from user_tables;
查看名称包含log字符的表
SQL>select object_name,object_id from user_objects
where instr(object_name,'LOG')>0;
查看某表的创建时间
SQL>select object_name,created from user_objects where object_name=upper('&table_name');
查看某表的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');
查看放在Oracle的内存区里的表
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;