Oracle常用语句 _生活知道

–1 Oracle 版本查询
SELECT * FROM V$VERSION;
SELECT * FROM PRODUCT_COMPONENT_VERSION;
–2 Oracle字符集查看
SELECT * FROM Nls_Database_Parameters where PARAMETER in;–服务端
SELECT Userenv FROM dual;–客户端
–3 oracle用户删除
–查看用户占用了多少空间
SELECT owner, tablespace_name, ROUND/ 1024 / 1024/ 1024, 2) &34;
FROM dba_segments
GROUP BY owner, tablespace_name
ORDER BY SUMDESC;
–查询当前登陆用户所占空间大?。?
select sum / 1024 / 1024/1024 as SIZE_G
from user_segments
–查询所有dba用户所占空间大?。?
select sum / 1024 / 1024/1024 as SIZE_G
【Oracle常用语句】from dba_segments
–查看当前用户下所有表占用的空间情况：
select segment_name,tablespace_name,bytes,blocks from user_segments
/*先删除用户，再删除表空间，必须保证表空间没有用户使用才能删除*/
–查询用户的相关信息：
select * from DBA_USERS;
–删除用户：
drop user username cascade;
–删除表空间和它的系统文件：
drop tablespace YSSUCO including contents and datafiles;
–如果发现不能删除用户，应该是用户还有连接，查询用户的连接：
select username,sid,serial39;username&，然后杀掉：
alter system kill session ‘1505,81’;
–再次查询，可以查询它的状态：
select saddr,sid,serial39;username&39;/u01/app/oracle/oradata/FA/YSSUCO01.DBF&39;/u01/app/oracle/oradata/FA/YSSUCO02.DBF&39;/u01/app/oracle/oradata/FA/YSSUCO03.DBF& mkdir /dp_dir
[root@CNZHAULAMC094 ~] chmod -R 777 /dp_dir/expdp.dmp
[root@CNZHAULAMC094 ~]39;/dp_dir&39;/dp_dir&39;WHERE c_user_code=ywy&39;您创建的目录的名称&39;导出的文件名称&39;6B86B273FF34FCE19D6B804EFF5A3F5747ADA4EAA22F1D49C01E52DDB7875B4B&39;100M&39;processes&39;s&39;NEWGZDB&,
a.USERNAME,
b.PARSE_CALLS,
b.PARSING_SCHEMA_NAME,
b.CPU_TIME/1000000,
b.ELAPSED_TIME/1000000,
b.DISK_READS,
b.DIRECT_WRITES,
b.BUFFER_GETS,
a.event,
b.sql_text,
b.SQL_FULLTEXT
from v$session a inner join v$sqlarea b
on a.SQL_HASH_VALUE = https://www.30zx.com/b. hash_value and b.PARSING_SCHEMA_NAME=upper
–物理读最高sql语句
select a.USERNAME,
a. USER_ID,
b.PARSE_CALLS,
b.PARSING_SCHEMA_NAME,
b.CPU_TIME/1000000,
b.ELAPSED_TIME/1000000,
b.DISK_READS,
b.DIRECT_WRITES,
b.BUFFER_GETS,
b.sql_text,
b.SQL_FULLTEXT
from dba_users a inner join v$sqlarea b
on a.USER_ID = b.PARSING_USER_ID and b.PARSING_SCHEMA_NAME=upper and disk_reads>1000000
–查询前10名执行最多次数SQL语句
select sql_text &34;, executions &34;
fromover
exec_rank
from v$sqlarea)
where exec_rank <= 10;
–查询前10名占用CPU最高的SQL语句
select sql_text &34;,
c_t &34;,executions &34;,cs &34;　from )cs,
rank over top_time
from v$sqlarea) where top_time <= 10
–查询前10名执行时间最长SQL语句
select sql_text &34;,
c_t &34;,executions &34;,cs &34;
from )cs,
rank over top_time
from v$sqlarea) where top_time <= 10
–查询前10名最耗资源SQL语句
select sql_text &34;,
DISK_READS &34;,cs &34;
from )cs,DISK_READS,
rank over top_disk
from v$sqlarea) where top_disk <= 10
–查询前10名最耗内存SQL语句
select sql_text &34;,
BUFFER_GETS &34;,cs &34;
from )cs,BUFFER_GETS,
rank over top_mem
from v$sqlarea) where top_mem <= 10
–查看锁表语句
Select
c.sid,
c.serial39;OSMPPORTAL&39;CMS&39;CMS&39;/u01/oradata/orac/work01.dbf&39;CMS&39;alter system kill session &39;&39;,&||&39;&39;
FROM V$SESSION
WHERE USERNAME=&39;;
alter system kill session &39; immediate;
alter system kill session &39; immediate;
alter system kill session &39; immediate;
alter system kill session &39; immediate;
alter system kill session &39; immediate;
alter system kill session &39; immediate;
alter system kill session &39; immediate;
alter system kill session &39; immediate;
alter system kill session &39; immediate;
alter system kill session &39; immediate;
alter system kill session &39; immediate;
–再复制这些语句，粘贴到sqlplus中执行，来杀掉Test2的session 。
如果不存在cms用户：
create user cms identified bycms default tablespace work01 temporary tablespace temp;
不管存不存在都应该给cms授权
grant connect,resource to cms;
7.最后将数据导入
下面在windows的cmd下将用户导进去
imp file=e:\\cms.dmp fromuser=cms touser=cms userid=cms/cms@orac
这里要注意的是之前们可以临时给cms赋予dba权限，最后回收他，但是回收之后，记得再给cms赋予resource权限NFO,USER_PROJECT_INFO) file=/home/oracle/osmp2.dmp
–11oracle如何生成awr报告
[root@localhost ~]39;html&39;text&39;html&39;s Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
test TEST 319 22 Jun 2020 00:00 1
320 22 Jun 2020 01:00 1
321 22 Jun 2020 02:00 1
322 22 Jun 2020 03:00 1
323 22 Jun 2020 04:00 1
324 22 Jun 2020 05:00 1
325 22 Jun 2020 06:00 1
326 22 Jun 2020 07:00 1
327 22 Jun 2020 08:00 1
328 22 Jun 2020 09:00 1
329 22 Jun 2020 10:00 1
330 22 Jun 2020 11:00 1
331 22 Jun 2020 12:00 1
332 22 Jun 2020 13:00 1
333 22 Jun 2020 14:00 1
334 22 Jun 2020 15:00 1
335 22 Jun 2020 16:00 1
336 22 Jun 2020 17:00 1
337 22 Jun 2020 18:00 1
338 22 Jun 2020 19:00 1
339 22 Jun 2020 20:00 1
340 22 Jun 2020 20:43 1
341 22 Jun 2020 20:44 1
342 22 Jun 2020 20:58 1
343 22 Jun 2020 20:58 1
Enter value for begin_snap: 339
Begin Snapshot Id specified: 339
Enter value for end_snap: 343
End Snapshot Id specified: 343
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_339_343.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrrpt_20200622.htlm
……
</body></html>
Report written to awrrpt_20200622.htlm
awr报告生成路径：/home/oracle/awrrpt_20200622.htlm
以上就是朝夕生活（www.30zx.com）关于“Oracle常用语句”的详细内容，希望对大家有所帮助！

Oracle常用语句

猜你喜欢