Review usage and deploy the agent

Sessions and memory

By the end of this lesson, you can read session usage and deploy the agent configuration to another schema.

Lesson 7 of 7~20 minScript: 09_promote.sql

1. Lesson 1: Set up the demo and call a model
2. Lesson 2: Create a prompt profile and agent
3. Lesson 3: Add tools that read contract data
4. Lesson 4: Read execution logs and diagnose errors
5. Lesson 5: Add a tool that creates credit notes
6. Lesson 6: Sessions and memory
7. Lesson 7: Review usage and deploy the agent

Session activity and deployment

This lesson reads session activity and token usage from your demo schema. It then introduces the APEX chat interface and the script for deploying the agent configuration to another schema. Run 09_promote.sql only at the deployment step.

Review session activity

uc_ai_agent_sessions holds one row for each conversation. Run this query in your original demo schema:

select s.turn_count
     , s.message_count
     , s.total_input_tokens
     , s.total_output_tokens
     , s.created_by
     , s.audience
     , nvl(s.title, '(no title)')       as title
     , nvl(s.feedback_rating, '-')      as rating
  from uc_ai_agent_sessions s
 where s.root_agent_id = ( select id from uc_ai_agents where code = 'SC_DESK' )
 order by s.started_at desc
 fetch first 5 rows only;

Selected columns from recorded sessions:

TURN_COUNT  MESSAGE_COUNT  TOTAL_INPUT_TOKENS  TOTAL_OUTPUT_TOKENS  CREATED_BY  AUDIENCE
         1              0                   0                    0  UC_AI       db
         1              8                 657                   76  UC_AI       db
         1              4                 549                   38  UC_AI       db

The following columns help explain the session records:

• audience separates a signed-in APEX user from an anonymous visitor from a database session. A username cannot do that, because every anonymous visitor of an app shares one APEX public user. The rows above say db, because they were started from SQLcl and from a scheduler job.
• created_by records the APEX user when one is available, or the database user otherwise.
• message_count is 0 on the first row. That run failed. A failed run has no result object from which to calculate the session totals. Message records can still exist. A failed execution with tool_calls_count = 0 can contain tool calls in uc_ai_agent_messages. Use the queries from lesson 4 to inspect them.

Read token usage

UC AI records input and output token counts. Run this query to group today’s usage by caller across all agents in the schema:

select s.created_by
     , sum(s.total_input_tokens)  as input_tokens
     , sum(s.total_output_tokens) as output_tokens
  from uc_ai_agent_sessions s
 where s.started_at >= trunc(sysdate)
 group by s.created_by
 order by 2 desc;

One recorded run used 657 input and 76 output tokens. Usage varies with prompts, conversation history, tool results, and model behavior. To estimate charges, apply the provider’s input and output rates separately.

Budgets and spend limits

Per-model pricing, budgets and spend enforcement are part of Guardrails. This lesson stays with what core records: token counts.

Add a session title and feedback

These calls add a title and feedback to a session. Set :session_id to an existing session ID as described in lesson 4, then run the block:

begin
  uc_ai_agents_api.set_session_title(
    p_session_id => :session_id
  , p_title      => 'Credit note for INV-1001'
  );

  uc_ai_agents_api.set_session_feedback(
    p_session_id => :session_id
  , p_rating     => 'down'
  , p_comment    => 'Quoted the wrong coverage window.'
  );
end;
/

The application supplies titles and feedback. Reports can use negative ratings to identify conversations that need review.

Add an APEX chat interface

The APEX Chat plug-in provides a chat region for the agent. It stores the question, runs the agent in a background job, and polls for the answer. This keeps the interface available while the provider processes the request. Three attributes connect the region to this example:

• Agent Code names the agent: SC_DESK.
• Agent Input Mapping fills the three placeholders of the profile, with &APP_USER. for the engineer, a page item for the date, and #USER_MESSAGE# for what the engineer typed.
• Agent Run Context carries the contract the page is on, so the tools of lesson 3 stay bound to one record and the memory of lesson 6 stays keyed on it.

The plug-in writes every message to uc_ai_chat_messages, beside the execution rows this lesson reads.

Put an Agent in APEX builds that region on this desk, in six lessons. It covers what the plug-in does about a conversation another user must not read, a page that moves to another contract mid-conversation, and an audit trail that names the person who asked. The APEX Chat plug-in guide lists every region attribute.

