[Q]FTP怎么在脚本中自动上传/下载 [A]可以把FTP写到shell脚本中，如 ftp -n -i 主机IP <<EOF user username pass cd 目标目录 put file get file #查询文件 ls #退出 bye EOF
[Q]怎么样查看数据库字符集 [A]数据库服务器字符集select * from nls_database_parameter 应该是select * from nls_database_parameters把？
[Q]怎么样在ORACLE中定时运行存储过程 [A]可以利用dbms_job包来定时运行作业，如执行存储过程，一个简单的例子，提交一个作业： VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;'SYSDATE,'SYSDATE + 1'); commit; END; 之后，就可以用以下语句查询已经提交的作业 select * from user_jobs; should be
SQL> create user test identified by "123"; 用户已创建 SQL> grant connect to test; 授权成功。 SQL> conn test/123@fs8dev; 已连接。 SQL> conn test/"123"@fs8dev; 已连接。 SQL>
(4) rebuilding the indexes afterwards in parallel with nologging.
The redo you see generated in the non-indexed example is used to protect the data dictionary
example:
insert /*+ append */ into mytest select * from all_objects;
alter index t_idx unusable;
alter session set skip_unusable_indexes=true;
insert into mytest select * from all_objects;
conclusion: a direct path load into a non-logged table with skip index maintenance -- no redo to speak of
Suggestion: Doing hot backup the affected datafiles as soon as possible after the load! so you have something you can recover from...
select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&1')||'%' --and b.value > 0
[Q]怎么得到表中每个分区的记录数 [A]select count(*) from table partitin(partitionname);可以得到一个分区上的记录数。但是无法在一个sql中查询每个分区的记录。 我写了一个存储过程。 create or replace procedure p_partition_total (p_tablename in varchar2) as v_part_name varchar2(30); v_total number; begin dbms_output.put_line(rpad('Partition_name', 30) || lpad('Total', 10)); for c_part_name in (select partition_name from user_tab_partitions where table_name = UPPER(p_tablename)) loop execute immediate 'select ''' || c_part_name.partition_name || ''', count(*) from ' || p_tablename || ' partition (' || c_part_name.partition_name || ')' into v_part_name, v_total; dbms_output.put_line(rpad(v_part_name, 30) || lpad(v_total, 10)); end loop; end; /
调用时将表名传入即可，不用区分大小写。 注意打开serveroutput选项。 例子： SQL> create table test partition by range(id) 2 (partition p1 values less than (100), 3 partition p2 values less than (500), 4 partition p3 values less than (1000), 5 partition p4 values less than (maxvalue) 6 ) 7 as select rownum id from dba_objects 8 ;
表已创建。
SQL> create or replace procedure p_partition_total (p_tablename in varchar2) as 2 v_part_name varchar2(30); 3 v_total number; 4 begin 5 dbms_output.put_line(rpad('Partition_name', 30) || lpad('Total', 10)); 6 for c_part_name in (select partition_name from user_tab_partitions where table_name = UPPER(p_tablename)) loop 7 execute immediate 'select ''' || c_part_name.partition_name || ''', count(*) from ' || p_tablename || ' partition (' || c_part_name.partition_name || ')' into v_part_name, v_total; 8 dbms_output.put_line(rpad(v_part_name, 30) || lpad(v_total, 10)); 9 end loop; 10 end; 11 /
过程已创建。
SQL> set serveroutput on size 100000 SQL> exec p_partition_total('test'); Partition_name Total P1 99 P2 400 P3 500 P4 5208
代码:-------------------------------------------------------------------------------- set heading off prompt Welcome to Zhangleyi's SQL*PLUS! column sys_date new_value datevalue set termout off select 'The time is ' || to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') sys_date from dual; set termout on prompt &datevalue set serveroutput on set heading on
set termout off column host_name new_value hname select SYS_CONTEXT('USERENV','HOST') host_name from dual; set sqlprompt &hname> -------------------------------------------------------------------------------- 登录SQL*PLUS的欢迎信息就变成如下所示： [zhangleyi@as orcl]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jan 5 20:10:44 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production
Welcome to Zhangleyi's SQL*PLUS! The time is 2004-01-05 20:10:44 as.rhel.com>
网站地图
收藏本站
高级搜索
相关文章: