lsnrctl status sqlplus / as sysdba STARTUP shutdown immediate --查看版本 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_NAMEusers.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 DIRECTORYexpdp_dir DUMPFILEkefu_20251015.dmp LOGFILEkefu.log SCHEMASsxsl,icd transformsegment_attributes:n; --验证 sqlplus new_user/passworddatabase