Oracle常用的 sql语句

annoyance404

已于 2023-12-20 09:01:39 修改

阅读量48

点赞数 1

文章标签：数据库 oracle

于 2023-12-20 08:53:15 首次发布

本文链接：https://blog.csdn.net/Best_wind/article/details/135099034

版权

---创建表空间SQL：

create tablespace xxx
datafile 'xxx/datafile/xxx.DBF' size 200M
autoextend on next 40M maxsize unlimited
default storage(initial 128k next 128k minextents 2 maxextents unlimited);

---创建临时表空间SQL：

create temporary tablespace xxx
tempfile 'xxx/datafile/xxx.dbf ' size 100M
autoextend on next 20m maxsize unlimited;

---查询表空间和临时表空间

SELECT d.tablespace_name "Name", d.status "Status",
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.90') "Size (M)",
TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024,
'99999999.99'
) USE,
TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0),
'990.00'
) "Used %"
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)

----查询表空间文件位置

SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;

----增加表空间

alter tablespace NHIP_DC
add datafile '+DATA/PTORCL/TABLESPACE/NHIP_DC1.dbf' size 30g autoextend on ;

---修改表空间和临时表空间

Alter user test1 default tablespace tbs_test1; --修改 test1用户的默认表空间是tbs_test1
Alter user ptjob temporary tablespace ptjob_temp; --修改 test1用户的临时表空间是temp_tbs_test1

---创建用户

-- Create the user
create user YKT_ODS
identified by YKT_ODS
default tablespace YKTODS
temporary tablespace YKTODS_TEMP
profile DEFAULT
quota unlimited on users;
-- Grant/Revoke role privileges
grant connect to YKT_ODS;
grant exp_full_database to YKT_ODS;
grant imp_full_database to YKT_ODS;
grant resource to YKT_ODS;
-- Grant/Revoke system privileges
grant create any job to YKT_ODS;
grant create any sequence to YKT_ODS;
grant create any synonym to YKT_ODS;
grant create any table to YKT_ODS;
grant create any trigger to YKT_ODS;
grant create any view to YKT_ODS;
grant create database link to YKT_ODS;
grant debug any procedure to YKT_ODS;
grant debug connect session to YKT_ODS;
grant insert any table to YKT_ODS;
grant select any table to YKT_ODS;
grant select any transaction to YKT_ODS;
grant unlimited tablespace to YKT_ODS;
grant update any table to YKT_ODS;

--查看用户信息

select * from dba_users;

---设置口令期限为永久

select username,account_status,expiry_date,profile from dba_users;
select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
alter profile default limit password_life_time unlimited;

---导入表结构

imp system/123456@127.0.0.1/orcl file= E:\ptjob.dmp fromuser=ptjob touser=ptjob ignore=y ;

imp nhip_empi/nhip_empi@172.29.241.3:1521/orcl file=e:/empi.dmp full=y