On the way

查询表信息   

select * from tab;

查询表空间信息  

select * from dba_data_files;

查询表在哪个表空间中 

select tablespace_name from dba_tables where table_name = your_tablename’  and owner=’表的owner’;

创建个用户表空间同时指定使用该表空间

create table your_table(name,type)  tablespace your_tablespacename(name,type);

表的创建时间

select object_name,created from user_objects where object_name=upper(‘&table_name’);

表的大小

select sum(bytes)/(1024*1024) as “size(M)” from user_segments
where segment_name=upper(‘&table_name’);

看各个表空间的自由空间

select tablespace_name,sum(bytes)/1024/1024 free_Mbytes

from dba_free_space

group by tablespace_name

order by free_Mbytes

查看各个表空间占用磁盘情况

SQL> col tablespace format a20
SQL> select
b.file_id 文件ID号,
b.tablespace_name 表空间名,
b.bytes 字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余空间,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id

快速做一个和原表一样的备份表

create table new_table as (select * from old_table);

查看表与表之间的关系

从两个数据字典中查，all_constraints(user_constraints)和all_cons_columns(user_cons_columns)  
  查外键：  
  select   *   from   all_constraints  
      where   constraint_type   =   ‘R’;  
  查外键中的关联字段：  
  select   *   from   all_cons_columns;

Related posts:

1. Oracle PL/SQL 3 days