oracle基础笔记

1、plsql中文乱码

--查看语言,plsql中文乱码
select * from V$NLS_PARAMETERS;
select userenv(‘language‘) from dual;
新建环境变量: NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

2、控制台管理员登录

sqlplus /nolog
connect /as sysdba

3、创建表空间,用户,目录

create tablespace mytbs datafile ‘D:\databases\tablespaces\mytbs.dbf‘ size 50m autoextend on next 32m maxsize unlimited extent management local logging online blocksize 8k autoallocate segment space management auto;
create user mytbs identified by mytbs default tablespace mytbs; --创建角色
grant connect,resource,dba to mytbs; --设置权限
create or replace directory dbdir as ‘D:\databases\backups‘; --创建目录
grant read, write on directory dbdir to mytbs;

4、删除用户和表空间

drop user mytbs cascade;--删除用户，及级联关系也删除掉
--alter tablespace mytbs offline;--删除表空间，及对应的表空间文件也删除掉
drop tablespace mytbs including contents and datafiles cascade constraint;

5、imp/exp

--imp导入
imp mytbs/mytbs@localhost:1521/orcl file=D:\databases\backups\mytbs.dmp log=D:\databases\backups\mytbs.log fromuser=mytbs touser=mytbs tablespaces=mytbs ignore=y;
--exp导出
exp mytbs/mytbs@localhost:1521/orcl file=D:\databases\backups\mytbs.dmp log=D:\databases\backups\mytbs.log owner=mytbs grants=y;

6、expdp/impdp

--expdp导出[语句末尾不能带分号]
expdp amarabs/amarabs@localhost:1521/orcl directory=DBDIR dumpfile=abscj_20181215expdp.dmp logfile=abscj_20181215expdp.log schemas=AMARABS
--impdp导出[相同用户]
impdp amarabs/amarabs@localhost:1521/orcl directory=DBDIR dumpfile=abscj_20181215expdp.dmp schemas=AMARABS;
--impdp导出[不同用户:旧/新]
impdp amarabs/amarabs@localhost:1521/orcl directory=DBDIR dumpfile=abscj_20181215expdp.dmp remap_schema=amarabs:abscj remap_tablespace=amarabs:abscj;

7、修改表的表空间、修改索引的表空间

--修改用户表的表空间
select ‘alter table AMARABS.‘||table_name||‘ move tablespace AMARABS;‘ from user_tables;
--alter table aa move tablespace amarabs;--修改表空间

--修改用户索引的表空间
select ‘alter index ALS757.‘||index_name||‘ rebuild tablespace ALS757;‘ from dba_indexes where table_owner=‘ALS757‘ and index_type <> ‘LOB‘ and tablespace_name <>‘ALS757‘ order by table_name;
-- alter table tb_name move tablespace tbs_name;
-- alter index index_name rebuild tablespace tbs_name;

select ‘alter table ALS757.‘||table_name||‘ move tablespace ALS757;‘ from dba_tables where OWNER=‘ALS757‘ and TABLESPACE_NAME <> ‘ALS757‘;
select distinct table_name,‘select * from ALS757.‘||table_name||‘;‘ from dba_tables where OWNER=‘ALS757‘ and TABLESPACE_NAME <> ‘ALS757‘;
select ‘alter index ALS757.‘||index_name||‘ rebuild tablespace ALS757;‘ from dba_indexes where table_owner=‘ALS757‘ and index_type <> ‘LOB‘ and tablespace_name <>‘ALS757‘ order by table_name;
select distinct table_name,‘select * from ‘||table_owner||‘.‘||table_name||‘;‘ from dba_indexes where owner=‘ALS757‘ and TABLESPACE_NAME <> ‘ALS757‘;

8、查看数据库的信息

--表空间的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;

--表空间使用
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;

--查看数据库库对象(table,index,view,function,sequence等)
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;

--查看数据库的版本　
SELECT version FROM product_component_version WHERE substr(product, 1, 6) = ‘Oracle‘;

oracle基础笔记

原文：https://www.cnblogs.com/cuminbf/p/10262332.html