查询当前实例的状态:select status from v$instance; (正常结果:OPEN)
查询当前数据库的打开模式:select open_mode from v$database; (正常结果:READ WRITE)
查询当前scn:select to_char(current_scn) from v$database;
查询所有的schema:
select username from dba_users where account_status = 'OPEN';
select username from sys.dba_users where account_status = 'OPEN';
select
*
from
all_users
;
查询某个schema下面的所有表(sys下面):select table_name from dba_tables where owner='X';
查询某个schema下面的所有表(当前用户下):SELECT table_name FROM user_tables;
查询所有的表空间(sys下面):select tablespace_name from dba_tablespaces;
查看当前用户的缺省表空间:select username,default_tablespace from user_users; (记住:是当前用户,想查apple,先conn apple/apple)
select username,DEFAULT_TABLESPACE from sys.dba_users where username = 'DER322';
用户的加锁和解锁:alter user scott account lock/unlock;
清空共享池,刷新共享池:alter system flush shared_pool;
手动触发检查点:alter system checkpoint;
强制性切换日志:alter system switch logfile; (还没搞明白什么时候需要切换日志)
查询表占用的空间(官方建议超过2G,进行表分区)
select round(BYTES/1024/1024,2)||'M' from user_segments where segment_name='T_FP_ABNORMAL_DETAIL';
删除某个schema:删除表空间,以为包括的数据文件:
drop user der322 cascade;
drop tablespace tbs_der322 including contents and datafiles;
查看ORCL目录:select file_name from dba_data_files;
drop tablespace tbs_der322 including contents and datafiles cascadeconstraint;
一个表空间下面有多少个数据文件?
启动状态 | SQL语句 | 结果 |
nomount | select status from v$instance; | STARTED |
select open_mode from v$database; | ERROR at line 1: ORA-01507: database not mounted | |
mount | select status from v$instance; | MOUNTED |
select open_mode from v$database; | MOUNTED | |
open | select status from v$instance; | OPEN |
select open_mode from v$database; | READ WRITE 或者 READ ONLY |
修改sys密码:
ALTER USER SYS IDENTIFIED BY "newpass";
快捷键 / 可以查看上次的select语句,相当于重复执行。如之前无select,运行/,则显示:
SP2-0103: Nothing in SQL buffer to run. 可见/是显示sql buffer的。
显示初始化参数命令 show parameter
需要说明的如果你希望修改这些初始化的参数,可以到文件:盘符:\oracle目录\admin\数据库实例\pfile\init.ora文件中去修改。
各种文件的路径 (http://blog.itpub.net/29532781/viewspace-1174681/)
select name from v$datafile;
select name from v$tempfile;
select member from v$logfile;
select name from v$controlfile;
create pfile='/u01/app/....' from spfile; 参数文件可以指定位置
ho ls $ORACLE_HOME/dbs/orapw$ORACLE_SID 密码文件
日志文件的查看:
desc v$logfile
select member from v$logfile;
归档日志的查看:
archive log list;
查看归档路径
SQL> archive log list
Archive destination USE_DB_RECOVERY_FILE_DEST
SQL> show parameter db_recovery
db_recovery_file_dest string /u01/oracle/fast_recovery_area
增大容量:SQL> alter system set db_recovery_file_dest_size=10G;
修改路径:SQL> alter system set log_archive_dest_1='location=/home/archivelog';
(貌似show parameter db_recovery出来的路径和修改的不一样,但这个语句确实能修改成功。)
删除归档日志(7天)(rman下执行):DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; (发现有时候不管用,还要结合下面2条)
检查过期备份:crosscheck backupset; (可以看到除最近rman一次,之前所有的rman备份)
删除过期文件(可执行多次):delete noprompt obsolete; (删除过期rman文件,只保留最近一次;删除过期archivelog)
用report obsolete;可以看到的情况下,delete obsolete就可以删除。noprompt是表示不提示输入y/n,直接删除。
检查归档:crosscheck archivelog all; (发现有200个,但目录下却有1000个。手动删除800个)
删除过期归档:delete expired archivelog all;
后来发现上面都不管用,用:delete obsolete device type disk; (删除过期rman和arc,保留最后一次rman)
关于闪回:
开启闪回:alter database flashback on;
查看参数:SQL> show parameter flash;
db_flashback_retention_target integer 1440
1440分钟是24小时,表示闪回日志保留24小时。
闪回日志的目录是:/opt/oracle/fast_recovery_area/orcl/ORCL/flashback
修改闪回日志的保留时间是:alter system set db_flashback_retention_target=1440 scope=both;
查看闪回区目录:show parameter db_recovery_file_dest;
db_recovery_file_dest string /opt/oracle/fast_recovery_area/orcl
db_recovery_file_dest_size big integer 17271M
修改闪回日志的目录:alter system set db_recovery_file_dest='/opt/oracle/t1/flashback' scope=both; (好象重启DB才会生效)
配置闪回使用的空间大小:alter system set db_recovery_file_dest_size=10g scope=both; (马上可以看到闪回区文件变少了)
临时表空间太大,压缩
alter database tempfile '/opt/oracle/oradata/ORCL/ORCLpdb/temp01.dbf' resize 1024M;
-------------------------------------------------
创建和删除DBLink
CREATE DATABASE LINK ora83pd
CONNECT TO pd IDENTIFIED BY pd
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.83)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = ORCLPDB)
)
)';
Database link created.
select count(0) from tOdOrder@ora83pd;
如果创建全局dblink,必须使用systm或sys用户,在database前加public。
删除dblink
drop database link ora83pd;
---------------------------------------------