--解决中文乱码
--查看编码
select userenv('LANGUAGE') From Dual
--修改编码
alter database character set internal_use AL32UTF8;
Oracle 查看表空间的大小及使用情况sql语句
http://blog.itpub.net/30246303/viewspace-2138852/
oracle创建表空间、用户、用户授权、删除表空间、删除用户
https://www.cnblogs.com/smallrock/p/3542278.html
--查询所有表空间
SELECT * FROM dba_users;
--创建用户
CREATE USER username IDENTIFIED BY _password;
--授权DBA
GRANT DBA TO username ;
--删除用户及关联数据
DROP USER username CASCADE;
--导入导出数据
imp username/username@192.168.8.7:1521/orcl file="D:\DB\test.dmp" full=y ignore=y
exp username/username@192.168.8.7:1521/orcl owner=username file=D:\DB\test.dmp
--创建表空间
CREATE TABLESPACE SYSWARE_29S_TEST
logging DATAFILE 'F:\app\oradata\orcl\***.dbf'
SIZE 100m
AUTOEXTEND ON
NEXT 100m MAXSIZE 30480m
EXTENT MANAGEMENT LOCAL;
--创建用户并指定表空间
CREATE USER _TEST identified by _TEST
DEFAULT TABLESPACE _TEST
https://www.cnblogs.com/muyeh/p/6416918.html
linux下创建oracle表空间
http://blog.itpub.net/29485627/viewspace-1277018/
-- -- --
--数据泵导入导出,高低版本兼容性问题
sqlplus system/密码
create directory expnc_dir as 'E:\directory';
Grant read,write on directory expnc_dir to directory;
expdp username/username@127.0.0.1:1521/orcl directory=expnc_dir dumpfile=username.dmp schemas=username logfile=username.log version=11.2.0.1.0
impdp username/username@127.0.0.1:1521/orcl schemas=username directory=expnc_dir dumpfile=username.dmp logfile=username.log version=要导入的oracle版本号
-- -- --
--导出空表解决
select table_name from user_tables where NUM_ROWS=0;
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
--执行生成的语句
-- 自动生成id
lower(RAWTOHEX(sys_guid()))
--使用序列
SEQUENCE.nextVal
-- oracle报错,无效的月份,解决
alter session set NLS_DATE_FORMAT='DD-MON-YY';
to_char("test",'DD-MON-YYYY HH:MI:SS')
oracle 查每张表有多少条记录
SELECT table_name,num_rows FROM user_tables order by num_rows desc;