Oracle 常用命令及近期使用问题集合
【问题与背景】
一、删除一个表?一个库?
drop table table_name; drop table database_name; 二、删除一个用户下所有的数据? 最好先停掉库,shutdown immediate; && startup 这样能保证没有用户连接 或者 SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME=///'USERNAME///'; 查询到连接 SID SERIAL# ---------- ---------- 24 25341 结束此用户的所有会话 SQL>ALTER SYSTEM KILL SESSION ///'24,25341///'; System altered. 再删除:DROP USER USERNAME CASCADE; 三、创建用户并授权 (本例给连接,修改,管理员) create user username identified by passwd; grant connect,resource,dba to username; commit; 四、如何设置字符集? 查看客户端字符集环境: select * from nls_instance_parameters;其来源于v$parameter 设置过程 SVRMGR> SHUTDOWN IMMEDIATE; SVRMGR> STARTUP MOUNT; SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION; SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0; SVRMGR> ALTER DATABASE OPEN; SVRMGR> ALTER DATABASE CHARACTER SET zhs16gbk; SVRMGR> SHUTDOWN IMMEDIATE; SVRMGR> STARTUP; 如果报以下的错: SQL> alter database character set zhs16gbk; alter database character set zhs16gbk * ERROR at line 1: ORA-12712: new character set must be a superset of old character set 处理方法: SQL> ALTER DATABASE character set INTERNAL_USE zhs16gbk; # 使用INTERNAL_USE可以跳过超集的检查,ALTER DATABASE character set INTERNAL_USE 五、查询实例?所有库名?表名? select * from v$database; show parameter db; // 查看当前的所有数据库: show parameter optimizer; //显示设置信息 select table_name from user_tables; //当前用户的表 select table_name from all_tables; //所有用户的表 select table_name from dba_tables; //包括系统表 看字段名与数据类型: desc talbename; 查看主键: select * from user_constraints where constraint_type=///'P///' and TABLE_name=upper(///'TRD_USER///') 查看表空间的sql语句 col tablespace_name format a10; select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) ///"% used///", round((f.free/a.total)*100) ///"% Free///" from (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a, (select tablespace_name, round(sum(bytes/(1024*1024))) used from dba_extents group by tablespace_name) u, (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f WHERE a.tablespace_name = f.tablespace_name and a.tablespace_name = u.tablespace_name; 六、增加表空间 为表空间增加数据文件: alter tablespace users add datafile ///'/u01/oracle/oradata/trds/system01.dbf///' size 1000M; 增加表空间原有数据文件尺寸: alter database datafile ///'/u01/oracle/oradata/trds/system01.dbf///' resize 1000M; 七、数据库临时表空间的数据文件的丢失 当数据库的临时表空间的数据文件丢失也会引起 ORA-01157的错误。因为数据库对临时表空间的数据文件不会发生检查点,所以这个时候数据库照样能够打开。这种情况下的解决方法是逻辑上删除临时表空间的数据文件,并且重新增加一个新的临时表空间的数据文件。 例如: SELECT * FROM DBA_OBJECTS ORDER BY OBJECT_NAME; select * from dba_objects order by object_name; * ERROR at line 1: ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: ///'/Oracle/oradata/temp01.dbf///' ALTER DATABASE TEMPFILE ///'/oracle/oradata/temp01.dbf///' DROP; SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_TEMP_FILES; ALTER TABLESPACE TEMP ADD TEMPFILE ‘/Oracle/oradata/temp01.dbf‘ SIZE 100M; 八、查看ORACLE 用户连接数 select count(*) from v$session #连接数 Select count(*) from v$session where status=///'ACTIVE///' #并发连接数 查看oracle 数据库状态 select status from v$instance; select open_mode from v$database; 九、PLS-213: package STANDARD not accessible报错解决方法 sqlplus /nolog SQL> connect / as sysdba SQL> $ORACLE_HOME/rdbms/admin/catalog.sql SQL> $ORACLE_HOME/rdbms/admin/catproc.sql SQL> $ORACLE_HOME/rdbms/admin/catexp.sql 十、运行lsnrctl start 出现 TNSLSNR for Linux: Version 8.1.7.0.0 - Production System parameter file is /oracle/product/8.1.7/network/admin/listener.ora Log messages written to /oracle/product/8.1.7/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LinServer)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LinServer)(PORT=2481))(PROTOCOL_STACK=(PRESENTATION=GIOP)(SESSION=RAW))) TNS-01201: Listener cannot find executable /oracle/product/8.1.7/bin/extproc for SID PLSExtProc 把binl里的extprocO 做一个叫extproc的link 十一、oemapp相关命令,如下: oemapp dbastudio oemapp console oemapp txtm 十二、如何创建一个实例? CREATE DATABASE trds CONTROLFILE REUSE LOGFILE ///'/data/oracle/trds/redo01.log///' SIZE 1M REUSE, ///'/data/oracle/trds/redo02.log///' SIZE 1M REUSE, ///'/data/oracle/trds/redo03.log///' SIZE 1M REUSE, ///'/data/oracle/trds/redo04.log///' SIZE 1M REUSE DATAFILE ///'/data/oracle/trds/system01.dbf///' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M CHARACTER SET WE8ISO8859P1; CREATE ROLLBACK SEGMENT rb_temp STORAGE (INITIAL 100 k NEXT 250 k); ALTER ROLLBACK SEGMENT rb_temp ONLINE; CREATE TABLESPACE rbs DATAFILE ///'/data/oracle/trds/rbs01.dbf///' SIZE 5M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 150M; CREATE TABLESPACE users DATAFILE ///'/data/oracle/trds/users01.dbf///' SIZE 3M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 150M; CREATE TABLESPACE temp DATAFILE ///'/data/oracle/trds/temp01.dbf///' SIZE 2M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 150M; CREATE ROLLBACK SEGMENT rb1 STORAGE(INITIAL 50K NEXT 250K) tablespace rbs; CREATE ROLLBACK SEGMENT rb2 STORAGE(INITIAL 50K NEXT 250K) tablespace rbs; CREATE ROLLBACK SEGMENT rb3 STORAGE(INITIAL 50K NEXT 250K) tablespace rbs; CREATE ROLLBACK SEGMENT rb4 STORAGE(INITIAL 50K NEXT 250K) tablespace rbs; ALTER ROLLBACK SEGMENT rb1 ONLINE; ALTER ROLLBACK SEGMENT rb2 ONLINE; ALTER ROLLBACK SEGMENT rb3 ONLINE; ALTER ROLLBACK SEGMENT rb4 ONLINE; ALTER ROLLBACK SEGMENT rb_temp OFFLINE; DROP ROLLBACK SEGMENT rb_temp ; |