目录
- 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;
文章评论