Oracle 常用技巧和脚本 and On database character set

 

http://www.itpub.net/276524.html

 

设置客户端字符集为US7ASCII
D:/>SET NLS_LANG=AMERICAN_AMERICA.US7ASCII
查看服务器字符集为US7ASCII
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET US7ASCII

建立测试表
SQL> CREATE TABLE TEST (R1 VARCHAR2(10));

Table created.

插入数据
SQL> INSERT INTO TEST VALUES('东北');

1 row created.

SQL> SELECT * FROM TEST;

R1
----------
东北

SQL> EXIT

 

如何查看 当前数据库的字符集? 在SQL*PLUS下，运行:. “select userenv('language') from dual;”

[Q]怎么样查看数据库字符集
[A]数据库服务器字符集select * from nls_database_parameters，其来源于props$，是表示数据库的字符集。
客户端字符集环境select * from nls_instance_parameters,其来源于v$parameter，
表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表
会话字符集环境 select * from nls_session_parameters，其来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。
客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。如果多个设置存在的时候，alter session>环境变量>注册表>参数文件
字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。

[Q]怎么样修改字符集
[A]8i以上版本可以通过alter database来修改字符集，但也只限于子集到超集，不建议修改props$表，将可能导致严重错误。
Startup nomount;
Alter database mount exclusive;
Alter system enable restricted session;
Alter system set job_queue_process=0;
Alter database open;
Alter database character set zhs16gbk;

Oracle 常用技巧和脚本

1. 如何查看ORACLE的隐含参数？


ORACLE的显式参数，除了在INIT.ORA文件中定义的外，在svrmgrl中用"show parameter *"，可以显示。但ORACLE还有一些参数是以“_”，开头的。如我们非常熟悉的“_offline_rollback_segments”等。

这些参数可在sys.x$ksppi表中查出。

语句：“select ksppinm from x$ksppi where substr(ksppinm,1,1)='_'; ”




2. 如何查看安装了哪些ORACLE组件？

进入${ORACLE_HOME}/orainst/，运行./inspdver，显示安装组件和版本号。



3. 如何查看ORACLE所占用共享内存的大小？

可用UNIX命令“ipcs”查看共享内存的起始地址、信号量、消息队列。

在svrmgrl下，用“oradebug ipc”，可看出ORACLE占用共享内存的分段和大小。

example:

SVRMGR> oradebug ipc
-------------- Shared memory --------------

Seg Id Address Size
1153 7fe000 784
1154 800000 419430400
1155 19800000 67108864


4. 如何查看当前SQL*PLUS用户的sid和serial#?

在SQL*PLUS下，运行：

“select sid, serial#, status from v$session

where audsid=userenv('sessionid');”



5. 如何查看当前数据库的字符集？

在SQL*PLUS下，运行：

“select userenv('language') from dual;”

或：

“select userenv('lang') from dual;”



6. 如何查看数据库中某用户，正在运行什么SQL语句？

根据MACHINE、USERNAME或SID、SERIAL#，连接表V$SESSION和V$SQLTEXT，可查出。

SQL*PLUS语句：

“SELECT SQL_TEXT FROM V$SQL_TEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESS

AND T.HASH_VALUE=S.SQL_HASH_VALUE

AND S.MACHINE='XXXXX' OR USERNAME='XXXXX' -- 查看某主机名，或用户名

/”

7. 如何删除表中的重复记录？

例句：

DELETE
FROM table_name a
WHERE rowid > ( SELECT min(rowid)
FROM table_name b
WHERE b.pk_column_1 = a.pk_column_1
and b.pk_column_2 = a.pk_column_2 );


8. 手工临时强制改变服务器字符集

以sys或system登录系统，sql*plus运行：“create database character set us7ascii;".
有以下错误提示：
* create database character set US7ASCII
ERROR at line 1:
ORA-01031: insufficient privileges
实际上，看v$nls_parameters，字符集已更改成功。但重启数据库后，数据库字符集又变回原来的了。
该命令可用于临时的不同字符集服务器之间数据倒换之用。
9. 怎样查询每个instance分配的PCM锁的数目

