oracle数据库知识点

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$sessiona,v$sqlarea 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