Oracle常用资料

--查看版本
SELECT * FROM v$version;
--查看字符集
--查看编码
SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
--查看实例
SELECT instance_name, host_name FROM v$instance;
--查看实例状态
SELECT status FROM v$instance;
--查看用户
SELECT username, default_tablespace, temporary_tablespace FROM dba_users;
--查看表空间
SELECT tablespace_name, status, initial_extent, next_extent, max_extents
FROM dba_tablespaces;

--查看用户及表空间
SELECT  FILE_NAME,TABLESPACE_NAME,username,user_id,account_status,default_tablespace,temporary_tablespace,created FROM DBA_DATA_FILES data join dba_users users on data.TABLESPACE_NAME=users.default_tablespace;


-- 6.查询表空间使用情况
SELECT a.tablespace_name "表空间名",
       total "表空间大小",
       free "表空间剩余大小",
       (total - free) "表占用空间大小",
       ROUND((total - free) / total * 100, 2) || '%' "已使用空间百分比"
  FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 free
          FROM dba_free_space
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name
 ORDER BY (total - free) DESC;

--创建用户
CREATE USER sxsl IDENTIFIED BY Sxsl#222102;
create user icd identified by icd;

CREATE TABLESPACE sxsl  DATAFILE 'tablespacesxsl.dbf' SIZE 6G AUTOEXTEND ON NEXT 100 MAXSIZE UNLIMITED;
CREATE TABLESPACE ICD_SERVER DATAFILE 'tablespaceICD.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
CREATE TABLESPACE ICD_SERVICE DATAFILE 'icdservice.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
CREATE TABLESPACE ICD_MAILSERVICE DATAFILE 'icdmailservice.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
CREATE TABLESPACE ICD_CHATSERVICE DATAFILE 'icdchatservice.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
CREATE TABLESPACE ICD_BILLLOG_CHARGE DATAFILE 'icdbilllogcherge.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
CREATE TABLESPACE ICD_BILLLOG1 DATAFILE 'icdbilllog1.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
CREATE TABLESPACE ICD_BILLLOG2 DATAFILE 'icdbilllog2.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
CREATE TABLESPACE ICD_BILLLOG3 DATAFILE 'icdbilllog3.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
CREATE TABLESPACE ICD_BILLLOG4 DATAFILE 'icdbilllog4.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;


-- 分配默认表空间和临时表空间
ALTER USER sxsl DEFAULT TABLESPACE sxsl;
ALTER USER sxsl TEMPORARY TABLESPACE temp;
ALTER USER icd DEFAULT TABLESPACE icd_server;
ALTER USER icd TEMPORARY TABLESPACE temp;

-- 授予权限，例如连接和资源等
GRANT CONNECT, RESOURCE,dba TO sxsl;
GRANT CONNECT, RESOURCE,dba TO icd;


--创建directory目录
CREATE DIRECTORY expdp_dir AS '/home/oracle/directory';

--查询directory
SELECT directory_name, directory_path FROM dba_directories;
--删除directory目录
 drop directory dir_name;


ALTER USER system IDENTIFIED BY 123456;
--导入
impdp system/123456 DIRECTORY=expdp_dir DUMPFILE=kefu_20251015.dmp LOGFILE=kefu.log SCHEMAS=sxsl,icd     transform=segment_attributes:n;
--验证
sqlplus new_user/password@database