Oracle Tips

1.查看数据库版本
SQL> select banner from sys.v_$version;

BANNER
----------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

2.修改oracle最大连接数
#sqlplus /nolog
SQL>conn / as sysdba

SQL> show parameter processes;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 1
processes integer 150

SQL> alter system set processes=300 scope = spfile;

System altered.

SQL> show parameter processes;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 1
processes integer 150

SQL> create pfile from spfile;

重启数据库 :

SQL> shutdown
SQL> startup

3.修改用户密码
一般登陆时可以输入：
#sqlplus "/as sysdba"


在以SYSDBA身份登陆时可以修改其他用户的密码，比如：
SQL> alter user user01 identified by user10;
用户已更改。
这个是把USER01用户密码修改为USER10

4、查看安装了哪些选项
SQL> col PARAMETER format a60
SQL> col VALUE format a10
SQL> select * from sys.v_$option;

PARAMETER VALUE
------------------------------------------------------------ ----------
Partitioning TRUE
Objects TRUE
Parallel Server FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE

PARAMETER VALUE
------------------------------------------------------------ ----------
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
N-Tier authentication/authorization TRUE
Function-based indexes TRUE
Plan Stability TRUE
Online Index Build TRUE
Coalesce Index TRUE
Managed Standby TRUE
Materialized view rewrite TRUE

PARAMETER VALUE
------------------------------------------------------------ ----------
Materialized view warehouse refresh TRUE
Database resource manager TRUE
Spatial TRUE
Visual Information Retrieval TRUE
Export transportable tablespaces TRUE
Transparent Application Failover TRUE
Fast-Start Fault Recovery TRUE
Sample Scan TRUE
Duplexed backups TRUE
Java FALSE
OLAP Window Functions TRUE

已选择33行。

5.查看isqlplus端口

cat $ORACLE_HOME/install/portlist.ini

启动iSQLPlus:isqlplusctl start

6.Oracle Password file:

Parameter REMOTE_LOGIN_PASSWORDFILE determine the behavior of db authentification

Use command 'ORAPWD' to create a password file.

Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER system privileges for a database.

7. Create SP(Server Parameter) file from pfile:

CREATE SPFILE='/u01/oracle/dbs/spfilemynewdb.ora' FROM
PFILE='/u01/oracle/admin/initmynewdb/scripts/init.ora';

8. Recover oraInventroy

$OARCLE_HOME/oui/bin/runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 ORACLE_HOME_NAME="OracleHome1"