--用户的删除,创建,表空间创建,分配指定表空间,授权
drop user bhthpro cascade;
create user bhthpro identified by bhthpro;
create tablespace transforex datafile 'D:\oraclexe\app\oracle\oradata\XE\transforex.dbf' size 2048M;
alter user bhthpro default tablespace transforex;
grant connect, resource, dba to bhthpro;
---------------------------------------------------------------------------------------------------------------------
如果有出现字符集ORA-12899问题
运行cmd命令弹出窗口然执行下面语句
输入:sqlplus /nolog
SQL>conn / as sysdba
若此时数据库服务器已启动,则先执行 SHUTDOWN IMMEDIATE 命令关闭数据库服务器,
然后执行以下命令:
SQL>shutdown immediate
SQL>STARTUP MOUNT
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL>ALTER DATABASE CHARACTER SET ZHS16GBK;
ERROR at line 1:ORA-12721: operation cannot execute when other sessions are active
若出现上面的错误,使用下面的办法进行修改,使用INTERNAL_USE可以跳过超集的检查:
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;
--------------------------------------------------------------------------------------------------------------------
复制一个用户对象到另一个用户上
原主机上:
exp system/system_password owner=你想导出的用户1,用户2... file=filename log=logname目标主机上:
imp system/system_password fromuser=你导出的用户名1,用户2... touser=你要导入的用户名1,用户2... file=filename log=logname
(注意,imp时,fromuser和touser的顺序是一一对应的)
--------------------------------------------------------------------------------------------------------------
查询表空间语句:
select b.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
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.file_id,b.bytes
order by b.tablespace_name
-----------------------------------------------------------------------------------------------------
如果有出现Ora-12514:TNS:监听程序当前无法识别链接描述符中请求的服务。
打开:%Oracle_Hoem%/network/admin/listener.ora文件
在
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\Oracle\Oracle10g)
(PROGRAM = extproc)
)
后面添加:
(SID_DESC =
(GLOBAL_DBNAME = ORACLE)
(ORACLE_HOME = D:\Oracle\Oracle10g)
(SID_NAME = ORACLE)
)
最后变成:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\Oracle\Oracle10g)
(PROGRAM = extproc)
) (SID_DESC =
(GLOBAL_DBNAME = ORACLE)
(ORACLE_HOME = D:\Oracle\Oracle10g)
(SID_NAME = ORACLE),
)
)
重启oracle
---------------------------------------------------------------------------------------------------------------------
ORA-28002错误: 如果oracle 11g密码过期默认180天,
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
修改密码无期限:ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
-------------------------------------------------------------------------------------------------------------------------------------------------------
可以用以下语句查询一个汉字占用的字节长度
select lengthb('你') from dual;
查询数据库编码语句: select * from v$nls_parameters;
---------------------------------------------------------------------------------------------------