Category Archives: Oracle

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不是关键的操作,不会导致什么过分严重的问题,往死里整就行。

由于权限问题无法生成AWR报告

生成AWR报告的时候遇到如下错误:

~~~~~~~~~~~~~~~~~~~~~~~
 The default report file name is awrrpt_1_7796_7797.html. To use this name,
 press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_7796_7797.html
 select output from table(dbms_workload_repository.awr_report_html( :dbid,
 *
 ERROR at line 1:
 ORA-00904: : invalid identifier
Report written to awrrpt_1_7796_7797.html

这是因为用户没有生成AWR报告的权限。如果希望一个用户能生成AWR报告,最少需要几个权限呢?
我认为应是三个:
1.connect/create session
2.select any dictionary(查询字表)
3.execute on any dbms_workload_repository(负载信息库)

通过以下实验证明:

Last login: Tue Dec 13 16:00:29 2011 from 58.242.2.52
 [[email protected] ~]$ sql
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 14 07:51:45 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop user test1 cascade;
User dropped.
SQL> create user awr identified by awr;
User created.
SQL> grant connect to awr;
Grant succeeded.
SQL> grant select any dictionary to awr;
Grant succeeded.
SQL> grant execute on DBMS_WORKLOAD_REPOSITORY to awr;
Grant succeeded.
SQL> conn awr/awr
 Connected.
 SQL> @?/rdbms/admin/awrrpt
Current Instance
 ~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
 ----------- ------------ -------- ------------
 1288916317 ORCL 1 orcl
Specify the Report Type
 ~~~~~~~~~~~~~~~~~~~~~~~
 Would you like an HTML report, or a plain text report?
 Enter 'html' for an HTML report, or 'text' for plain text
 Defaults to 'html'
 Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
 ------------ -------- ------------ ------------ ------------
 * 1288916317 1 ORCL orcl hpserver
Using 1288916317 for database Id
 Using 1 for instance number
Specify the number of days of snapshots to choose from
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Entering the number of days (n) will result in the most recent
 (n) days of snapshots being listed. Pressing without
 specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
 Instance DB Name Snap Id Snap Started Level
 ------------ ------------ --------- ------------------ -----
 orcl ORCL 2136 14 Dec 2011 00:00 1
 2137 14 Dec 2011 01:00 1
 2138 14 Dec 2011 02:00 1
 2139 14 Dec 2011 03:00 1
 2140 14 Dec 2011 04:00 1
 2141 14 Dec 2011 05:00 1
 2142 14 Dec 2011 06:00 1
 2143 14 Dec 2011 07:00 1
Specify the Begin and End Snapshot Ids
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Enter value for begin_snap: 2142
 Begin Snapshot Id specified: 2142
Enter value for end_snap: 2143
 End Snapshot Id specified: 2143
Specify the Report Name
 ~~~~~~~~~~~~~~~~~~~~~~~
 The default report file name is awrrpt_1_2142_2143.html. To use this name,
 press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_2142_2143.html
 …省略…
End of Report
Report written to awrrpt_1_2142_2143.html

网上有人说连查询字典表的权限都不需要,我们测试下:

SQL> revoke select any dictionary from awr;
Revoke succeeded.
SQL> conn awr/awr
 Connected.
 SQL> @?/rdbms/admin/awrrpt
Current Instance
 ~~~~~~~~~~~~~~~~
 v$instance i
 *
 ERROR at line 6:
 ORA-00942: table or view does not exist
Specify the Report Type
 ~~~~~~~~~~~~~~~~~~~~~~~
 Would you like an HTML report, or a plain text report?
 Enter 'html' for an HTML report, or 'text' for plain text
 Defaults to 'html'
 Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 from dba_hist_database_instance wr, v$database cd, v$instance ci
 *
 ERROR at line 14:
 ORA-00942: table or view does not exist

报错,提示没有此表或view。在什么情况下会提示呢?
1.真的没有这个对象(我们知道,这个可以有)
2.用户没权限查看此对象。

综上所述,如果一个用户需要生成awr,需要至少对他进行如下赋权:
grant connect to [user];
grant select any dictionary to [user];
grant execute on DBMS_WORKLOAD_REPOSITORY to [user];

参考资料:
http://cyzhang1983.itpub.net/post/40572/499634
http://www.itpub.net/thread-1330158-1-1.html

ORA-600 [kqlnrc_1]错误分析

群里朋友提出一个问题:数据库遇到ORA-00600: internal error code, arguments: [kqlnrc_1], [0x7000000DCA26B38], [], [], [], [], [], []错误,请求解决。
MOS上关于ORA-600 [kqlnrc_1]错误有详细的文档说明:How To Find The Object That Causing ORA-600 [kqlnrc_1] [ID 1190673.1]

根据文档说明,一步一步寻找问题的根源:
1.Trace文件中找到ORA-600报错,顺便看了下,是AIX上的10.2.0.4.0的库

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 ORACLE_HOME = /oracle/app/product/10.2.0/db_1
 System name: AIX
 Node name: tobadb88
 Release: 3
 Version: 5
 Machine: 00CCA0B44C00
 Instance name: han_tocco
 Redo thread mounted by this instance: 1
 Oracle process number: 85
 Unix process pid: 1737624, image: [email protected]
*** 2011-12-12 16:02:56.457
 ksedmp: internal or fatal error
 ORA-00600: internal error code, arguments: [kqlnrc_1], [0x7000000DCA26B38], [], [], [], [], [], []
 Current SQL statement for this session:

2.查询0x7000000DCA26B38
直接搜索没有任何结果,经分析,在trace文件内的handle对应的值字母为小写,故搜索不到,重新搜索即可找到INVALID对象:

SO: 7000005d0479c98, type: 53, owner: 7000005e51bf988, flag: INIT/-/-/0x00
 LIBRARY OBJECT LOCK: lock=7000005d0479c98 handle=7000000dca26b38 mode=S
 call pin=7000005d6d0e638 session pin=0 hpc=0000 hlc=0000
 htl=7000005d0479d18[7000005d9176060,7000005d9176060] htb=7000005d9176060 ssga=7000005d9175aa8
 user=70000060b786c98 session=70000060b786c98 count=1 flags=PNC/[0400] savepoint=0x1cb8
 LIBRARY OBJECT HANDLE: handle=7000000dca26b38 mtx=7000000dca26c68(0) cdp=0
 [email protected]_DBGIS
 hash=01539fc30d3e6ae740e53e09dddbce4a timestamp=04-25-2009 17:35:49
 namespace=TABL flags=REM/KGHP/TIM/SML/[02020000]
 kkkk-dddd-llll=0000-0001-0001 lock=S pin=S latch#=17 hpc=0002 hlc=0002
 lwt=7000000dca26be0[7000000dca26be0,7000000dca26be0] ltm=7000000dca26bf0[7000000dca26bf0,7000000dca26bf0]
 pwt=7000000dca26ba8[7000000dca26ba8,7000000dca26ba8] ptm=7000000dca26bb8[7000000dca26bb8,7000000dca26bb8]
 ref=7000000dca26c10[7000000dca26c10,7000000dca26c10] lnd=7000000dca26c28[7000000dca26c28,7000000dca26c28]
 LIBRARY OBJECT: object=7000000d69413b0
 type=SYNM flags=EXS/LOC[0005] pflags=[0000] status=INVL load=0

Library cache中的失效对象为CRM_INTF.NW_SYN_INTERFACE
由于不是自己的库,后面的compile过程无法实现,不过已经找到了失效对象,剩下的工作已经不再复杂。

总结:
大小写,空格,减号,下划线,每一点,一个细节,都要务必严谨。
BTW,我是通过status=INVL找到的,有时,换一种角度思考,也会有不错的效果。

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