Variable for Database Link Name

I want to do v$session@remotedatabase where remotedatabase is a variable for a dblink address. Is that possible?

I'm using Apex 4 and trying to get temporary space on all databases.

Current query

select 'Total temp space available in :Database is '||sum(bytes)/1024/1024 ||' mb' from v$tempfile@:Database

yeilds ORA-01729: database link name expected because the variable isn't resolved correcltly?

I'm quite new to SQL, sorry

You would need to use dynamic SQL in order to have the database link be variable. In PL/SQL, that would look something like

DECLARE
  l_sql_stmt varchar2(1000);
  l_tmp_space varchar2(1000);
  l_database  varchar2(100) := <<db link name>>;
BEGIN
  l_sql_stmt := 'select ''Total temp space available in ' ||l_database  || ' is '' || sum(bytes)/1024/1024 ||'' mb'' from v$tempfile@' || l_database;
  dbms_output.put_line( l_sql_stmt );
  EXECUTE IMMEDIATE l_sql_stmt
    INTO l_tmp_space;
  dbms_output.put_line( l_tmp_space );
END;

Basically, you need to construct a string that contains the SQL statement and then execute that dynamically generated string (that's more complicated when the SQL statement itself is building up a string as yours is here-- that makes figuring out which single quotes to escape a bit of a challenge).

Depending on what you are trying to do in APEX, however, you may want something a little different. For example, if you are trying to create a report based on this, a regular (non-interactive) report can be populated either from a SQL statement or a function that returns a SQL statement. If that's what you're trying to do, you'd want something like

DECLARE
  l_sql_stmt varchar2(1000);
  l_tmp_space varchar2(1000);
  l_database  varchar2(100) := <<db link name>>;
BEGIN
  l_sql_stmt := 'select ''Total temp space available in ' ||l_database  || ' is '' || sum(bytes)/1024/1024 ||'' mb'' from v$tempfile@' || l_database;
  dbms_output.put_line( l_sql_stmt );
  RETURN l_sql_stmt;
END;