Author Archives: M. Parfait

Oracle查看所有表及各表行数


如何查看表的行数呢?最简单的方式就是用下面的命令:

select count(*) from tablename;

但是我们如果想看一下用户的所有表的行数呢?user_tables里有一个num_rows字段,可以查询此字段:

SQL> select table_name , NUM_ROWS from user_tables;
TABLE_NAME NUM_ROWS
 ------------------------------ ----------
 T051598
 T051599
 T051600
 T051601
 T051602
 T051603
 T051604
 T051605
 T051607
 T051608
 T051611
 T051614
 T257610
 T399510
 T399512
 T399514
 T399516
 T399520
 T399638
 T413451
 T413834
 T414483
 T414714
 T422788
 T422898
 T423588
 T423694
 T429877

为什么row_nums字段会是空的呢?
这部分表是刚刚imp至数据库中的,统计信息还没有收集,所以我们要先收集一下统计信息,使用如下语句:

analyze table xxx compute statistics;

再去查询,即可看到所有用户表的行数:

SQL> select table_name , NUM_ROWS from user_tables;
TABLE_NAME NUM_ROWS
 ------------------------------ ----------
 T051598 24930
 T051599 100999
 T051600 1461
 T051601 32380
 T051602 588
 T051603 9167
 T051604 583
 T051605 94
 T051607 92146
 T051608 205659
 T051611 222
 T051614 4530
 T257610 14550
 T399510 42543
 T399512 41468
 T399514 6219
 T399516 170623
 T399520 58
 T399638 55920
 T413451 1931
 T413834 461
 T414483 3
 T414714 38
 T422788 20266
 T422898 40421
 T423588 4652
 T423694 2
 T429877 121

SQLPLUS简易连接

转载,原作者描述的已经非常清楚,故不再重复工作。发布至此,做记录,同致敬(写的非常全面)。
使用SQLPLUS连接远程数据库系统的时候,可以通过以下两种方式配置连接。

方式一:简易连接,不用进行网络配置,其实就是tnsname.ora文件,但只支持oracle10G以上。
命令:sqlplus 用户名/密码@ip地址[:端口]/service_name [as sysdba]
示例:sqlplus marshall/[email protected]:1521/orcl as sysdba
备注:使用默认1521端口时可省略输入

方式二:进行网络配置 oracle9i和以前的版本
图形化操作:Net Configuration Assistant–> 本地Net服务名配置–>添加->服务名->协议(选tcp)->主机名称->端口->完成。
文本化操作:编辑$ORACLE_HOME/NETWORK/ADMIN/tnsnames.ora文件
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip或主机名称)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = 数据库的服务名称)
)
)
备注说明:红色为修改部分
PROTOCOL:客户端与服务器端通讯的协议,一般为TCP,该内容一般不用改。
HOST:数据库侦听所在的机器的机器名或IP地址,数据库侦听一般与数据库在同一个机器上,所以当我说数据库侦听所在的机器一般也是指数据库所在的机器。在UNIX或WINDOWS下,可以通过在数据库侦听所在的机器的命令提示符下使hostname命令得到机器名,或通过ipconfig(for WINDOWS) or ifconfig(for UNIX)命令得到IP地址。需要注意的是,不管用机器名或IP地址,在客户端一定要用ping命令ping通数据库侦听所在的机器的机器名,否则需要在hosts文件中加入数据库侦听所在的机器的机器名的解析。
PORT:数据库侦听正在侦听的端口.可以察看服务器端的listener.ora文件或在数据库侦听所在的机器的命令提示符下通过lnsrctl status [listener name]命令察看。此处Port的值一定要与数据库侦听正在侦听的端口一样。
SERVICE_NAME:在服务器端,用system用户登陆后,sqlplus> show parameter service_name命令察看。
======================================================================
如何保证客户端机器连接到oracle数据库呢?
A. 客户端
1.在客户端机器上安装ORACLE的Oracle Net通讯软件,它包含在oracle的客户端软件中。
2.正确配置了sqlnet.ora文件
3.正确配置了tnsname.ora文件
B. 服务器端
1.保证listener已经启动 lsntctl start
2.保证数据库已经启动。 sql>startup

