安装oracle11g:
1. 创建用户:useradd - m - d/home/oracel-g dba oracle
2. 修改环境变量:vi .bash_profile (环境变量为直接文件提供,复制即可)
3. cd /home/oraclesetup : chown oracle:dba ...1of2.zip
chown oracle:dba ...1of2.zip 切换到安装包所在目录,赋权
4. 切换到oracle用户: su - oracle
5. 解压2个压缩安装包:unzip ....zip/tar -zxvf ....tar
6. 电脑端:运行RX(自己电脑打开RX就可以,不需要登录)
服务端:export Display=10.46.164.13:0.0 export LANG=en
(10.46.164.13为运行RX所在电脑ip;执行时必须cd /home/oraclesetup/oracle11g/database下面)
./runInstaller(执行后会出现图形化安装界面,按步骤选择安装)
7. 安装完成后cd /home/oracle/oracle11g 下面; ; ./root.sh
(1)进入数据库: sqlplus system/manager@a(数据库名)
(2)开启数据库: sqlplus / as sysdba
: Shutdown immediate;
: startup
8.监听: /home/oraclesetup/oracle11g/database下面运行:netca #Listener configuration ->Add 图形化安装; 创建实例:dbca
9.客户端:安装选择最大的那个,然后在Net configration Assistant(配置和移植工具中)配置监听和服务等。
10.清除数据库用户:drop user A cascade;
11.修改表中数据可直接用update命令修改修改表或者用plsql客户端
select rowid,t.* from table t ;解锁直接修改,然后提交 ,这种方式不会锁表
-----------如何重启oracle?
1.sqlplus / as sysdba
2.Shutdown immediate;
3.startup
----------如何配置oracle服务名:
home/oracle/oracle11g/network/admin/tnsname.ora里面配置下服务名
----------oracle 如何执行脚本?(服务端)
sqlplus system/manager@a
@/home/oracle/oracle11g/a.sql;
------------oracle修改字符集
1、服务器端
SQL> conn / as sysdba
SQL> shutdown immediate;
SQL> startup mount
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> alter database open;
服务端字符集如果是ZHS16GBK,则执行:
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
服务端字符集如果是AL32UTF8,则执行:
SQL> ALTER DATABASE character set INTERNAL_USE AL32UTF8;
服务端字符集如果是UTF8,则执行:
ALTER DATABASE character set INTERNAL_USE UTF8;
重新启动
SQL> shutdown immediate;
SQL> startup
SQL> select * from v$nls_parameters;
select * from nls_database_parameters;
NLS_CHARACTERSET
NLS_LANGUAGE
----------Oracle创建表空间脚本
declare v_count1 number(3);
begin
select count(*) into v_count1 from dba_tablespaces where tablespace_name = upper('A_DATA');
if v_count1 = 0 then
execute immediate 'create tablespace A_DATA datafile ''/home/oracle/A_DATA'' size 200M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED DEFAULT STORAGE (INITIAL 64K NEXT 4M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)';
end if;
end;
/
declare v_count2 number(3);
begin
select count(*) into v_count2 from dba_tablespaces where tablespace_name = upper('A_TEMP');
if v_count2 = 0 then
execute immediate 'create temporary tablespace A_TEMP tempfile ''/home/oracle/A_TEMP'' size 200M REUSE AUTOEXTEND ON NEXT 100M';
end if;
end;
/
--------------oracel手动建数据库用户:
declare v_count3 number(3);
begin
select count(1) into v_count3 from dba_users where username=upper('a');
if v_count3 = 1 then
execute immediate 'drop user a cascade';
end if;
execute immediate 'create user a identified by db10$ZTE default tablespace A_DATA temporary tablespace A_TEMP profile default';
execute immediate 'grant connect to a with admin option';
execute immediate 'grant dba to a with admin option';
execute immediate 'grant resource to a';
execute immediate 'grant unlimited tablespace to a with admin option';
end;
/
--------------------Orale 手动imp命令导入zxin, zxinsys 等库dmp文件命令 (日志为后生成的文件,位置可指定):
imp system/manager fromuser=a touser=b file=/home/oracle/a/c.dmp log=/home/oracle/a/c.log
---------------------------------------备份数据库?
exp zxinmeasure/db10\$ZTE@zxin file=zxinmeasure.dmp log=zxinmeasure_exp.log owner=zxinmeasure;
---------------Oracle如何修改最大连接数?(有时环境起不来是因为连接数问题)
进入数据库,1. show parameter processes(查看数据库进程的连接数)
show parameter sessions(查看session会话值)
2.alter system set processes=1000 scope=spfile;(修改连接数)
alter system set sessiones=1000 scope=spfile;(修改session值)
3.重启oracle生效
4.select count(*) from v$process; 查看数据库当前进程的连接数
---------------------oracle安装常见问题解决方法?
1.安装完netca报错: jnigetoraclehome ---------------解决方法:
cd $ORACLE_HOME/inventory/Scripts/ext/lib
cp libclntsh.so.11.1 $ORACLE_HOME/lib/
2. dbca 创建实例报错解决方法:/home/oracle/oracle11g/bin检查oracle 文件的大小和权限6751
3.安装过程中关于TNS报错:去正常的oracle环境里面复制oracle lsnrctl tnslsnr 等到/home/oracle/oracle11g/bin下
4.密码即将过期问题解决方法:
select profile,resource_name,limit from dba_profiles where resource_name='PASSWORD_LIFE_TIME';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
alter user c##admin identified by 123456;
或者直接登录plsql 会自动提示改密码。