1、查看表空间的名称及大小
2、查看表空间物理文件的名称及大小
3、查看回滚段名称及大小
4、查看控制文件
5、查看日志文件
6、查看表空间的使用情况
7、查看数据库库对象
8、查看数据库的版本
9、查看数据库的创建日期和归档方式
10、查看当前所有对象
11、建一个和a表结构一样的空表
12、察看数据库的大小，和空间使用情况
13、查看现有回滚段及其状态
14、查看数据文件放置的路径
15、显示当前连接用户
16、把SQL*Plus当计算器
17、连接字符串
18、查询当前日期
19、用户间复制数据
20、视图中不能使用order by，但可用group by代替来达到排序目的
21、通过授权的方式来创建用户
22、How to find the tablespace of a table?
23、How to remove duplicate rows from a table
24、检查无效的数据库对象
25、检查不起作用的约束
26、检查无效的trigger
27、检查无效的trigger
28、密码有效期的查看、修改
29、DOS链接提示"无效选项"的处理
30、数据库密码如果忘了怎么办
31、资源文件
32、断开用户连接
33、查看数据库服务器连接参数
34、查看数据库名及其他属性
35、密码过期的处理
36、查找表中字段重复的数据
37、查询对象占用空间大小
38、查询系统是以pfile还是spfile启动
39、DBLink的创建、删除、查询
40、查看数据库版本
41、移动表或表分区
42、查看数据文件是否被存储占用
43、删除表空间的空数据文件
44、不配置网络，直接连接
45、锁定对象后的解锁
46、取得一段时间内的所有日期
47、设置Segment
48、批量更新
49、查询输出到文本
50、获得全球唯一32位字符串
51、授权用户表空间权限
52、查看段（deferred_segment_creation）值
53、闪回
54、找回删除的表
55、缩小Undo表空间
56、创建一个数据库常用代码
57、Merge语句
58、判断是哪个IP导致用户被锁
100、一些数据字典(以DBA登录)
//////////////////////////////////////////////////////////
1、查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
3、查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
4、查看控制文件
select name from v$controlfile;
5、查看日志文件
select member from v$logfile;
6、查看表空间的使用情况
--显示表空间大小、剩余大小、剩余百分比
select f.tablespace_name 表空间名,curG-file_freeG 实际使用G,curG 磁盘占用G,maxG 最大空间G,ext_freeG+file_freeG 剩余空间G
from
(select tablespace_name,round(sum(bytes/(1024*1024*1024)),2) as file_freeG
from dba_free_space group by tablespace_name) f
join
(select tablespace_name,round(sum(bytes/(1024*1024*1024)),2) as curG,
round(sum(maxbytes/(1024*1024*1024)),2) as maxG,round(sum(maxbytes-bytes)/(1024*1024*1024),2) as ext_freeG
from dba_data_files group by tablespace_name) d
on f.tablespace_name=d.tablespace_name;
--显示数据文件大小、最大大小、剩余百分比
select tablespace_name,file_name,file_id,
round(bytes/(1024*1024),0) as 当前占用M,round(maxbytes/(1024*1024),0) 最大空间M,
round((maxbytes-bytes)/(1024*1024),0) as 剩余空间M,
to_char(100*(maxbytes-bytes)/DECODE(maxbytes,0,maxbytes-bytes,maxbytes), '999.99')||'%' as 剩余百分比
from dba_data_files order by tablespace_name,file_id
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
7、查看数据库库对象
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
8、查看数据库的版本
Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle';
select * from v$version;
9、查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;
10、查看当前所有对象
SQL> select * from tab;
11、建一个和a表结构一样的空表
SQL> create table b as select * from a where 1=2;
SQL> create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;
12、察看数据库的大小，和空间使用情况
SQL> col tablespace format a20
SQL> select b.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
/
dba_free_space --表空间剩余空间状况
dba_data_files --数据文件空间占用情况
13、查看现有回滚段及其状态
SQL> col segment format a30
SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;
14、查看数据文件放置的路径
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;
15、显示当前连接用户
SQL> show user
16、把SQL*Plus当计算器
SQL> select 100*20 from dual;
17、连接字符串
SQL> select 列1||列2 from 表1;
SQL> select concat(列1,列2) from 表1;
18、查询当前日期
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
19、用户间复制数据
SQL> copy from user1 to user2 create table2 using select * from table1;
20、视图中不能使用order by，但可用group by代替来达到排序目的
SQL> create view a as select b1,b2 from b group by b1,b2;
21、通过授权的方式来创建用户
SQL> grant connect,resource to test identified by test;
SQL> conn test/test
create user SXK identified by ncsxk default tablespace ncbdcsxk;
grant connect,create session,create table,select any table to sxk;
22、How to find the tablespace of a table?
SELECT tablespace_name
FROM all_tables
WHERE table_name = 'YOURTABLENAME';
23、How to remove duplicate rows from a table
If the unique/primary keys can be identified from the table, it is easier to remove the records from the table using the following query:
DELETE FROM tablename
WHERE rowid not in (SELECT MIN(rowid)
FROM tablename
GROUP BY column1, column2, column3...);
Here column1, column2, column3 constitute the identifying key for each record.
If the keys cannot be identified for the table, you may create a temporary table using the query
CREATE TABLE temptablename
AS SELECT DISTINCT *
FROM tablename;
Then drop the original table and rename the temp table to original tablename.
24、检查无效的数据库对象
col owner for a20
col object_name for a30
SELECT owner, object_name, object_type
FROM dba_objects
WHERE status= 'INVALID';
25、检查不起作用的约束
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status = 'DISABLED' AND constraint_type = 'P' ;
27、检查无效的trigger
SELECT owner, trigger_name, table_name, status
FROM dba_triggers
WHERE status = 'DISABLED';
28、密码有效期的查看、修改
--查看概要文件名
select username,profile from dba_users where username='user';
--检查概要文件（默认为default）的密码有效期设置
select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
--将密码有效期修改成无限制
alter profile default limit password_life_time unlimited;
--检查“RESOURCE_LIMIT”是否配置为“FLASE”
show parameter resource_limit;
alter system set resource_limit=false scope=both;
--修改有效期后，还没有被提示ORA-28002警告的用户账号不会再碰到同样的提示，而已经被提示的用户账号必须再改一次密码
alter user user_name identified by <old_password> account unlock; --不用换密码
29、DOS链接提示"无效选项"的处理
$sqlplus /nolog
sql>conn / as sysdba 或 sql>conn user_name/psw as sysdba
30、数据库密码如果忘了怎么办
开始-->运行-->cmd
输入 ：sqlplus /nolog 回车
输入 ：connect / as sysdba 回车
用户解锁 : alter user system account unlock 回车
修改密码：alter user system identified by manager
31、资源文件
--查看RESOURCE_LIMIT值
show parameter resource_limit;
--启动当前资源限制
alter system set RESOURCE_LIMIT=true;
--创建资源配置(时间单位：分钟)
create profile [profilename] limit connect_time unlimited idle_time unlimited;
--把某种配置文件赋给某个用户
alter user [user_name] profile [profilename];
--查看用户使用了哪个资源文件
select username,profile from dba_users;
--查看资源文件设置的权限
select profile,resource_name,limit from dba_profiles;
32、断开用户连接
--查看哪些用户连接了数据库
select sid,serial#,username from v$session;
--断开用户的连接
alter system kill session '118,1394'; --'118,1394' : 'sid,serial#'
33、查看数据库服务器连接参数
--专用服务器连接(dedicated server)，共享服务器连接(shared server)
--如果VALUE_COL_PLUS_SHOW_PARAM大于0为()dedicated模式
show parameter shared_server mts_servers；
34、查看数据库名及其他属性
select name from v$database;
show parameter db
查看参数文件。
35、密码过期的处理
--查询该用户所使用的profile，如果没有特别设置，一般会是default：
SELECT username,PROFILE FROM dba_users;
--查询default的profile设置是否有密码限制，发现密码生命期限为180天：
SELECT * FROM dba_profiles WHERE resource_name='PASSWORD_LIFE_TIME';
--修改default的profile密码策略为无限制：
alter profile default limit password_life_time unlimited;
--策略已更改，但如果修改profile时候已经有该提示的用户还是需要修改密码，保持原密码即可，无提示的用户将不会再提示
alter user username identified by xxx;
--密码的历史，password_reuse_time=30,password_reuse_max=10，用户可以在30天以后重用该密码，要求密码必须被改变超过10次。
create profile p1 limit password_reuse_time 1/1440 password_reuse_max 1;
alter user scott profile p1;
--5分钟后可重用该密码，但这期间必须要被改成其他密码一次
alter profile p1 limit password_reuse_time 5/1440 password_reuse_max 1;
36、查找表中字段重复的数据
--count函数不能与列同时使用，但加group by后就可以了。分组后找出组中多余一条记录的，然后作为查询条件。
select * from bdc_fwsx where 不动产单元号 in (select 不动产单元号 from (
select count(1) as a,不动产单元号 from bdc_fwsx where 状态=1 group by 不动产单元号)
where a>1) and 状态=1 order by 不动产单元号
37、查询对象占用空间大小
--查询段占用的空间
select bytes from user_segments where segment_name='tablename';
--查询段剩余空间和已用空间
analyze table tablename compute statistics;
select empty_blocks,blocks from user_table where table_name='tablename';
38、查询系统是以pfile还是spfile启动
--如果isspecified里有true，表明用spfile进行了指定配置
--如果全为false，则表明用pfile启动
select isspecified,count(*) from v$spparameter group by isspecified;
select decode(count(*),1,'spfile','pfile') from v$spparameter
where rownum=1 and isspecified='TRUE';
39、DBLink的创建、删除、查询
--跨库查询数据时可以建立数据库连接，create后不加public，则创建的dblink只有创建者可以使用
create [public] database link link_name connect to user_name identified by user_pwd using 'server_name';
drop database link link_name;
--使用时，只要在表名后加“@link_name”即可。
--应避免使用connect by, start with, prior关键字。BLOB字段不可用。
--当本地global_names为TRUE时，link_name必须与远程的global_name一致
show parameter global_name;
select * from dba_db_links;
alter system set global_names=FALSE;
--如果创建连接后还是无法查询，可以试一下把server_name换成连接字符串。
create [public] database link link_name connect to user_name identified by user_pwd using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 17.16.27.251)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GHY)
)
)';
--查看dblink详细信息，如是否在使用中
select * from v$dblink;
40、查看数据库版本
select * from v$version;
41、移动表或表分区
--移动表的语法
Alter table tablename move [Tablespace new_name Storage(initial 50M next 50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging];
--移动分区的语法
alter table tablename move (partition partname) [update global indexes];
--之后之后必须重建索引
Alter index indexname rebuild;
--如果表有Lob段，那么正常的Alter不能移动Lob段到别的表空间，而仅仅是移动了表段，
--可以采用如下的方法移动Lob段
alter table tablename move lob(lobsegname) store as (tablespace newts);
42、查看数据文件是否被存储占用
--如果找不到记录，说明数据文件没有存储任何数据
select segment_name,file_id,blocks from dba_extents where file_id=5;
43、删除表空间的空数据文件
--首先查询到数据文件的file_id(dba_data_files)，然后查看数据文件是否是空数据文件(dba_extents)
alter tablespace tbs_name drop datafile 'datafile_path_name.dbf';
44、不配置网络，直接连接
--启动cmd
sqlplus /nolog
conn user/pwd@IP:1521/DBName;
45、锁定对象后的解锁
--查出锁定的对象sid与serial#，杀死会话（两列数据用逗号连接）
select * from v$session where sid in (select session_id from v$locked_object);
--结果
SID SERIAL#
================
136 14178
--杀死会话
alter system kill session '136,14178';
46、取得一段时间内的所有日期
--两个日期之间的天数
select to_char(to_date('2017-01-01','yyyy-mm-dd')+level-1) dt,level
from dual connect by level <=
floor(to_date('2017-07-23','yyyy-mm-dd')-to_date('2017-01-01','yyyy-mm-dd'))+1;
--起始日期，n，显示n个月
select to_char(add_months(date'2017-01-01', level-1), 'yyyy-mm') months
from dual connect by level <= 12
47、设置Segment
alter system set deferred_segment_creation=false scope=both;
48、批量更新
update a set WtNo=b.No from WT_Task a join WT_Basicinformation b on a.WtId=b.WtId;
49、查询输出到文本
spool [输出文件全路径]
set echo on --显示SQL执行语句
[查询语句]
spool off
50、获得全球唯一32位字符串
--sys_guid()是RAW类型数据，rawtohex转换为16进制表示的字符串；
--hextoraw是将16进制表示的字符串转换为RAW类型
select rawtohex(sys_guid()) from dual
51、授权用户表空间权限
alter user user_name quota unlimited on tablespace_name;
alter user user_name quota *M on tablespacename;
alter user user_name quota 0 on tablespacename;
--配额数据字典
dba_ts_quotas，user_ts_quotas
52、查看段（deferred_segment_creation）值
--创建表前修改该值为FALSE，导出时就会导出空表
show parameter deferred_segment_creation;
alter system set deferred_segment_creation=false scope=both;
53、闪回
select * from tablename as of timestamp to_timestamp('2019-10-30 02:00:00','yyyy-mm-dd hh24:mi:ss');
54、找回删除的表
select * from recyclebin where original_name='tablename';
flashback table "object_name" to before drop;
55、缩小Undo表空间
--关闭自动增长
alter database datafile 'D:SoftwareOracle11goradataORCLUNDOTBS01.DBF' autoextend off;
--缩小undo tablespace
create undo tablespace undotbs2 datafile 'D:SoftwareOracle11goradataORCLUNDOTBS02.DBF' size 100M;
alter system set undo_tablespace=undotbs2;
drop tablespace undotbs1 including contents and datafiles cascade constraints;
56、创建一个数据库常用代码
--创建数据库、创建表空间、用户，用户分配权限
create tablespace space_name datafile 'D:Oracleoradataspace_namedatafile01.dbf' size 100M autoextend on next 20M maxsize 30G;
create user user_name identified by pwd default tablespace spacename temporary tablespace TEMP;
grant create session,connect, dba to user_name;
alter tablespace space_name add datafile 'D:Oracleoradataspace_namedatafile02.dbf' size 100M autoextend on next 20M maxsize 30G;
alter database datafile 'D:Oracleoradataspace_namedatafile02.dbf' resize 2048M autoextend on next 10M maxsize 30G;
--删除表空间：首先查询到数据文件的file_id(dba_data_files)，然后查看数据文件是否是空数据文件(dba_extents)
drop tablespace space_name including contents and datafiles cascade constraints;
alter tablespace space_name drop datafile 'datafile_path_name.dbf';
--分配segment
alter system set deferred_segment_creation=false scope=both;
--修改default的profile密码策略为无限制，密码生命期限默认为180天
select username,profile from dba_users;
select * from dba_profiles where resource_name='PASSWORD_LIFE_TIME';
alter profile default limit password_life_time unlimited;
--建表
create table table_name
(id number,
name varchar2(200)
constraint PK_TABLE_NAME primary key(ID)
)tablespace(space_name);
57、Merge语句
merge [into] [schema.]table [alias]
using {[schema.]table|views|query} [alias]
on {condition}
when matched then update set {clause}
when not matched then insert values {clause}
58、判断是哪个IP导致用户被锁
--用dba角色的用户登陆，查看被锁用户及时间
select username,account_status,lock_date from dba_users;
--查看哪个ip导致被锁 搜索文件listener.log，路径：.diag nslsnrDESKTOP-Q5H59EDlistener race
--根据锁定时间与log中的对比，找到登录IP
100、一些数据字典(以DBA登录)
--查看数据库全名
select * from GLOBAL_NAME;
--查看数据库归档方式
SELECT DBID, NAME, LOG_MODE FROM V$DATABASE;
ARCHIVE LOG LIST;
--查看文件路径
SELECT NAME FROM V$DATAFILE;
SELECT MEMBER FROM V$LOGFILE;
SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;
--查看日志文件当前状态
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
--查看用户系统权限
select * from user_sys_privs;
--查看游标
SELECT SID,USER_NAME,SQL_TEXT FROM V$OPEN_CURSOR;
--查看目录对象
select * from dba_directories;
--查询撤销设置
show parameter undo_
--查看表空间
select name,bigfile,flashback_on from v$tablespace;
--查看用户控件配额
user_ts_quotas
--查看NLS参数
select * from v$nls_parameters;
--与角色相关的数据字典视图
SYSTEM_PRIVILEGE_MAP 所有可用系统权限
dba_roles 列出数据库中定义的角色
dba_role_privs 列出授予数据库用户的角色
session_roles 列出当前用户启动的角色
role_sys_privs 列出授予其他角色的角色,显示通过角色授予用户的系统权限
role_tab_privs 列出授予角色的表权限
role_role_privs 列出授予角色的系统权限
dba_sys_privs 列出用户或角色系统权限(直接赋值给用户或角色的系统权限)
user_sys_privs 列出当前用户所拥有的权限
--与分区表相关
dba_tab_partitions
dba_tab_subpartitions
--查看哪些用户连接了数据库
select sid,serial#,username from v$session;
--查看表属性
user_tables 用户表属性列表
all_tables 系统表属性列表
--配额数据字典
dba_ts_quotas 用户表空间配额信息
user_ts_quotas 当前用户表空间配额信息
--查看所有数据字典及其说明
DICT 列出所有数据字典及其说明
DICT_COLUMNS 列出数据字典每个列的作用
--查看用户相关信息
user_tab_privs 列出当前用户的表级权限
user_sys_privs 列出当前用户的系统权限
user_role_privs 列出当前用户的角色
user_tables 列出当前用户下所有的表
user_users 列出当前用户属性，如缺省表空间等
dba_sys_privs 显示指定用户所具有的系统权限
dba_users 查看用户属性
tab 显示当前用户所拥有的表、视图、序列
--查看执行的SQL语句
v$sql 显示执行的SQL语句，优先使用，比v$sqlarea快，不会产生share pool latch的争用
v$sql_bind_capture sql需要预编译的很多参数绑定在v$sql_bind_capture 这个表，如v$sql出现:1，:2等
v$sqlarea 对v$sql的一个group by统计，相同的version合为一条记录