Deploy the agent configuration

The prompt profile, tool registrations, and agent configuration are database rows. Include a script that creates or updates these rows alongside your PL/SQL source files in version control.

To deploy this demo to another schema:

1. Install UC AI in the target environment and configure provider access as in lesson 1.
2. Connect SQLcl to a separate demo schema in the target environment.
3. 00_setup.sql deletes existing demo data in the target schema. Run @00_setup.sql to create and populate the demo tables.
4. Run @sc_desk_pkg.pks. Then run @sc_desk_pkg.pkb. Use show errors after each file to inspect compilation errors.
5. Run @00_precheck.sql. Correct any reported failure before continuing.
6. Run @09_promote.sql to deploy the agent configuration.

For a real application, deploy its tables and adapted handlers before running the configuration script. The demo handlers depend on the sc_* tables; unrelated application tables do not satisfy that dependency.

The promotion script performs these operations:

1. Tools with merge_tool_from_schema, which is create-or-replace for a tool.

2. The profile, created when absent and updated when present, at an explicit version.

3. The agent, created when absent and left alone when present.

4. Memory, then the two activations. enable_for_agent writes the memory tag into the profile version the agent resolves to now, so it runs after the profile exists.

Deploy or manage these objects separately:

Not deployed	Why
The demo tables and handler package	Deploy these before the configuration script
The run history	The history of a development schema is not production data
The memory stores	Development memory can contain test data

A release log ends with this:

OBJECT    DETAIL
tools     4
profile   SC_DESK_PROFILE v1 active
agent     SC_DESK v1 active
memory    context on contract_id

Archive or delete the agent

The following call fragments show the two operations. Run only the operation you need, inside a PL/SQL block:

-- Stop it answering. Keeps every row, and is reversible.
uc_ai_agents_api.change_status('SC_DESK', 1, uc_ai_agents_api.c_status_archived);

-- Remove it and everything that belongs only to it.
uc_ai_agents_api.purge_agent(p_code => 'SC_DESK');

Archiving version 1 makes that version unavailable to calls that select an active version. If other active versions exist, archive those as well to stop new calls by agent code. This does not cancel a run already in progress.

purge_agent removes every version of the agent, its executions, its sessions, their messages, and the memory stores that belong to it alone. It keeps what other things also use: a shared or global memory store, a session another agent opened, and the prompt profile — a profile lives without an agent, and another agent can use it. It also keeps a tool whose PL/SQL text names the agent, because a tool only mentions it in a string.

purge_agent deletes an audit trail

purge_agent removes run history. Decide whether you are allowed to do that before you run it in production. If you only want the agent to stop, archive it.

Verification

Connect to your original demo schema, then run this query:

select 'sessions' as what, to_char(count(*)) as detail
  from uc_ai_agent_sessions
 where root_agent_id = ( select id from uc_ai_agents where code = 'SC_DESK' )
union all
select 'tokens today'
     , to_char(nvl(sum(total_input_tokens + total_output_tokens), 0))
  from uc_ai_agent_sessions
 where started_at >= trunc(sysdate);

sessions counts conversations for SC_DESK. tokens today totals usage across all agents in that schema. A newly deployed schema has no conversation history yet. Use the earlier query with separate input and output counts to estimate provider charges.

Key takeaways

• Apply the provider’s input and output rates separately to estimate charges from recorded token counts.
• Deploy tables and handler packages before the agent configuration. Keep the configuration script in version control.
• uc_ai_agents_api.purge_agent(p_code => 'SC_DESK'); removes the agent and its history. change_status(..., c_status_archived) is the reversible one.

Full reference: Conversation sessions has every session column, and the APEX Chat plug-in has every region attribute.

Where to go next

• Analyze Data at Scale — the next course. The model writes a JavaScript program that runs inside the database and calls your tools, so 26 tool calls become one. The code-mode guide is the reference behind it.
• Secure an Agent — the same desk, with data an outsider wrote in it. One supplier email redirects a payment. The tutorial adds controls and tests them against the example.
• Put an Agent in APEX — this desk, as a chat region on a page, in six lessons.
• Extract structured data from a PDF — a response schema, and the five checks that decide whether you can trust what came back.
• Multi-agent systems — hand off to a specialist, or let one agent use another as a tool.
• File analysis — attach the scanned service report to the question.
• Guardrails — budgets, spend limits and rate limiting.