PLSQL command reference

To Make all Tablespace READ ONLY

begin
  for s in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP')) loop
    execute immediate 'alter tablespace  "'||s.tablespace_name||'" read only';
  end loop;
end;
/

Make all tablespace NOLOGGING

begin
  for s in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS01','UNDOTBS02','TEMP','TEMP_RRS') and LOGGING='LOGGING') loop
    execute immediate 'alter tablespace  "'||s.tablespace_name||'" NOLOGGING';
  end loop;
end;
/

To make all file autoextend on

begin
  for s in (select file_id from dba_data_files where autoextensible='NO') loop
    execute immediate 'alter database datafile '||s.file_id||' autoextend on';
  end loop;
end;
/