oralcle数据库常用SQL语句
表空间
SQL>select username,default_tablespace from user_users;
1.查看当前用户的角色
SQL>select * from user_role_privs;
2.查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
3.查看用户下所有的表
SQL>select * from user_tables;
用户
1.查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;
2.查看当前用户的角色
SQL>select * from user_role_privs;
3.查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
4.显示当前会话所具有的权限
SQL>select * from session_privs;
5.显示指定用户所具有的系统权限
SQL>select * from dba_sys_privs where grantee='GAME';
表
1.查看用户下所有的表
SQL>select * from user_tables;
SELECT * FROM ALL_TABLES;
2.查看名称包含log字符的表
SQL>select object_name,object_id from user_objects
where instr(object_name,'LOG')>0;
3.查看某表的创建时间
SQL>select object_name,created from user_objects where object_name=upper('&table_name');
4.查看某表的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');
查看放在ORACLE的内存区里的表
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;
索引
1.查看索引个数和类别
SQL>select index_name,index_type,table_name from user_indexes order by table_name;
2.查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper('&index_name');
3.查看索引的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&index_name');
序列号
1.查看序列号,last_number是当前值
SQL>select * from user_sequences;
视图
1.查看视图的名称
SQL>select view_name from user_views;
2.查看创建视图的select语句
SQL>set view_name,text_length from user_views;
SQL>set long 2000; 说明:可以根据视图的text_length值设定set long 的大小
SQL>select text from user_views where view_name=upper('&view_name');
同义词
1.查看同义词的名称
SQL>select * from user_synonyms;
SELECT * FROM ALL_SYSNONYMS;
约束条件
1.查看某表的约束条件
SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');
SQL>select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
存储函数和过程
1.查看函数和过程的状态
SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';
2.查看函数和过程的源代码
SQL>select text from all_source where owner=user and name=upper('&plsql_name');
-- 查询本用户的表的列名和注释
select * from user_col_comments
-- 查询本用户的视图
select * from user_tab_comments where table_type='VIEW'
-----查询用户所有的存储过程
select * from dba_source where OWNER='ZLZ' AND TYPE='PROCEDURE' ;
-----查询用户所有的包
select * from dba_source where OWNER='ZLZ' AND TYPE='PACKAGE' ;
-----查询用户所有的函数
select * from dba_source where OWNER='ZLZ' AND TYPE='FUNCTION' ;
---物化视图日志
SELECT * FROM user_MVIEW_LOGS
---物化视图
select *from USER_dependencies where type = 'MATERIALIZED VIEW'
SELECT GRANTEE, OWNER,TABLE_NAME,GRANTOR FROM USER_TAB_PRIVS ---查询表存储过程的赋权
SELECT DISTINCT GRANTEE, OWNER, TABLE_NAME, GRANTOR FROM USER_COL_PRIVS ----查询表字段授权
----oracle查看表分区----
select table_owner, table_name, partition_name, tablespace_name
from dba_tab_partitions
where table_name in ('ZLZ_TEST_FAMILY_PART_RANGE',
'ZLZ_TEST_FAMILY_PART_HASH',
'ZLZ_TEST_FAMILY_PART_LIST');
如何杀掉imp/emp进程
a.执行命令:ps -ef | grep exp | grep -v grep | awk '{print "kill -9 "$2}' >test.sh
b.执行命令ll ,test.sh 大小不为0 时,执行 /bin/sh test.sh
c.等于0就没有要杀的
查询数据源的数据泵路径:select * from dba_directories;
修改表中字段:select t.*,t.rowid from zlz_test t;
删除表:drop table 表名称
清除表数据:truncate table 表名称
删除某一列的值:delete from 表名称 where 列名称 = 值
--加表字段
alter table zlz_test add homenum varchar2(12);
---增加zlz_test_2_1表的三个时间字段
update zlz_test_2_1 set date_id=sysdate-dbms_random.value
update zlz_test_2_1 set timestamp_id=sysdate-dbms_random.value
update zlz_test_2_1 set time_id=sysdate-dbms_random.value
oracle增加主键:alter table ZLZ_TDM_35 add constraint pk_card1 primary key(st_id)
oracle增加外键:alter table zlz_test_family_35 add constraint fk_family1 foreign key(st_id) references ZLZ_TDM_35 (st_id);
显示数据库所有分区表的信息:select * from DBA_PART_TABLES
drop user zlz_test cascade; ---删除schema
create user zlztest identified by zlztest; ----创建schema
grant dba to zlztest; ----给schema赋dba权限
revoke dba from zlztest; -----去掉schema的dba权限
查看当前用户的索引和约束
select index_name, table_name,status,logging from user_indexes;
select owner, constraint_name, constraint_type,table_name,status from user_constraints;
status = valid 是启用
status为ENABLED 是启用
oracle查看用户角色: select * from user_role_privs;
oracle查看用户权限: select * from dba_sys_privs where grantee='ZLZ';
--创建索引
create index 索引名 on 表名 (字段1, 字段2, 字段3);
--禁用索引
ALTER INDEX <indexName> UNUSABLE ;
--恢复索引
ALTER INDEX <indexName> REBUILD ;
--删除索引
drop index 索引名
----查看表大小-----
Select Segment_Name,Sum(bytes)/1024/1024/1024 GB From dba_Extents where owner='CXC_SOURCE' and Segment_Name='NORMALTABLE_5G_1' Group By Segment_Name;
---查询当前用户下全部的表及表大小m-----
select SEGMENT_NAME ,sum( bytes/1024/1024/1024) from DBA_segments where owner='CXC_SOURCE' AND segment_type = 'TABLE' GROUP BY SEGMENT_NAME ORDER BY 2 DESC ;
-----查每个对象
select owner , name "对象名", type "对象类型", referenced_name "关联表" from USER_DEPENDENCIES where referenced_name = 'INT_TBL_EVENT' and referenced_type = 'TABLE'
----统计个对象
select count(object_name) num, object_type from USER_objects group by object_type order by num desc
----查ddl语句
select dbms_metadata.get_ddl('TABLE','SZX_C_O_01','SUNZHAOXIONG') from dual
-----统计索引
select count(index_name) num, table_name from user_indexes group by table_name order by num asc,table_name
select count(index_name) from user_indexes
select COUNT from user_objects where object_type = 'INDEX'
--分区所以
select * from user_objects where object_type = 'INDEX PARTITION'
--每个索引有多少条分区索引
select count(subobject_name) num, object_name from USER_objects where object_type = 'INDEX PARTITION' group by object_name order by num ,object_name
select * from user_indexes where index_name = 'INT_TBL_PTRANACCT'
select * from user_indexes where table_name = 'INT_TBL_EVENT'
------查询关联关系
select *from USER_dependencies where referenced_type = 'TABLE'
------使用这条语句查哪台主机使用FY连数据库-------
select sid,serial#,machine,osuser from v$session where username='FY'
----oracle查看类型种类和总个数-----
select object_type ,COUNT(*) from dba_objects where owner='TEST1' GROUP BY object_type
ORDER BY 2;
----oracle查看类型种类详情----
select object_name,object_type from dba_objects where owner='TEST1' ORDER BY 2,1
---oracle删除用户---
user test;
若用户拥有对象,则不能直接删除,否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象,然后再删除用户。
drop user 用户名 cascade;
拥有connect role 的用户还能够创建表、视图、序列(sequence)、簇(cluster)、同义词(synonym)、回话(session)和其他 数据的链(link)
2》. resource role(资源角色)
更可靠和正式的数据库用户可以授予resource role。
resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。
3》. dba role(数据库管理员角色)
dba role拥有所有的系统权限
包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有
(2)授权命令
语法: grant connect, resource to 用户名;
例子: grant connect, resource to test;
(3)撤销权限
语法: revoke connect, resource from 用户名;
列子: revoke connect, resource from test;
----oracle创建/授权/删除角色----
除了前面讲到的三种系统角色
connect、resource和dba,用户还可以在oracle创建自己的role。用户创建的role可以由表或系统权限或两者的组合构成。为了创建role,用户必须具有create role系统权限。
1》创建角色
语法: create role 角色名;
例子: create role testRole;
2》授权角色
语法: grant select on class to 角色名;
列子: grant select on class to testRole;
注:现在,拥有testRole角色的所有用户都具有对class表的select查询权限
3》删除角色
语法: drop role 角色名;
例子: drop role testRole;
注:与testRole角色相关的权限将从数据库全部删除
---- --数据库允许的最大连接数---
select value from v$parameter where name ='processes';
---- --当前的数据库连接数-----
select count(*) from v$process ;
------修改最大连接数:------
alter system set processes = 300 scope = spfile;
注意:要关闭数据库重启才能生效
shutdown immediate; --关闭数据库
startup; --重启数据库
-------查看当前有哪些用户正在使用数据----
select osuser, a.username, cpu_time/executions/1000000||'s', b.sql_text, machine
from v$session a, v$sqlarea b
where a.sql_address =b.address
order by cpu_time/executions desc;
----当前的session连接数---
select count(*) from v$session
----并发连接数---
select count(*) from v$session where status='ACTIVE';
---【【【 在表空间新增数据文件和修改表空间数据文件的大小 / 扩表空间 / 表空间扩展 】】】----
现在服务器上查剩余磁盘(看数据文件挂载[mount]的那个磁盘)
df -h
查看当前用户所属的表空间
select * from dba_users where username='CXC_SOURCE';
指定表空间找到数据文件的file_name
SELECT file_name, tablespace_name, bytes/(1024*1024) M FROM dba_data_files where tablespace_name='SHSNC';
修改表空间数据文件的大小
ALTER database datafile '/u01/app/oracle/product/10.1/dbs/users01.dbf' RESIZE 32M;
在表空间新增加数据文件(单个数据文件最大限制32G)--注意:每个文件大小最大30G-----
ALTER TABLESPACE SHSNC ADD DATAFILE '/home/app/oradata/orcl/shsnc35.dbf' SIZE 30G;
ALTER TABLESPACE SHSNC ADD DATAFILE '/home/app/oradata/orcl/shsnc36.dbf' SIZE 30G;
ALTER TABLESPACE SHSNC ADD DATAFILE '/home/app/oradata/orcl/shsnc37.dbf' SIZE 30G;
ALTER TABLESPACE SHSNC ADD DATAFILE '/home/app/oradata/orcl/shsnc38.dbf' SIZE 30G;
ALTER TABLESPACE SHSNC ADD DATAFILE '/home/app/oradata/orcl/shsnc39.dbf' SIZE 30G;
ALTER TABLESPACE SHSNC ADD DATAFILE '/home/app/oradata/orcl/shsnc40.dbf' SIZE 30G;
ALTER TABLESPACE SHSNC ADD DATAFILE '/home/app/oradata/orcl/shsnc41.dbf' SIZE 30G;
重新查询
SELECT file_name, tablespace_name, bytes/(1024*1024) M FROM dba_data_files where tablespace_name='USERS';
--添加表字段数据的两种方法
select * from zlz_test for update
select rowid,t.* from zlz_test t
--验证表中字段正则表达式是否正确
select 'A',a.* from zlz_test1 a union all
select 'B',b.* from zlz_test1 b where regexp_like(address,'^(.*省)+.+市.+(区|县)');
-------查表所属分区-----
select * from all_all_tables where table_name='zlz_fenqu_1'
----新增一个表,将表中ID字段修改为自增长
create table ZLZ_TEST_CHINESE
(
id NUMBER,
name VARCHAR2(28)
)
create sequence st_id
increment by 1
start with 1
maxvalue 999999 ---建立一个最小为1,最大为999999的一个序列号会自动循环的序列
cycle;
--查看当前数据源的表空间容量信息
select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as "总块数",
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);
--查看表空间使用百分比
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name and free.tablespace_name='ZLZTBS';
--建立删表语句(含表结构)
select 'drop table '||segment_name||' purge;' MB
from dba_segments
where tablespace_name = 'ZLZTBS' and segment_name not like '%$$'
group by segment_name;
--查看某个表是否正在执行与杀进程
select t.P1,
t.P2,
t.P3,
t.event,
t.sid,
BLOCKING_SESSION,
t.SQL_EXEC_START,
(sysdate - t.SQL_EXEC_START) * 24,
t.PROGRAM,
T.INST_ID,
t.SQL_ID,
T.pdml_status,
T.pddl_status,
T.SID,
t.PROGRAM,
t.SERVICE_NAME,
t.USERNAME,
t.MACHINE,
t.PROCESS,
T.SERIAL#,
''';',
to_char(substr(a.SQL_FULLTEXT, 1, 3000)),
a.SQL_FULLTEXT,
'alter system kill session ''' || t.sid || ',' || t.SERIAL# || ',@' ||
t.inst_id || ''';',
t.USERNAME,
t.STATUS
from Gv$sql a, gv$session t
where t.sql_id = a.SQL_ID
and a.sql_id in
(select a.SQL_ID
from gv$session a, gv$sqlarea b
where a.SQL_ID = b.SQL_ID
and a.INST_ID = b.INST_ID
and b.SQL_TEXT like '%SIM_TB_ACCESS_LOG_BAK%')
and t.INST_ID = a.INST_ID
and a.SQL_FULLTEXT not like '%gv$session%'
and upper(a.SQL_FULLTEXT) like '%SIM_TB_ACCESS_LOG_BAK%';