linux登录数据库
su - oracle
sqlplus /nolog
conn /as sysdba
lsnrctl status
lsnrctl shop
lsnrctl start
shutdown immediate
su - oracle
sqlplus /nolog
conn /as sysdba
shutdown immediate
ora-03113 通信通道的文件结尾
途径空间小,一个方法是将空间设置大点,另一个方法是将多余的文件删除掉
将空间设置大:
sqlplus /nolog
sqlplus / as sysdba
conn /as sysdba
shutdown abort--关闭进程
startup mount --装载数据库
select * from v$recovery_file_dest --查询归档日志
db_recovery_file_dest_size=10737418240--设置归档日志空间为10G
exit
删除归档日志
rmantarget / --进入rman工具窗口
crosscheckarchivelog all;--这个命令可以将所有无效的expired的archivelog标记出来
deletenoprompt archivelog until time "sysdate -3";--删除3天前的归档日志
ora -28000 数据库被锁
sqlplus / as sysdba
desc dba_profiles;
select resource_name,limit from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS';
alter user query account unlock;
数据库锁表
select * from v$version t1,v$locker_object t2 where t1.sid = t2.session_id;
alter system kill session '';
数据库过程被锁
select t.session_id from dba_ddl_locks t where name='PCX_YDCRM_JGQJ)DTZBPZYYB';
select sid,serial# from v$version where sid= 'session_id';
alter system kill session 'sid,serial#';
如何查看数据库有哪些用户
select * from dba_users;
删除用户:
drop user xxx;--该用户没有任何对象
drop user xxx cascade;--该用户有对象,删除所有对象和用户
select * from dba_users;
select * from dba_data_files;
创建用户:
create user xxx identified by 123321;
授权:
grant create session to crm_xc;
grant "connect" to crm_xc with admin option;
grant user crm_xc quota 5M on system;
grant create table to crm_xc with admin option;
grant delete any table to crm_xc with admin option;
grant insert any table to crm_xc with admin option;
grant select any table to crm_xc with admin option;
grant update any table to crm_xd witn admin option;
grant create any table to crm_xc;
grant read,write on directory dump_dir to crm_xc;
DQL 数据查询语言 select
DML 数据操作语言 insert update delete
DDL 数据定义语言 create drop alter truncate
DCL 数据控制语言 grant revoke
TPL 事务控制语言 commit rollback
一、 数据库
1、Oracle是怎样分页的
Oracle用rownum进行分页
分页语句的步骤:
a.最内层sql,查询要分页的所有数据
b.第二层sql,通过rownum伪列确定显示数据的上限,并且给查询的数据添加rownum伪列的值
c.最外层sql,设置显示数据的下限
select*from
(select a.