原文链接:http://www.cnblogs.com/zhou__zhou/archive/2010/03/19/sqlplus.html

Linux中导入AWR报告时文件名后缀问题

导入AWR报告的过程中报错:

SQL> @?/rdbms/admin/awrload

 ~~~~~~~~~~
 AWR LOAD
 ~~~~~~~~~~
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 ~ This script will load the AWR data from a dump file. The ~
 ~ script will prompt users for the following information: ~
 ~ (1) name of directory object ~
 ~ (2) name of dump file ~
 ~ (3) staging schema name to load AWR data into ~
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
 ~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
 ------------------------------ -------------------------------------------------
 ORA11G_DIR /home/ora11g
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: ORA11G_DIR
Using the dump directory: ORA11G_DIR
Specify the Name of the Dump File to Load
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: AWRDAT_5318_5498
Loading from the file name: AWRDAT_5318_5498.dmp
Staging Schema to Load AWR Snapshot Data
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 The next step is to create the staging schema
 where the AWR snapshot data will be loaded.
 After loading the data into the staging schema,
 the data will be transferred into the AWR tables
 in the SYS schema.
The default staging schema name is AWR_STAGE.
 To use this name, press to continue, otherwise enter
 an alternative.
Enter value for schema_name:
Using the staging schema name: AWR_STAGE
Choose the Default tablespace for the AWR_STAGE user
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Choose the AWR_STAGE users's default tablespace. This is the
 tablespace in which the AWR data will be staged.
TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
 ------------------------------ --------- ------------------
 S PERMANENT
 SYSAUX PERMANENT *
 USERS PERMANENT
Pressing will result in the recommended default
 tablespace (identified by *) being used.
Enter value for default_tablespace:
Using tablespace SYSAUX as the default tablespace for the AWR_STAGE
Choose the Temporary tablespace for the AWR_STAGE user
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Choose the AWR_STAGE user's temporary tablespace.
TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
 ------------------------------ --------- -----------------------
 TEMP TEMPORARY *
Pressing will result in the database's default temporary
 tablespace (identified by *) being used.
Enter value for temporary_tablespace:
Using tablespace TEMP as the temporary tablespace for AWR_STAGE
... Creating AWR_STAGE user
|
 | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 | Loading the AWR data from the following
 | directory/file:
 | /home/ora11g
 | AWRDAT_5318_5498.dmp
 | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 |
 | *** AWR Load Started ...
 |
 | This operation will take a few moments. The
 | progress of the AWR load operation can be
 | monitored in the following directory/file:
 | /home/ora11g
 | AWRDAT_5318_5498.log
 |
 DBMS_DATAPUMP.ADD_FILE(dump file)
 ORA-39001: invalid argument value
 Exception encountered in AWR_LOAD
 begin
 *
 ERROR at line 1:
 ORA-20115: datapump import encountered error:
 ORA-39001: invalid argument value
 ORA-39000: bad dump file specification
 ORA-31640: unable to open dump file "/home/ora11g/AWRDAT_5318_5498.dmp" for read
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
 ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1717
 ORA-06512: at line 3
begin
 *
 ERROR at line 1:
 ORA-20106: AWR tables do not exist for the 'AWR_STAGE' user
 ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 2920
 ORA-00942: table or view does not exist
 ORA-06512: at line 3
... Dropping AWR_STAGE user
End of AWR Load

仔细查看报错,发现找不到/home/ora11g/AWRDAT_5318_5498.dmp这个文件。

查看/home/ora11g下的文件,发现有这个文件,不过仔细查看,其后缀为DMP!

[[email protected] ora11g]# ls -lt *.dmp *.DMP
 -rw-r--r-- 1 ora11g oinstall 56430592 Feb 15 16:41 awrdat_929_976.dmp
 -rw-r--r-- 1 ora11g oinstall 77807616 Feb 15 10:41 AWRDAT_5318_5498.DMP

我们将其修改为dmp再次尝试导入

 

