oracle数据库知识点
create user wjbrzxt
IDENTIFIED by wjbrzxt;
grant dba to wjbrzxt;
Oracle:数据库中更改密码,防止密码锁定。
alter user wjbrzxt account unlock;
alter user wjbrzxt identified by Oracle123;
commit;
orcl创建表空间
CREATE SMALLFILE TABLESPACE “BKJ2GBZW” DATAFILE ‘F:\oracle\product\10.2.0\oradata\BKJ2GBZW’ SIZE 1000M AUTOEXTEND ON NEXT 102400K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER “NEWGBZW” PROFILE “DEFAULT” IDENTIFIED BY “111111” DEFAULT TABLESPACE “BKJ2GBZW” TEMPORARY TABLESPACE “TEMP” ACCOUNT UNLOCK;
GRANT “CONNECT” TO “NEWGBZW”;
GRANT “DBA” TO “NEWGBZW”;
1.创建oracle的文件
CREATE SMALLFILE TABLESPACE “ZHGW” DATAFILE ‘D:\oracle\product\10.2.0\db_1\oradata\ZHGW’ SIZE 1000M AUTOEXTEND ON NEXT 102400K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
2.创建oracle用户和文件的映射关系。
CREATE USER “ZHDB” PROFILE “DEFAULT” IDENTIFIED BY “root” DEFAULT TABLESPACE “ZHGW” TEMPORARY TABLESPACE “TEMP” ACCOUNT UNLOCK;
3.添加权限
GRANT “CONNECT” TO “ZHDB”;
GRANT “DBA” TO “ZHDB”;
4.删除数据库
DROP TABLESPACE monitor INCLUDING CONTENTS AND DATAFILES;
5.删除用户
drop user monitor cascade;
调整ORACLE-procress&session的参数值和占用值
ORA-12516: 监听程序找不到符合协议堆栈要求的可用处理程序
1.使用系统管理员登录执行,如果登录不上就关闭TOMCAT,释放资源后再登陆,修改完毕后立即重启数据库,重新查看允许最大连接数有没有生效
alter system set processes=1000 scope=spfile;
alter system set sessions=1105 scope=spfile;
select count(*) from v$process --当前的连接数
select value from v$parameter where name = ‘processes’ --数据库允许的最大连接数
修改最大连接数:
alter system set processes = 300 scope = spfile;
重启数据库:
shutdown immediate;
startup;
–查看当前有哪些用户正在使用数据
SELECT osuser, a.username,cpu_time/executions/1000000||‘s’, sql_fulltext,machine
from v s e s s i o n a , v session a, v sessiona,vsqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
导入导出
导入:imp ZHDB/root@orcl file=“D:\newmyeclipse\zh\db\zhonghang.dmp” full=“y”
打开cmd窗口,执行以下格式命令:
imp 用户名/密码@服务器IP:端口/服务名 file=dmp文件路径 full=y
例子,这是我的:
imp ZXXT/ZXXT@192.168.20.212:1521/orcl file=E:\运政执法数据\yz\yzdata.dmp full=y