目录
- 1、删除部分数据
- 2、查看directory
- 3、查看指定概要文件(如default)的密码有效期设置
- 4、将密码有效期由默认的180天修改成“无限制”,修改之后不需要重启动数据库,会立即生效。
- 5、telnet 172.18.1.245 1521 不通,防火墙以及selinux已关闭;
- 6、关于索引操作:
- 7、oracle表空间查询(或参看:https://www.cnblogs.com/xwdreamer/p/3511047.html)
- 8、通过“段”查看哪些段占用空间大,表空间--段--区--块
- 9、查看用户的段,
- 10、查询Oracle数据库段SEGMENT和对象大小
- 11、数据库进程查询(ORA-12519错误的解决方案)
- 12.archivelog正确的清除方法
- 13、shutdown的用法
- 14、startup的用法
- 15、Oracle 查询库中所有表名、字段名、字段名说明,查询表的数据条数、表名、中文表名、
1、删除部分数据
SQL> delete from pdba whererownum<1000;
2、查看directory
sql>select * from dba_directories;
3、查看指定概要文件(如default)的密码有效期设置
sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
4、将密码有效期由默认的180天修改成“无限制”,修改之后不需要重启动数据库,会立即生效。
sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
5、telnet 172.18.1.245 1521 不通,防火墙以及selinux已关闭;
启动正常,可以查看状态:HOST=127.0.0.1,未把对外地址开放;所以修改两个位置:
(1)vim /etc/hosts; 172.18.1.245 ORACLEOGG1
(2)vim /data/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora中的HOST=ORACLEOGG1
[oracle@ORACLEOGG1 admin]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-JUN-2019 14:36:19 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 18-JUN-2019 15:00:29 Uptime 9 days 23 hr. 35 min. 50 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /data/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora Listener Log File /data/u01/app/oracle/diag/tnslsnr/ORACLEOGG1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orclogg1" has 1 instance(s). Instance "orclogg1", status READY, has 1 handler(s) for this service... Service "orclogg1XDB" has 1 instance(s). Instance "orclogg1", status READY, has 1 handler(s) for this service... The command completed successfully
6、关于索引操作:
查询所有所有索引: select * from user_sequences; 删除索引: drop sequence SEQ_ACM_CLAIM; 创建索引: CREATE UNIQUE INDEX SEQ_ACM_CLAIM ON TBL_ACM_CLAIM(SEQ_NO);
7、oracle表空间查询(或参看:https://www.cnblogs.com/xwdreamer/p/3511047.html)
(1)----查询表空间使用情况---
SELECT UPPER(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比", F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;
(2)查看表空间总使用率
SELECT A.TABLESPACE_NAME, ROUND(MAXTOTAL,0) AS TOTAL, ROUND((TOTAL-FREE)/TOTAL,3)*100 AS USED_PER FROM (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS TOTAL,SUM(MAXBYTES)/1024/1024 AS MAXTOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;
--
(3)查看表空间是否具有自动扩展的能力,并查看数据文件总量及当前使用量
SELECT T.TABLESPACE_NAME,D.FILE_NAME, D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T,DBA_DATA_FILES D WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME ORDER BY TABLESPACE_NAME,FILE_NAME;
(4)----修改表空间文件扩展方式:
--ALTER DATABASE DATAFILE '/u01/Oracle/oradata/orcl/ccen01.dbf' AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
(5) 扩容表空间(增加数据文件)
--alter tablespace MESPRD_TBSP add datafile '/data/u01/app/oracle/oradata/SHYCORCLPRD/datafile/MESPRD_03.dbf' size 1024M autoextend on next 1000m;
(6) 表空间物理文件对应
select b.file_name 物理文件名, b.tablespace_name 表空间, b.bytes/1024/1024 大小M, (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M, substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率 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.bytes order by b.tablespace_name;
8、通过“段”查看哪些段占用空间大,表空间--段--区--块
每张表都是作为“段”来存储的,可du以通过zhiuser_segments视图查看其相应信dao息,例:
SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments WHERE segment_name='EP_SC106'
(1) 首先用语句查询容量大于1G的数据段
select segment_name,sum(bytes)/1024/1024 as counts from dba_segments group by segment_name having sum(bytes)/1024/1024>1000 order by counts asc;
(2).根据LOB段查询该lob段属于哪个表
select table_name,segment_name from dba_lobs where segment_name='SYS_LOB0000136091C00003$$';
9、查看用户的段,
SELECT * FROM USER_SEGMENTS T;
10、查询Oracle数据库段SEGMENT和对象大小
(1)、查询段类别:
SELECT T.TABLESPACE_NAME, SEGMENT_TYPE, COUNT(1)
FROM USER_SEGMENTS T
WHERE 1 = 1
GROUP BY T.TABLESPACE_NAME, T.SEGMENT_TYPE
ORDER BY T.TABLESPACE_NAME, T.SEGMENT_TYPE;
SELECT T.TABLESPACE_NAME, SEGMENT_TYPE, COUNT(1) count1
FROM dba_SEGMENTS T
WHERE 1=1 and owner='NCC'
GROUP BY T.TABLESPACE_NAME, T.SEGMENT_TYPE
ORDER BY count1 desc;
(2)、查看数据库表空间存储对象的大小
SELECT * FROM (SELECT SEGMENT_NAME, T.SEGMENT_TYPE, SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE FROM USER_SEGMENTS T WHERE 1 = 1 GROUP BY T.SEGMENT_NAME, T.SEGMENT_TYPE) T WHERE 1 = 1 ORDER BY SEGMENT_SIZE DESC;
(3)、查看表空间占的总大小:
SELECT SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE FROM USER_SEGMENTS T;
(4)、实际表占用空间大小,包括表所属对象:INDEX、LOBINDEX、LOBSEGMENT
SELECT * FROM (SELECT SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE FROM (SELECT T.TABLESPACE_NAME, T.SEGMENT_NAME, T.SEGMENT_TYPE, T.BYTES FROM USER_SEGMENTS T WHERE 1 = 1 AND T.SEGMENT_TYPE NOT IN ('INDEX', 'LOBINDEX', 'LOBSEGMENT') UNION ALL SELECT T.TABLESPACE_NAME, UL.TABLE_NAME AS SEGMENT_NAME, 'TABLE' AS SEGMENT_TYPE, T.BYTES FROM USER_SEGMENTS T INNER JOIN USER_LOBS UL ON T.SEGMENT_NAME = UL.SEGMENT_NAME WHERE 1 = 1 AND T.SEGMENT_TYPE = 'LOBSEGMENT' UNION ALL SELECT T.TABLESPACE_NAME, UI.TABLE_NAME AS SEGMENT_NAME, 'TABLE' AS SEGMENT_TYPE, T.BYTES FROM USER_SEGMENTS T INNER JOIN USER_INDEXES UI ON T.SEGMENT_NAME = UI.INDEX_NAME WHERE 1 = 1 AND T.SEGMENT_TYPE IN ('INDEX', 'LOBINDEX') AND 1 = 1) T WHERE 1 = 1 GROUP BY SEGMENT_NAME, SEGMENT_TYPE) T WHERE 1 = 1 ORDER BY SEGMENT_SIZE DESC;
11、数据库进程查询(ORA-12519错误的解决方案)
select count(*) from v$process; 取得数据库目前的进程数。 select value from v$parameter where name = 'processes'; 取得进程数的上限。 alter system set processes = 300 scope = spfile; --修改最大连接数为300,也可以是其他值 select * from v$license 查询数据库自启动以来最大的并发数量
12.archivelog正确的清除方法
Oracle在开启了归档模式后,会在指定的archive目录下产生很多的archivelog文件,而且默认是不会定期清除的,时间长久了,该文件夹会占用很大的空间。
问题:如何定期正确删除archivelog文件呢? 很多人直接在archive目录下删除文件,这样其实不能达到在Oracle CLF文件中删除文件记录的效果。
正确方法:
(1)用RMAN连接目标DB:在命令行界面输入以下命令
RMAN target sys/orcle@orcl
(2)在RMAN命令窗口中,输入如下命令:
crosscheck archivelog all; delete expired archivelog all;
或者删除指定时间之前的archivelog:
RMAN> delete archivelog all completed before 'sysdate-2'; RMAN> delet archivelog until time 'sysdate-2';
(3)其它相关命令:
查看归档日志列表:
list archivelog all;
查看失效的归档日志列表:
list expired archivelog all;
(4)定期清除archivelog: 可以将如下代码写成shell文件或.bat,在crontab或控制面版的任务计划下添加新的定时任务:
RMAN target sys/*@orcl crosscheck archivelog all; delete expired archivelog all;
13、shutdown的用法
shutdown有四个参数:normal、transactional、immediate、abort。缺省不带任何参数时表示是normal。
(1)shutdown normal:
不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。
(2)shutdown transactional:
不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。
(3)shutdown immediate:
不允许新的连接、不等待会话结束、不等待事务结束、做一个检查点并关闭数据文件。没有结束的事务是自动ROLLBACK的。启动时不需要实例恢复。
(4)shutdown abort:
不允许新的连接、不等待会话结束、不等待事务结束、不做检查点且没有关闭数据文件。启动时自动进行实例恢复。
另外,对于normal、transactional、immediate,db buffer cache的内容写入了数据文件,没有提交的事务被回滚,所有的资源被释放,数据库被“干净”的关闭。
对于abort,db buffer cache的内容没有写入数据文件,没有提交的事务也没有回滚。数据库没有DISMOUNT和关闭,数据文件也没有关闭。当数据库启动时,需要通过REDO LOG恢复数据,通过回滚段对事务回滚,对资源进行释放。
14、startup的用法
startup [force] [restrict] [pfile= FILENAME] [open [recover][ DATABASE] | mount | nomount]
(1)startup open:
startup缺省的参数就是open,打开数据库,允许数据库的访问。当前实例的控制文件中所描述的所有文件都已经打开。
(2)startup mount:
mount数据库,仅仅给dba进行管理操作,不允许数据库的用户访问。仅仅只是当前实例的控制文件被打开,数据文件未打开。
(4)startup nomount:
仅仅通过初始化文件,分配出sga区,启动数据库后台进程,没有打开控制文件和数据文件。不能任何访问数据库。
(5)startup pfile= FILENAME:
以FILENAME为初始化文件启动数据库,不是采用缺省初始化文件。
(6)startup force:
中止当前数据库的运行,并开始重新正常的启动数据库。
(7)startup restrict:
只允许具有restricted session权限的用户访问数据库。
(8)startup recover:
数据库启动,并开始介质恢复。
使用shutdown的时候 normal和immediate可能都因为连接没有释放而造成数据库无法重启的现象。
可以采用以下两个方案:
SQL>shutdown abort; --这个方法是不等待会话结束就直接关闭掉数据库,一般情况下是不推荐使用的。实在关不掉的话,一般也没有什么问题。跟直接KILL进程差不多。 SQL>startup --shutdown abort以后在启动数据库。 SQL>startup force; --这个方法是直接关闭掉数据库然后再重启数据库,这个方式也可以达到关闭的目的,我个人使用的时候两个方法都行。
15、Oracle 查询库中所有表名、字段名、字段名说明,查询表的数据条数、表名、中文表名、
(1)查询某个用户的表名或数量
select count(*) from all_tables where owner='OGG_TRG';
(2)查看当前登录的用户的表:
select table_name from user_tables;
(3)查询所有表名:
select t.table_name from user_tables t;
(4)查询所有字段名:
select t.column_name from user_col_comments t;
(5)查询指定表的所有字段名:
select t.column_name from user_col_comments t where t.table_name = 'BIZ_DICT_XB';
(6)查询指定表的所有字段名和字段说明:
select t.column_name, t.column_name from user_col_comments t where t.table_name = 'BIZ_DICT_XB';
(7)查询所有表的表名和表说明:
select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name;
(8)查询模糊表名的表名和表说明:
select t.table_name from user_tables t where t.table_name like 'BIZ_DICT%'; select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name where t.table_name like 'BIZ_DICT%';
(9)查询表的数据条数、表名、中文表名
select a.num_rows, a.TABLE_NAME, b.COMMENTS from user_tables a, user_tab_comments b WHERE a.TABLE_NAME = b.TABLE_NAME order by TABLE_NAME;
文章评论