Oracle 遇到的問題 （2）

標籤：reserve   select   bsp   cad   desc   alt   表名   sel   rect   

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_columnswhere  data_type =‘NCLOB‘ 3、 查詢oracle 行數selectnvl(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）