用以下命令：

select count(*) "Number of hashed PCM locks" from v$lock_element where bitand(flags,4)<>0

/

select count(*) "Number of fine grain PCM locks" from v$lock_element

where bitand(flags,4)=0

/

10. 怎么判断当前正在使用何种SQL优化方式？

用explain plan产生EXPLAIN PLAN，检查PLAN_TABLE中ID=0的POSITION列的值。

e.g.

select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0

/

11. 做EXPORT时，能否将DUMP文件分成多个？

ORACLE8I中EXP增加了一个参数FILESIZE，可将一个文件分成多个：

EXP SCOTT/TIGER FILE=（ORDER_1.DMP,ORDER_2.DMP,ORDER_3.DMP) FILESIZE=1G TABLES=ORDER；



其他版本的ORACLE在UNIX下可利用管道和split分割：

mknod pipe p

split -b 2048m pipe order & #将文件分割成，每个2GB大小的，以order为前缀的文件：

#orderaa,orderab,orderac,... 并将该进程放在后台。

EXP SCOTT/TIGER FILE=pipe tables=order

 

 

如果能够保证这四个字符集是一致的，则在Export、Import过程中，ORACLE字符集就不用 发生转换。 可用以下办法检查数据库字符集: 通过InitXXXX.ora文件进行查看； 借助SQL 语句查看： select name,VALUE$ from SYS.PROPS$ WHERE name=‘NLS_CHARACTERSET’。

 

http://www.itpub.net/242761,2.html

 

http://www.itpub.net/2407.html

 

使用
imp help=y
可以看到imp的帮助。

mp expuser/password@SID file=file.dmp log=file.log ignore=y fromuser=user1,user2..........

 

D:/>exp scott/tiger file=a.dmp
D:/>imp test/test fromuser=scott tables=emp file=a.dmp
D:/>imp test/test tables=dept file=a.dmp

说明：
如果导出用户没有DBA权限，则导入用户可以不用指定fromuser、touser参数
如果导出用户拥有DBA权限，则导入用户也必须拥有DBA权限

你确信导出时客端字符集设置和服务端是是相同的吗？
如果不同会进行装换的这可能会造成数据损坏

（1）知道字符集查ID（得到十进制的ID）
例如：
1* SELECT NLS_CHARSET_ID('ZHS16GBK') FROM DUAL
SQL> /

NLS_CHARSET_ID('ZHS16GBK')
--------------------------
852

知道字符集ID查名字
SQL> SELECT NLS_CHARSET_NAME(852) FROM DUAL
SQL> /

NLS_CHAR
--------
ZHS16GBK

 

34字节转成十进制就为查到字符集ID

dmp存储的是导出客户端的字符集
你是没有办法由此得知原数据库的字符集的.

如果导出时客户端NLS_LANG和Server端不同
那么转换可能已经损坏了数据，那么你是没有办法使用这个dmp文件来正确恢复数据的

Q:

关于表空间问题——求救

我要把数据库A里的表X移植到数据库B，在数据库B中我新建了一张与A中完全一样的表，但是在imp文件是，导入数据到一部分时就出错：IMP-00058: ORACLE error 1653 encountered。
     应该时提示空间不够吧，于是我就把数据库B中一些不用的表清空，还是一样的错误提示？？请大家指教一下，到底是表空间不够，还是数据库用户空间不够导致？ 2中不同的原因又该如何处理？？？
     小妹在这里先感谢大家了，希望能和oracle高手及兴趣爱好者交流。QQ：416609165。

oerr ora 1653
01653, 00000, "unable to extend table %s.%s by %s in tablespace %s"
// *Cause:  Failed to allocate an extent for table segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//         files to the tablespace indicated.

 

向表空间中添加数据文件
ALTER TABLESPACE tablespace ADD DATAFILE filespec [autoextend_clause]
例子： ALTER TABLESPACE user_data ADD DATAFILE '/u01/oradata/userdata03.dbf' SIZE 200M;