oracle 系统操作语句(持续更新中...)

查询所有的schema：

select username from dba_users where account_status = 'OPEN';

select username from sys.dba_users where account_status = 'OPEN';

select * fromall_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;

删除某个schema：删除表空间，以为包括的数据文件：

drop user der322 cascade;

drop tablespace tbs_der322 including contents and datafiles;

一个表空间下面有多少个数据文件？

启动状态

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";

显示初始化参数命令  show parameter

需要说明的如果你希望修改这些初始化的参数，可以到文件：盘符:\oracle目录\admin\数据库实例\pfile\init.ora文件中去修改。

长期更新中...