1、修改表字段类型
/*修改原字段名name为name_tmp*/ alter table CA_CaDocument rename column cd_taskids to cd_taskids_T ;
/*增加一个和原字段名同名的字段name*/ alter table CA_CaDocument add cd_taskids Nvarchar2(500);
/*将原字段name_tmp数据更新到增加的字段name*/ update CA_CaDocument set cd_taskids=trim(cd_taskids_T);
/*更新完，删除原字段name_tmp*/ alter table CA_CaDocument drop column cd_taskids_T;
2、查询表字段类型 某一类型
select TABLE_NAME,COLUMN_NAME from information_schema.columns WHERE DATA_TYPE LIKE ‘nvarchar‘
AND (CHARACTER_MAXIMUM_LENGTH=-1 OR CHARACTER_OCTET_LENGTH=-1) ORDER BY TABLE_NAME;
select table_name,column_name,data_type,DATA_LENGTH From all_tab_columns
where data_type =‘NCLOB‘
3、 查询oracle 行数
select
nvl(
case(t.table_name)
when Upper(‘T_InnerTestScoreStdConfig‘) then Upper(‘T_InnerTestScoreStandardConfig‘ )
end,
t.table_name),count_rows(table_name)
from user_tables t order by t.table_name desc
4、查询sql 行数
create table #t(name nvarchar(255), rows bigint, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20))
exec sp_MSforeachtable "insert into #t exec sp_spaceused ‘?‘"
select Upper(name), rows from #t where name in (‘T_SettlementInformItem‘,
‘PPM_SupplierConfirm‘,
‘CA_DocSign‘,
‘T_Todo‘,
‘T_Task‘,
‘SP_RectifyNodeInfo‘) order by name desc
drop table #t
5、数据导入临时表
SELECT * INTO new_table_name FROM old_tablename where ...... (表名不能以# 开头)
Oracle 遇到的问题 （2）
原文：http://www.cnblogs.com/huxiaoli/p/6101376.html
