UKoom

今天一个任务是从两张表中用sql语句导出些数据，并且提交这些数据的插入语句。上网查询后找到个方法，例子如下： spool c:\data.sql; select 'insert into table_name values('''||field_1||''','''||field_2||''');'from table_name where field_n=some_conditon; spool off; 从这个方案中引申学到两条：spool命令和使用select语句批量拼装sql语句 spool命令 1 Oracle的spool命令可以用来将数据export出来到文本文件。Oracle的Import/Export命令用于备份和恢复比较有效，但对于一些临时数据量的导出，Export不好用，甚至不可用。在这里，就是spool的发挥之地了。 2Spool一般使用格式为spool [filepath]filename;       其他sql语句；spool off; 要输出的内容都在spool语句中包含； 3 spool还有些其他控制命令： set pagesize 0               --设置页面大小， 0表示无限制，如果设置为10,则10行数据后出现一空行 set num 18                  --设置数字的长度，如果不够大，则用科学记数法显示 set heading off             --设置不要题头，则不出现select的field list set feedback off           --设置不需要返回信息， 比如" 100 rows selected“ set term off                 -- set trimspool on           --trim 4 如果在sqlplus中直接使用sql语句，则导出的数据文件中会包含spool语句中使用的sql语句，如果不想让生成这些可把要使用的sql语句存在一个文件中再在sqlplus中执行此文件即可。 使用select语句批量拼装sql语句 仿照select 'insert into table_name values('''||field_1||''','''||field_2||''');'from table_name where field_n=some_conditon;可联想批量生成其他sql语句，例如drop、update、delete语句。其中||为连接符号，三个单引号最后生成一个单引号。

SSH 处理Oracle Clob

It sure is easier to remove oracle products than to add them.... The method suggested, using Oracle Universal installer to remove the product, is the safest method, and the only reliable means if you have several different ORACLE-HOMEs on you server. However, if you want to remove all oracle software from your ...

SQL> create table tst (col varchar2(10), row_chng_dt date); Table created. SQL> insert into tst values ('Version1', sysdate); 1 row created. SQL> select * from tst ; COL ROW_CHNG ---------- -------- Version1 16:10:03 SQL> drop table tst; Table dropped. SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime ...

Can one import tables to a different tablespace?ITPUB个人空间g!] g4`6k;W)a Oracle offers no parameter to specify a different tablespace to import data into. Objects will be re-created in the tablespace they were originally exported from. One ...

Recompile the invalid objects: SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET MARKUP HTML OFF SET ESCAPE \ SPOOL RECOMPILE.SQL select 'ALTER VIEW ', object_name, 'compile \;' from user_objects where status = 'INVALID' and object_type='VIEW'; SPOOL OFF Reference: http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php

Normally, creating a snapshot comprise of these steps; 1. create databse link create database link TEST_DBLINK.US.ORACLE.COM connect to AMICOS identified by AMICOS using 'test'; 2. create snapshot Create snapshot  Test_SnapShot REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/24 as select * from A_Table@TEST_DBLINK If  we want to refresh snapshot FAST, we need to do these two steps: 1. create ...

SQL Server: SELECT TOP 10 product, descr, email FROM products ORACLE:...

// // The table you ...

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, ...