Archive for the ‘数据库技术’ Category
Smart Questionaor ?
星期五, 05月 30th, 2008ORA-12514
星期四, 05月 22nd, 2008今天碰到一个错，很典型。
1. 错误 ORA-12514
C:\Documents and Settings\Administrator>sqlplus icmadmin/fvt6eip@rmdb
SQL*Plus: Release 10.2.0.3.0 – Production on Fri May 23 01:53:46 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name:
[Good Article]Initialization Parameter files: PFILEs vs. SPFILEs
星期一, 05月 5th, 2008sqlplus icmadmin/password@dbname
星期日, 05月 4th, 2008这种方式使用local naming service 连接数据库。也就是读取tnsnames.ora来连接。
ICMNLSDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = icmnlsdb.cn.ibm.com)
)
)
这个时候，
sqlplus icmadmin/fvt6eip@ICMNLSDB
sqlplus icmadmin/fvt6eip@icmnlsdb
都能连上数据库，也就是说 dbname 本身对大小写不敏感，并且这个值可以随便改。
SERVICE_NAME = icmnlsdb.cn.ibm.com 与 listener.org 里面 的GLOBAL_DBNAME必须一致。
(SID_DESC =
(GLOBAL_DBNAME = icmnlsdb.cn.ibm.com)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = icmnlsdb)
)
使用ezconnect佐证以上内容，
1.sqlplus icmadmin/fvt6eip@//localhost:1521/icmnlsdb.cn.ibm.com 连接成功。
2.sqlplus icmadmin/fvt6eip@//localhost:1521/icmnlsdb 连接失败。
oracle@linux:/u01/app/oracle/product/10.2.0/db_1/network/admin> sqlplus icmadmin/fvt6eip@//localhost:1521/icmnlsdb
SQL*Plus: Release 10.2.0.3.0 – Production on Sun May 4 09:30:33 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
ORA-12560: TNS:protocol adapter error
星期日, 05月 4th, 2008oracle@linux:~> sqlplus icmadmin/fvt6eip@icmnlsdb
SQL*Plus: Release 10.2.0.3.0 – Production on Sun May 4 09:07:05 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
mentalink解释是：
- ORA-12560: TNS:protocol adapter error
- Cause: A generic protocol adapter error occurred.
Action: Check addresses used for proper protocol specification. Before reporting this error, look at the error stack and check for lower level transport errors. For further details, turn on tracing and reexecute the operation. Turn off tracing when the operation is complete. - 于是我ping
- oracle@linux:~> hostname
linux
oracle@linux:~> ping linux.cn.ibm.com
PING linux.cn.ibm.com (192.168.150.10) 56(84) bytes of data.
From 192.168.150.129: icmp_seq=2 Destination Host Unreachable
From 192.168.150.129 icmp_seq=2 Destination Host Unreachable
From 192.168.150.129 icmp_seq=3 Destination Host Unreachable
From 192.168.150.129 icmp_seq=4 Destination Host Unreachable— linux.cn.ibm.com ping statistics —
4 packets transmitted, 0 received, +4 errors, 100% packet loss, time 2999ms - 果然不通。
- oracle@linux:~> tnsping icmnlsdb
TNS Ping Utility for Linux: Version 10.2.0.3.0 – Production on 04-MAY-2008 09:10:48
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linux.cn.ibm.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = icmnlsdb)))
TNS-12560: TNS:protocol adapter error - 同样的错。
IBM Content Manager Oracle Table Space Usage Information
星期日, 05月 4th, 2008ＣＭ Oracle table space parameters
| Field name | Description | Default name / option | Record your value here |
|---|---|---|---|
| Frequently queried large objects | Table space holds tables for which updates, inserts, and deletes are very common. | ICMVFQ04 | |
| Moderately queried large objects | Table space holds large, persistant, frequently used tables, such as the item type tables. | ICMLFQ32 | |
| Rarely queried large objects | Table space holds large but infrequently used tables, such as event logs. | ICMLNF32 | |
| Frequently queried small objects | Table space holds small, frequently used but seldom updated tables. | ICMSFQ04 | |
| Table indexes | Table space holds library server indexes. | ICMLSNDX |
看看Linux上面这几个tablespace 对应的存储文件，
TABLESPACE FILE_NAME
———- ————————————————————
SYSTEM /u01/app/oracle/oradata/icmnlsdb/icmnlsdb_SYSTEM_01.dbf
UNDOTBS1 /u01/app/oracle/oradata/icmnlsdb/icmnlsdb_UNDOTBS1_01.dbf
SYSAUX /u01/app/oracle/oradata/icmnlsdb/icmnlsdb_SYSAUX_01.dbf
ICMLFQ32 /u01/app/oracle/oradata/icmnlsdb/icmnlsdb_ICMLFQ32_01.dbf
ICMLNF32 /u01/app/oracle/oradata/icmnlsdb/icmnlsdb_ICMLNF32_01.dbf
ICMLSNDX /u01/app/oracle/oradata/icmnlsdb/icmnlsdb_ICMLSNDX_01.dbf -LS index table
ICMLSTXT /u01/app/oracle/oradata/icmnlsdb/icmnlsdb_ICMLSTXT_01.dbf – Oracle text search table
ICMSFQ04 /u01/app/oracle/oradata/icmnlsdb/icmnlsdb_ICMSFQ04_01.dbf
ICMVFQ04 /u01/app/oracle/oradata/icmnlsdb/icmnlsdb_ICMVFQ04_01.dbf
连接Oracle数据库的五种常见方式浅析
星期六, 05月 3rd, 2008- connect user/pass[@db]
使用data directory 验证，数据库必须开启。
- connect user/pass[@db] as sysdba
- connect user/pass[@db] as sysoper
使用外部密码文件验证。
- connect / as sysdba
- connect / as sysoper
使用OS用户验证，即检查运行SqlPlus的用户是否存在于操作系统中拥有Oracle的系统用户组中。
后四种方式的连接，可以关闭，开启数据库(shutdown, startup) 。这四种连接方式，无论数据库处于何种状态，甚至根本都没创建，都可以连接。说白了，是instance上面的监听程序连上了，只在数据库内存区打转，还没跟实际的数据库有啥关系呢。
使用 Transaction
星期六, 05月 3rd, 2008A transaction is started by any valid DML statement, including INSERT,
UPDATE, and DELETE. A SELECT statement will execute a query against the
database but not start a transaction. ROLLBACK and COMMIT will end a
transaction but start another one. SAVEPOINT defines a named marker in
your transaction that allows data before it to be committed and following it
to be rolled back when the ROLLBACK TO savepoint statement is issued.