- 启动库
startup
- 启动监听
lsnrctl start
- 连接数据库
sql> sqlplus /nolog
sql> conn /as sysdba
或者
sql> sqlplus / as sysdba
- 查看用户拥有的数据库对象
select object_name from user_objects;
- 查看约束信息
select constraint_name from user_constraints;
- 查看用户拥有的表
select table_name from user_tables;select * from tab;
- 查看用户拥有的视图
select view_name from user_views;
- 查看用户拥有的触发器
select trigger_name from user_triggers;
- 查看用户拥有的序列
select sequence_name from user_sequences;
- 查看用户拥有的存储过程
select object_name from user_procedures;
- 查看用户拥有的索引
select index_name from user_indexes;
- 显示当前用户
show user;
- 切换用户
conn system/bjpowernode;
- 查看所有的用户
conn system/bjpowernodeselect username from dba_users;
- 查看用户拥有的权限
select * from session_privs;
- 常用权限
CREATE SESSION 连接数据库
CREATE TABLE 创建表
CREATE VIEW 创建视图
CREATE SEQUENCE 创建序列
CREATE PROCEDURE 创建存储过程
CREATE TRIGGER 创建触发器
CREATE INDEXTYPE 创建索引
UNLIMITED TABLESPACE 对表空间的使用
- 给用户加锁
alter user scott account lock;
- 给用户解锁
```
alter user scott account unlock;
```
- 修改用户密码
```
alter user scott identified by test123;
```
- 新建用户
```
create user bbs identified by bbs123;
```
- 删除用户及相关对象
```
drop user test1 cascade;
```
- 给用户授权(多个采用逗号间隔)
```
grant create session, create table to bbs;
```
- 分配表空间usres给用户
```
create table t_test(id number(10), name varchar2(30))
```
> 以上出现无法创建表,主要原因在于没有分配表空间,也就是我们新建的表不知道放到什么地方。
```
alter user bbs default tablespace users;
```
- 授权表空间给用户
```
grant UNLIMITED TABLESPACE to bbs;
```
- 导入和导出命令imp、exp
> 首先赋予该用户dba的权限然后:
> 如:将数据库文件.DMP导入到yishiyiyi2011数据库中
```
imp yishiyiyi2011/yishiyiyi@SID full=y file=fileLoad ignore=y
```
> 导出同理.
```
exp yishiyiyi2011/yishiyiyi@SID full=y file=fileLoad
注意:yishiyiyi2011 是我要导出的数据库
```
> 另一种方式:
> 将数据库TEST完全导出,用户名system密码manager导出到D:\daochu.dmp中exp
```
system/manager@TEST file=d:\daochu.dmp full=y
```
> 将D:\daochu.dmp中的数据导入TEST数据库
```
imp system/manager@TEST file=d:\daochu.dmp
```
> 只有DBA才能执行完整数据库或表空间的导出
- 删除表空间以及相应的数据文件
```
drop tablespace xxxx including contents and datafiles;
```
- 查看各个表空间使用情况
select b.tablespace_name,
round(b.all_byte) all_byte,
round(b.all_byte - a.free_byte) use_byte,
round(a.free_byte) free_byte,
100 - round((a.free_byte / b.all_byte) * 100) percent
from (select tablespace_name,
sum(nvl(bytes, 0)) / 1024 / 1024 / 1024 free_byte
from dba_free_space
group by tablespace_name) a,
(select tablespace_name,
sum(nvl(bytes, 0)) / 1024 / 1024 / 1024 all_byte
from dba_data_files
group by tablespace_name) b
where b.tablespace_name = a.tablespace_name(+)
order by 5 desc, 1;
- 一个完整的过程,创建用户、创建表空间、授权、建表
--创建用户
create user bbs identified by bbs123;
--创建表空间
create tablespace ts_bbs datafile 'E:\bbs\bbs_data.dbf' size 100m;
--将表空间分配给用户
alter user bbs default tablespace ts_bbs;
--给用户授权
grant create session, create table, create view, create sequence, unlimited tablespace to bbs;
--以bbs登陆建立表,tt
create table tt(tt_id number(10));
- 判断是否存在表
select * from user_tables t where t.table_name = upper('diycolumn');
- 判断是否存在表字段
select *
from user_tab_columns t where t.table_name = upper('diycolumn') and t.column_name = upper('Columnid');
- 判断是否存在主键
select *
from user_constraints t
where t.table_name =upper('diycolumn')
and t.constraint_type = 'P';
- /*根据内容查询存储过程、根据字段查询表明 互向*/
SELECT *
FROM ALL_SOURCE where TYPE = 'PROCEDURE' AND TEXT LIKE '%insert into dqd_bsa_files_mid1%';
SELECT A.* FROM user_objects a where a.OBJECT_TYPE = 'TABLE';
SELECT A.* FROM all_tab_cols A where a.TABLE_NAME = 'DQD_BSA_FILES_ALL';
SELECT A.* FROM all_tables a where a.TABLE_NAME = 'DQD_BSA_FILES_ALL';
- 根据操作系统进程号找sql堆栈
SELECT /*+ ordered */ p.spid, s.sid, s.serial#,s.CLIENT_IDENTIFIER,s.username, TO_CHAR(s.logon_time, 'mm-dd-yyyy hh24:mi') logon_time, s.last_call_et, st.value, s.sql_hash_value, s.sql_address, sq.sql_text
FROM v$statname sn, v$sesstat st, v$process p, v$session s, v$sql sq
WHERE s.paddr=p.addr
AND s.sql_hash_value = sq.hash_value and s.sql_Address = sq.address
AND s.sid = st.sid
AND st.STATISTIC# = sn.statistic#
AND sn.NAME = 'CPU used by this session'
AND p.spid = &osPID -- parameter to restrict for a specific PID
AND s.status ='ACTIVE'
ORDER BY st.value desc;
- AWR报告生成
Sqlplus / as sysdba
@?/rdbms/admin/awrrpt.sql //生成awr报告