[[email protected] ora11g]# mv AWRDAT_5318_5498.DMP awrdat_5318_5498.dmp
SQL> @?/rdbms/admin/awrload
 ~~~~~~~~~~
 AWR LOAD
 ~~~~~~~~~~
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 ~ This script will load the AWR data from a dump file. The ~
 ~ script will prompt users for the following information: ~
 ~ (1) name of directory object ~
 ~ (2) name of dump file ~
 ~ (3) staging schema name to load AWR data into ~
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
 ~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
 ------------------------------ -------------------------------------------------
 ORA11G_DIR /home/ora11g
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: ORA11G_DIR
Using the dump directory: ORA11G_DIR
Specify the Name of the Dump File to Load
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: awrdat_5318_5498
Loading from the file name: awrdat_5318_5498.dmp
Staging Schema to Load AWR Snapshot Data
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 The next step is to create the staging schema
 where the AWR snapshot data will be loaded.
 After loading the data into the staging schema,
 the data will be transferred into the AWR tables
 in the SYS schema.
The default staging schema name is AWR_STAGE.
 To use this name, press to continue, otherwise enter
 an alternative.
Enter value for schema_name:
Using the staging schema name: AWR_STAGE
Choose the Default tablespace for the AWR_STAGE user
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Choose the AWR_STAGE users's default tablespace. This is the
 tablespace in which the AWR data will be staged.
TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
 ------------------------------ --------- ------------------
 ALAND PERMANENT
 MARSHALL PERMANENT
 MARVEN PERMANENT
 S PERMANENT
 SYSAUX PERMANENT *
 USERS PERMANENT
 XXX PERMANENT
Pressing will result in the recommended default
 tablespace (identified by *) being used.
Enter value for default_tablespace:
Using tablespace SYSAUX as the default tablespace for the AWR_STAGE
Choose the Temporary tablespace for the AWR_STAGE user
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Choose the AWR_STAGE user's temporary tablespace.
TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
 ------------------------------ --------- -----------------------
 TEMP TEMPORARY *
Pressing will result in the database's default temporary
 tablespace (identified by *) being used.
Enter value for temporary_tablespace:
Using tablespace TEMP as the temporary tablespace for AWR_STAGE
... Creating AWR_STAGE user
|
 | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 | Loading the AWR data from the following
 | directory/file:
 | /home/ora11g
 | awrdat_5318_5498.dmp
 | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 |
 | *** AWR Load Started ...
 |
 | This operation will take a few moments. The
 | progress of the AWR load operation can be
 | monitored in the following directory/file:
 | /home/ora11g
 | awrdat_5318_5498.log
 |
... Dropping AWR_STAGE user
End of AWR Load


导入成功!

注意:在导入dmp文件时,一定要注意以下几点:
1.Directory Name输入一定为大写。
2.注意此Directory Name的权限及dmp文件权限。
3.在linux中,dmp文件的后缀名一定要为小写的dmp,如果是大写的DMP,会导致无法导入。
4.细心就好,load不是关键的操作,不会导致什么过分严重的问题,往死里整就行。

批量制作、发送邀请函

需要批量发送邀请函,要求邀请函中客户名称需要根据报名时客户姓名填写,最好邀请函生成后可根据邮箱地址直接发送。
这是一个重复、机械性的工作,对生活及生命皆无任何意义,故偷懒寻求一种简单的方式,Office 2010可以实现此功能。
在Office帮助文档为客户批量发送邀请函中,可参考第三四页的内容。
也可以参考youku中的视频批量制作客户邀请函

注意,在微软的帮助文档中,是从Outlook中选择的联系人,而我更建议第二个选项《使用现有列表》,这样就可以直接使用Excel中的客户信息了。
ZA102673516

设置Outlook客户端收发Gmail邮件

此篇记录对Outlook客户端的设置,以使其收发Gmail邮件。
配置方法详见:【Gmail IMAP】在公司、家裡用Outlook同步收發Gmail郵件!
此文语言简单明了,虽为繁体软体说明,但是稍微留意,与简体差异不大。

Page 1 of 2412345...1020...Last »
普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress