启动、关闭命令
打开服务器
net start oracleservicebinbo
启动数据库
startup
打开监听器
lsnrctl start
关闭数据库
SHUTDOWN IMMEDIATE
关闭oracle
shutdown
关闭监听
lsnrctl stop
创建、删除数据库
dbca
创建、产出监听
netca
创建表空间
create tablespace TABLESPACE-NAME datafile '/PATH/TO/TABLESPACE/FIEL' size 2g autoextend on;
alter tablespace TABLESPACE-NAME add datafile '/PATH/TO/TABLESPACE/FIEL2' size 2g autoextend on;
创建用户
create user USERNAME identified by PASSWORD default tablespace TABLESPACE-NAME;
创建目录
create directory DIRNAME as '/PATH/TO/DIR';
授权
Grant read,write on directory DIRNAME to USERNAME;
grant dba to USERNAME;
删除用户及关联表
drop user USERNAME cascade；
删除表空间及表空间文件
drop tablespace TABLESPACE-NAME including contents and datafiles cascade constraint;
查看实例名
select instance_name from v$instance;
查看表空间
select TABLESPACE_NAME from dba_tablespaces ;
查看当前用户
select USERNAME from user_users;
查看当前用户详细信息
select *from user_users;
查看当前用户角色信息
select * from user_role_privs;
查看当前用户权限
select * from role_sys_privs;
查看所有用户及表空间
select username,default_tablespace from dba_users;
修改用户默认表空间
alter user USERNAME default tablespace TABLESPACE_NAME ;
查看用户及状态
select USERNAME,ACCOUNT_STATUS from dba_users;
查看用户表
select table_name from user_tables;
查看所有表名
select table_name from all_tables;
查看所有表（包括系统表）
select * from dba_tables
查看数据库
select * from V$database;
查看数据库文件
select * from V$dbfile;
查看当前连接数
select count(*) from v$bgprocess;
查看和修改最大连接数和最大session（需要sysdba）
查看processes
show parameter processes
查看session
show parameter sessions
修改processes
alter system set processes=300 scope=spfile;
修改sessions
alter system set sessions=335 scope=spfile;
查看连接会话消息
select sid,serial#,username,program,machine,status from v$session;
查看使用的sql连接数
SELECT b.MACHINE, b.PROGRAM, COUNT (*)
FROM v$process a, v$session b
WHERE a.ADDR = b.PADDR AND b.USERNAME IS NOT NULL
GROUP BY b.MACHINE, b.PROGRAM
ORDER BY COUNT (*) DESC;
查看连接的用户
select osuser,a.username,cpu_time/executions/1000000||'s',sql_fulltext,machine
from v$session a,v$sqlarea b
where a.sql_address = b.address
order by cpu_time/executions desc;
未完待续
