oracle 显示所有表、SID、sqlplus连接数据库

mysql里有show tables; 显示当前数据库里的所有表

oracle里用：

返回所有表 
select table_name from all_tables; 
返回当前用户的所有表(1) 
select table_name from user_tables;
返回当前用户的所有表(2)
select table_name from tabs;


查询数据库SID

D:\oracle\product\10.2.0\oradata\SID文件夹...
或D:\oracle\product\10.2.0\db_1\dbs\SPFILESID名称.ORA

或者
select instance_name from v$instance; 

oracle 查询表名 comments及列名 comments

查询表的名字和comments

 select a.table_name,b.comments   from   user_tables a,ALL_TAB_COMMENTS b where a.table_name=b.table_name

 

查询字段的名字和comments

  select  distinct  a.COLUMN_NAME,b.comments from user_tab_columns a,user_col_comments b where a.TABLE_NAME=b.table_name and a.table_name=upper('A_COUNT');

 

 

sqlplus连接数据库

进入sqlplus：cmd--sqlplus
username/password@服务命名（本地配好的）

 

 

 

快速删除表数据，然后从其他表选择字段插入

select t.*, t.rowid from cc12 t
truncate table cc12;

insert into cc12 (ACC740, AAC001, AAB001, ACC741, ACC74M, ACC74J, ACC74A, ACC74B, ACC74C, ACC74D, ACC74E, AAE011, AAE017, AAE019, AAE036, AAE013, BCC74S, BCC74M, BCC74N, BCC741, BCC742, AAE101, AAE136, BCC743, BCC744, BCC745, BCC746, AAC021, BCC747)
select ACC740, AAC001, AAB001, ACC741, ACC74M, ACC74J, ACC74A, ACC74B, ACC74C, ACC74D, ACC74E, AAE011, AAE017, AAE019, AAE036, '被享受的档案号 '||CCC110, BCC74S, BCC74M, BCC74N, BCC741, BCC742, AAE101, AAE136, BCC743, BCC744, BCC745, BCC746, AAC021, BCC747
from zjla_user.cc12;

update cc12 set aae017 =f_to_sc01(aae017) ;
update cc12 set CCE001 =f_to_sc01(CCE001)
select count(*) from cc12;

转载于:https://www.cnblogs.com/Struts-pring/archive/2013/01/08/2850481.html