select * from all_users; ##查看所有用户
select name from v$database; ##查看当前数据库
database test; ##进入test数据库
select * from v$instance; ##查看所有的数据库实例
shutdown immediate ##关闭数据库
alter user sys identified by new_password; ##更改用户密码
select username,password from dba_users; ##查看当实例中的用户和密码
show parameter control_files; ##查看控制文件;
select member from v$logfile; ##查看日志文件
show parameter ; ## 查看数据库参数
select * from user_role_privs; ##查看当前用户的角色
select username,default_tablespace from user_users; ##查看当前用户的缺省表空间
alter user system identified by [password] ##修改用户的密码
ALTER USER "SCOTT" ACCOUNT UNLOCK ##解锁SCOTT用户
show parameter processes; ##查看最大会话数
ALTER USER "swvip" DEFAULT TABLESPACE "system"
修改用户swvip的默认表空间为system
select index_name,table_owner from dba_indexes where table_name like 'ACCT_HOST_ROUTE_BAK' ;
查表ACCT_HOST_ROUTE_BAK的索引表
查看当前库的所有数据表:
SQL> select TABLE_NAME from all_tables;
select * from all_tables;
SQL> select table_name from all_tables where table_name like ‘u’;
TABLE_NAME———————————————default_auditing_options
查看表结构:desc all_tables;
创建用户并赋予权限
###----------------------------创建用户并赋予权限------------------------------------####-
###----------------------------创建用户并赋予权限------------------------------------####-
create user itmd
identified by "itmd"
default tablespace ITMD
quota unlimited on MAS_DATA0 //在此空间无限制
temporary tablespace TEMP;
给用户赋予权限
grant connect to itmd;
grant resource,create session to itmd; 开发角色
grant create procedure to dbuser; #这些权限足够用于开发及生产环境
grant connect,resource,imp_full_database,exp_full_database,debug connect session,debug any procedure to XXX;
正常开发所用的所有权限。
给用户授权
grant dba to omc;--授予DBA权限
grant dba to omc;--授予DBA权限
revoke dba from userid; 收回DBA权限
grant unlimited tablespace to lxg;--授予不限制的表空间
grant select any table to lxg;--授予查询任何表
grant select any dictionary to lxg;--授予 查询 任何字典
grant unlimited tablespace to lxg;--授予不限制的表空间
grant select any table to lxg;--授予查询任何表
grant select any dictionary to lxg;--授予 查询 任何字典
删除用户
drop user SDTESTDB cascade;
drop user SDTESTDB cascade;
查看用户和默认表空间的关系。
select username,default_tablespace from dba_users;
查看用户使用的表空间
select DISTINCT owner ,tablespace_name from dba_extents where owner like ''jirarm'';
建表空间
###---------------------------------建表空间------------------------------------####-
================建立表空间============================
###---------------------------------建表空间------------------------------------####-
================建立表空间============================
CREATE TABLESPACE "ITMD"
LOGGING
DATAFILE '/oracle10g/data/itmd_data01.dbf' SIZE 1024M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
=====================================================
=================建立临时表空间============================
LOGGING
DATAFILE '/oracle10g/data/itmd_data01.dbf' SIZE 1024M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
=====================================================
=================建立临时表空间============================
CREATE
TEMPORARY TABLESPACE "TEMP01" TEMPFILE '/app/oracle/oradata/
sworacle/SWVIP.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1M
=====================================================
TEMPORARY TABLESPACE "TEMP01" TEMPFILE '/app/oracle/oradata/
sworacle/SWVIP.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1M
=====================================================
创建完后把刚才创建的表空间,设置成系统的默认表空间
alter database default temporary tablespace temp02
create tablespace OMC datafile '/oradata/omc.bdf ' size 20480m autoextend on ; ###autoextend on 自动扩展
###------------------------------------------------------------------------------------####-
DROP TABLESPACE SDCRM INCLUDING CONTENTS AND DATAFILES;
删除表空间UMPP
查看表空间
###----------------------------查看表空间大小------------------------------------####-
SELECT D.TABLESPACE_NAME "Name",
TO_CHAR(((((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024)) /(A.BYTES / 1024 / 1024))*100,'99,990.9') "used(%)",
TO_CHAR((DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024),'999,990.9') "Free (M)"
FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME
AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME;
###--------------------------------------------------------------------------------------####-
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ;
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ;
===============================================
查看某个用户的数据量
select sum(bytes)/1024/1024 from dba_segments where owner='DOCMANAGER';
select sum(bytes)/1024/1024 from dba_segments where owner='DOCMANAGER';
==================================================
查看表空间物理文件的名称及大小;
###--------------------表空间物理文件的名称及大小------------------------####-
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
###------------------------------------------------------------------------------------####-
查表空间下的表的数量;
###------------------------------------------------------------------------------------####-
select count(*) from dba_tables where tablespace_name ='BNMS_DATA01';
###------------------------------------------------------------------------------------####-
###------------------------------------------------------------------------------------####-
查看数据文件放置的路径
###------------------------------------------------------------------------------------####-
SQL> col file_name format a50
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
###------------------------------------------------------------------------------------####-
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
###------------------------------------------------------------------------------------####-
查看数据库库对象
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
用系统管理员,查看当前数据库有几个用户连接:
SQL> select username,sid,serial# from v$session;
select sid,serial#,username,program,machine,status from v$session;
select count(*) from v$session;
Alter system kill session 'SID,SERIAL#'
用这个命令把连接的用户给杀死。
扩表空间
###------------------------------------------------------------------------------------####-
alter tablespace ITMD add datafile '/oracle10g/data/itmd_data03.dbf' SIZE 2048m;
给表G000增加一个7500m的逻辑卷'/dev/vgbilling/rg000_lv03'
###------------------------------------------------------------------------------------####-
###------------------------------------------------------------------------------------####-
alter tablespace ITMD add datafile '/oracle10g/data/itmd_data03.dbf' SIZE 2048m;
给表G000增加一个7500m的逻辑卷'/dev/vgbilling/rg000_lv03'
###------------------------------------------------------------------------------------####-
检查被长时间锁的对象
###------------------------------------------------------------------------------------####-
###------------------------------------------------------------------------------------####-
SQL>select a.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status from v$locked_object a,dba_objects b where a.object_id=b.object_id;
###------------------------------------------------------------------------------------####-
###------------------------------------------------------------------------------------####-
select username,default_tablespace from dba_users where username ='PRMUSER';
查看用户TEST1的默认表空间
查看用户TEST1的默认表空间
/*加入一个日志组*/
alter database add logfile group 3 ('/$ORACLE_HOME/oracle/ora_log_file6.rdo' size 10M);
/*加入日志组的一个成员*/
alter database add logfile member '/$ORACLE_HOME/oracle/ora_log_file6.rdo' to group 3;
alter database add logfile group 3 ('/$ORACLE_HOME/oracle/ora_log_file6.rdo' size 10M);
/*加入日志组的一个成员*/
alter database add logfile member '/$ORACLE_HOME/oracle/ora_log_file6.rdo' to group 3;
/*删除日志组:当前日志组不能删;活动的日志组不能删;非归档的日志组不能删*/
alter database drop logfile group 3;
/*删除日志组中的某个成员,但每个组的最后一个成员不能被删除*/
alter databse drop logfile member '$ORACLE_HOME/oracle/ora_log_file6.rdo';
/*清除在线日志*/
alter database clear logfile '$ORACLE_HOME/oracle/ora_log_file6.rdo';
alter database drop logfile group 3;
/*删除日志组中的某个成员,但每个组的最后一个成员不能被删除*/
alter databse drop logfile member '$ORACLE_HOME/oracle/ora_log_file6.rdo';
/*清除在线日志*/
alter database clear logfile '$ORACLE_HOME/oracle/ora_log_file6.rdo';
alter database clear logfile group 3;
/*清除非归档日志*/
alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3;
/*数据库归档模式到非归档模式的互换,要启动到mount状态下才能改变;startup mount;然后再打开数据库.*/
alter database noarchivelog/archivelog;
achive log start;---启动自动归档
alter system archive all;--手工归档所有日志文件
select * from v$archived_log;
show parameter log_archive;
alter database noarchivelog/archivelog;
achive log start;---启动自动归档
alter system archive all;--手工归档所有日志文件
select * from v$archived_log;
show parameter log_archive;
/*回滚段的统计信息*/
select end_time,begin_time,undoblks from v$undostat;
/*undo表空间的大小计算公式: UndoSpace=[UR * (UPS * DBS)] + (DBS * 24)
UR :UNDO_RETENTION 保留的时间(秒)
UPS :每秒的回滚数据块
DBS:系统EXTENT和FILE SIZE(也就是db_block_size)*/
select * from dba_rollback_segs/v$rollname/v$rollstat/v$undostat/v$session/v$transaction;
show parameter transactions;
show parameter rollback;
select end_time,begin_time,undoblks from v$undostat;
/*undo表空间的大小计算公式: UndoSpace=[UR * (UPS * DBS)] + (DBS * 24)
UR :UNDO_RETENTION 保留的时间(秒)
UPS :每秒的回滚数据块
DBS:系统EXTENT和FILE SIZE(也就是db_block_size)*/
select * from dba_rollback_segs/v$rollname/v$rollstat/v$undostat/v$session/v$transaction;
show parameter transactions;
show parameter rollback;