/*步骤1:删除 root 用户*/
drop user root cascade;		
/*步骤2:删除 TABLE_SPACE_TMP 临时表空间*/
drop tablespace TABLE_SPACE_TMP including CONTENTS and datafiles;
/*步骤3:删除 TABLE_SPACE 表空间*/
drop tablespace TABLE_SPACE including CONTENTS and datafiles;

/*步骤4:创建临时表空间*/
create temporary tablespace TABLE_SPACE_TMP  tempfile '/home/oracle/oradata/temp.dbf' size 128M autoextend on next 128M maxsize 512M extent management local;
/*步骤5:创建表空间*/
create tablespace TABLE_SPACE logging datafile '/home/oracle/oradata/tbs.dbf'  size 512M;
/*步骤6:创建 root 用户、密码 root， 表空间分配*/
create user root identified by root default tablespace TABLE_SPACE temporary tablespace TABLE_SPACE_TMP;
/*步骤7:创建表空间*/
grant connect,resource,dba to root;
/*步骤8:提交*/
commit;

/*获取表字段*/
select * 
from user_tab_columns 
where Table_Name='用户表' 
order by column_name

/*获取表注释*/
select * 
from user_tab_comments 
where Table_Name='用户表'
order by Table_Name

/*获取字段注释*/
select * 
from user_col_comments 
where Table_Name='用户表'
order by column_name

/* 获取表：*/
select table_name from user_tables; --当前用户的表，属性有 table_name,tablespace_name,last_analyzed等      
select table_name from all_tables; --所有用户的表，属性有 ower,table_name,tablespace_name,last_analyzed
select table_name from dba_tables; --包括系统表，属性有 ower,table_name,tablespace_name,last_analyzed
select table_name from dba_tables where owner='zfxfzb' 

/*  获取表字段：*/
select * from user_tab_columns where Table_Name='用户表';	--table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id
select * from all_tab_columns where Table_Name='用户表';     --ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id
select * from dba_tab_columns where Table_Name='用户表';     --ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id

/*  获取表注释：*/
select * from user_tab_comments   --table_name,table_type,comments

/* 获取字段注释：*/
select * from user_col_comments   --table_name,column_name,comments

/* 删除用户所有表 */
/*将会输出一批删除表的sql语句，这些SQL语句执行一下就可以了。*/
select 'drop table '||table_name||';' 
from cat 
where table_type='TABLE'

/* 查看表结构： 注意，表名需加引号*/
desc <table_name>  -- desc "test_table"