v$tempfile与v$sort_usage之关系解析

在前面的V$TEMPSEG_USAGE与Oracle排序中我谈到V$TEMPSEG_USAGE和V$SORT_USAGE同源,其中的SEGFILE#代表的是绝对文件号(AFN).

那么对于临时表空间的临时文件来说,这个字段可以和什么字段进行关联呢?

我们再来看一下V$TEMPFILE的来源,V$TEMPFILE由如下语句创建:

SELECT tf.inst_id, tf.tfnum, TO_NUMBER (tf.tfcrc_scn),
       TO_DATE (tf.tfcrc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
       tf.tftsn, tf.tfrfn,
       DECODE (BITAND (tf.tfsta, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),
       DECODE (BITAND (tf.tfsta, 12),
               0, 'DISABLED',
               4, 'READ ONLY',
               12, 'READ WRITE',
               'UNKNOWN'
              ),
       fh.fhtmpfsz * tf.tfbsz, fh.fhtmpfsz, tf.tfcsz * tf.tfbsz, tf.tfbsz,
       fn.fnnam
  FROM x$kcctf tf, x$kccfn fn, x$kcvfhtmp fh
 WHERE fn.fnfno = tf.tfnum
   AND fn.fnfno = fh.htmpxfil
   AND tf.tffnh = fn.fnnum
   AND tf.tfdup != 0
   AND fn.fntyp = 7
   AND fn.fnnam IS NOT NULL

考察x$kcctf底层表,我们注意到TFAFN(temp file absolute file number)在这里存在:

SQL> desc x$kcctf
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ADDR                                   RAW(4)
 INDX                                   NUMBER
 INST_ID                                NUMBER
 TFNUM                                  NUMBER
 TFAFN                                  NUMBER
 TFCSZ                                  NUMBER
 TFBSZ                                  NUMBER
 TFSTA                                  NUMBER
 TFCRC_SCN                              VARCHAR2(16)
 TFCRC_TIM                              VARCHAR2(20)
 TFFNH                                  NUMBER
 TFFNT                                  NUMBER
 TFDUP                                  NUMBER
 TFTSN                                  NUMBER
 TFTSI                                  NUMBER
 TFRFN                                  NUMBER
 TFPFT                                  NUMBER

而这个字段在构建v$tempfile时并未出现,所以我们不能通过v$sort_usage和v$tempfile直接关联绝对文件号.

通过LOB对象与临时段一文中方法我们可以简单构建一个排序段使用,然后来研究一下: 

SQL> select username,segtype,segfile#,segblk#,extents,segrfno#
  2  from v$sort_usage;
USERNAME SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS   SEGRFNO#
-------- --------- ---------- ---------- ---------- ----------
SYS      LOB_DATA           9      18953          1          1

我们看到这里的SEGFILE#=9,而在v$tempfile是找不到这个信息的:

SQL> select file#,rfile#,ts#,status,blocks
  2  from v$tempfile;
     FILE#     RFILE#        TS# STATUS      BLOCKS
---------- ---------- ---------- ------- ----------
         1          1          2 ONLINE       38400

我们可以从x$kcctf中获得这些信息,我们可以看到v$tempfile.file#实际上来自x$kcctf.tfnum,实际上是临时文件的顺序号,而绝对文件号是x$kcctf.tfafn,这个才可以和v$sort_usage.segfile#关联: 

SQL> select indx,tfnum,tfafn,tfcsz       
  2  from x$kcctf;
      INDX      TFNUM      TFAFN      TFCSZ
---------- ---------- ---------- ----------
         0          1          9      38400
         1          2         10      12800

临时表空间的绝对文件号可以通过如下查询获得:

 

SQL> select tm.file# Fnum ,tf.tfafn AFN,tm.name FName
  2  from v$tempfile tm,x$kcctf tf
  3  where tm.file# = tf.tfnum;
      FNUM        AFN FNAME
---------- ---------- --------------------------------------------
         1          9 /opt/oracle/oradata/conner/temp1.dbf
         4         12 /opt/oracle/oradata/conner/temp2.dbf
 

至于其他就不再赘述。 

import os import psutil import platform import json import sqlite3 import logging import time from datetime import datetime from pathlib import Path logger = logging.getLogger('EnvironmentInterface') class EnvironmentInterface: def __init__(self, env_manager): self.env_manager = env_manager def request_vip_access(self, agent_id, resource_type): """请求VIP通道访问权限""" return self.env_manager.vip_system.request_access( agent_id=agent_id, resource=resource_type ) def release_vip_resource(self, agent_id, resource_type): """释放VIP资源""" self.env_manager.vip_system.release_resource( agent_id=agent_id, resource=resource_type ) def get_vip_status(self): """获取VIP通道状态""" return self.env_manager.vip_system.get_system_status() class EnvironmentInterface: def __init__(self, base_dir: str, coordinator=None): """初始化环境接口 Args: base_dir: 系统基础目录 coordinator: 意识系统协调器(可选) """ self.coordinator = coordinator # 配置日志 self.logger = logging.getLogger('EnvironmentInterface') self.logger.setLevel(logging.INFO) formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') # 控制台日志 console_handler = logging.StreamHandler() console_handler.setFormatter(formatter) self.logger.addHandler(console_handler) self.logger.propagate = False # 工作区路径设置 self.workspace_root = self._resolve_workspace_path(base_dir) # 创建标准目录结构 self.models_dir = self._resolve_and_create_dir("01_模型存储") self.cache_dir = self._resolve_and_create_dir("01_模型存储/下载缓存") self.system_dir = self._resolve_and_create_dir("02_核心代码") self.temp_dir = self._resolve_and_create_dir("04_环境工具/临时补丁") self.python_dir = self._resolve_and_create_dir("04_环境工具/Python环境") # 环境配置 - 跨平台兼容 path_sep = os.pathsep os.environ['PATH'] = f"{self.python_dir}{path_sep}{os.environ['PATH']}" if platform.system() == "Windows": os.environ['PATH'] += f"{path_sep}{self.python_dir}\\Scripts" os.environ['HF_HOME'] = self.cache_dir # 安全策略 self.authorized_actions = { "file_access": True, "web_search": True, "command_exec": True, "software_install": False, "hardware_control": False } self.action_log = [] # 初始化数据库 self.environment_db = os.path.join(self.system_dir, 'environment.db') self._init_db() self.logger.info("✅ 环境接口初始化完成") def _resolve_workspace_path(self, base_dir: str) -> str: """解析工作区路径为绝对路径""" try: base_path = Path(base_dir).resolve() workspace_path = base_path / "AI_Workspace" if not workspace_path.exists(): workspace_path.mkdir(parents=True, exist_ok=True) self.logger.info(f"创建工作区目录: {workspace_path}") return str(workspace_path) except Exception as e: self.logger.error(f"工作区路径解析失败: {str(e)}") # 跨平台回退路径 fallback_path = Path.home() / "AI_Workspace" fallback_path.mkdir(parents=True, exist_ok=True) return str(fallback_path) def _resolve_and_create_dir(self, relative_path: str) -> str: """解析路径并确保目录存在""" try: full_path = Path(self.workspace_root) / relative_path full_path.mkdir(parents=True, exist_ok=True) self.logger.info(f"创建/确认目录: {full_path}") return str(full_path) except Exception as e: self.logger.error(f"目录解析失败: {relative_path} - {str(e)}") # 创建临时目录作为回退 temp_path = Path(self.workspace_root) / "temp" temp_path.mkdir(parents=True, exist_ok=True) return str(temp_path) def _init_db(self): """初始化环境数据库""" max_retries = 3 for attempt in range(max_retries): try: with sqlite3.connect(self.environment_db) as conn: cursor = conn.cursor() # 系统信息表 cursor.execute('''CREATE TABLE IF NOT EXISTS system_info ( id INTEGER PRIMARY KEY, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, os TEXT, cpu TEXT, memory REAL, disk_usage REAL )''') # 文件探索历史表 cursor.execute('''CREATE TABLE IF NOT EXISTS file_exploration ( id INTEGER PRIMARY KEY, path TEXT UNIQUE, last_visited DATETIME, visit_count INTEGER DEFAULT 0 )''') # 资源管理表 cursor.execute('''CREATE TABLE IF NOT EXISTS resources ( id INTEGER PRIMARY KEY, name TEXT UNIQUE, type TEXT CHECK(type IN ('skin', 'furniture', 'tool')), path TEXT, is_active BOOLEAN DEFAULT 0 )''') # 添加索引 cursor.execute("CREATE INDEX IF NOT EXISTS idx_resources_type ON resources(type)") cursor.execute("CREATE INDEX IF NOT EXISTS idx_file_exploration_path ON file_exploration(path)") conn.commit() self.logger.info(f"✅ 数据库初始化完成: {self.environment_db}") return except (sqlite3.OperationalError, sqlite3.DatabaseError) as e: self.logger.error(f"❌ 数据库初始化失败 (尝试 {attempt + 1}/{max_retries}): {str(e)}") time.sleep(1) # 等待后重试 self.logger.error("❌ 数据库初始化最终失败,环境功能可能受限") # 系统监控功能 def get_system_info(self) -> dict: """获取并记录系统信息""" try: mem = psutil.virtual_memory() mem_used = round(mem.used / (1024 ** 3), 1) mem_total = round(mem.total / (1024 ** 3), 1) disk_usage = psutil.disk_usage('/').percent info = { "os": f"{platform.system()} {platform.release()}", "cpu": f"{platform.processor()} ({psutil.cpu_count(logical=False)} cores)", "memory": f"{mem_used}GB/{mem_total}GB ({mem.percent}%)", "disk_usage": f"{disk_usage}%" } # 保存到数据库 with sqlite3.connect(self.environment_db) as conn: cursor = conn.cursor() cursor.execute('''INSERT INTO system_info (os, cpu, memory, disk_usage) VALUES (?, ?, ?, ?)''', (info['os'], info['cpu'], mem_used, disk_usage)) conn.commit() self.log_action("system_monitor", "采集系统信息") return info except Exception as e: self.logger.error(f"❌ 获取系统信息失败: {str(e)}", exc_info=True) return { "error": "获取系统信息失败", "details": str(e) } # 文件探索功能 def explore_directory(self, path: str = None) -> dict: """探索目录内容""" try: target_path = Path(path) if path else Path(self.workspace_root) target_path = target_path.resolve() # 安全路径检查 workspace_path = Path(self.workspace_root).resolve() if not target_path.is_relative_to(workspace_path): return {"error": "访问路径超出工作区范围"} if not target_path.exists(): return {"error": "路径不存在"} # 记录探索历史 self._record_exploration(str(target_path)) contents = [] for item in target_path.iterdir(): try: item_info = { "name": item.name, "path": str(item), "type": "directory" if item.is_dir() else "file", "modified": datetime.fromtimestamp(item.stat().st_mtime).strftime("%Y-%m-%d %H:%M") } if item.is_file(): size = item.stat().st_size item_info["size"] = self._format_size(size) contents.append(item_info) except PermissionError: contents.append({ "name": item.name, "error": "权限不足" }) except Exception as e: contents.append({ "name": item.name, "error": str(e) }) # 排序:目录在前,按名称排序 contents.sort(key=lambda x: (0 if x.get('type') == 'directory' else 1, x['name'])) self.log_action("file_explore", f"探索路径: {target_path}") return { "current_path": str(target_path), "contents": contents } except Exception as e: self.logger.error(f"❌ 探索目录失败: {str(e)}", exc_info=True) return {"error": str(e)} def _format_size(self, size_bytes: int) -> str: """格式化文件大小""" if size_bytes < 1024: return f"{size_bytes} B" elif size_bytes < 1024 ** 2: return f"{size_bytes / 1024:.1f} KB" elif size_bytes < 1024 ** 3: return f"{size_bytes / (1024 ** 2):.1f} MB" else: return f"{size_bytes / (1024 ** 3):.1f} GB" def _record_exploration(self, path: str): """记录探索历史到数据库""" try: with sqlite3.connect(self.environment_db) as conn: cursor = conn.cursor() cursor.execute("SELECT 1 FROM file_exploration WHERE path = ?", (path,)) if cursor.fetchone(): cursor.execute('''UPDATE file_exploration SET last_visited = CURRENT_TIMESTAMP, visit_count = visit_count + 1 WHERE path = ?''', (path,)) else: cursor.execute('''INSERT INTO file_exploration (path, last_visited, visit_count) VALUES (?, CURRENT_TIMESTAMP, 1)''', (path,)) conn.commit() except Exception as e: self.logger.error(f"❌ 记录探索历史失败: {str(e)}") # 资源管理功能 def get_resources(self, resource_type: str = None) -> list: """获取资源列表(可过滤类型)""" try: with sqlite3.connect(self.environment_db) as conn: cursor = conn.cursor() if resource_type: cursor.execute('''SELECT id, name, type, path, is_active FROM resources WHERE type = ?''', (resource_type,)) else: cursor.execute('''SELECT id, name, type, path, is_active FROM resources''') return [{ "id": row[0], "name": row[1], "type": row[2], "path": row[3], "is_active": bool(row[4]) } for row in cursor.fetchall()] except Exception as e: self.logger.error(f"❌ 获取资源失败: {str(e)}") return [] def activate_resource(self, resource_id: int) -> bool: """激活特定资源""" try: with sqlite3.connect(self.environment_db) as conn: cursor = conn.cursor() # 获取资源信息 cursor.execute('''SELECT type FROM resources WHERE id = ?''', (resource_id,)) resource = cursor.fetchone() if not resource: self.logger.warning(f"资源ID不存在: {resource_id}") return False resource_type = resource[0] # 禁用同类型所有资源 cursor.execute('''UPDATE resources SET is_active = 0 WHERE type = ?''', (resource_type,)) # 激活指定资源 cursor.execute('''UPDATE resources SET is_active = 1 WHERE id = ?''', (resource_id,)) conn.commit() # 记录日志 cursor.execute('''SELECT name FROM resources WHERE id = ?''', (resource_id,)) resource_name = cursor.fetchone()[0] self.log_action("resource_activate", f"激活资源: {resource_name} ({resource_type})") # 通知协调器 if self.coordinator: self.coordinator.notify_resource_change(resource_type, resource_name) return True except Exception as e: self.logger.error(f"❌ 激活资源失败: {str(e)}") return False # 工作区管理功能 def get_workspace_info(self) -> dict: """获取工作区信息""" return { "workspace_root": self.workspace_root, "models_dir": self.models_dir, "cache_dir": self.cache_dir, "system_dir": self.system_dir, "temp_dir": self.temp_dir, "python_dir": self.python_dir } # 辅助功能 def is_authorized(self, action: str) -> bool: """检查操作授权状态""" return self.authorized_actions.get(action, False) def log_action(self, action: str, details: str) -> bool: """记录环境操作日志""" log_entry = { "timestamp": datetime.now().isoformat(), "action": action, "details": details } self.action_log.append(log_entry) self.logger.info(f"{action}: {details}") return True def process_environment_change(self, change: dict): """处理环境变化(如果协调器存在)""" if self.coordinator and hasattr(self.coordinator, 'process_stimulus'): stimulus = self._create_stimulus_from_change(change) self.coordinator.process_stimulus(stimulus) def _create_stimulus_from_change(self, change: dict) -> dict: """将环境变化转化为刺激""" return { "type": "environment_change", "source": "environment_interface", "timestamp": datetime.now().isoformat(), "data": change } # 使用示例 if __name__ == "__main__": # 配置日志 logging.basicConfig( level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s' ) # 创建环境接口实例 current_dir = os.path.dirname(os.path.abspath(__file__)) base_dir = os.path.dirname(os.path.dirname(current_dir)) env = EnvironmentInterface(base_dir) # 获取工作区信息 print("工作区信息:") print(json.dumps(env.get_workspace_info(), indent=2, ensure_ascii=False)) # 获取系统信息 print("\n系统信息:") print(json.dumps(env.get_system_info(), indent=2, ensure_ascii=False)) # 探索目录 print("\n工作区内容:") print(json.dumps(env.explore_directory(), indent=2, ensure_ascii=False))
最新发布
08-11
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值