---------------- ALTER DATAFILE
-- see ALTER DATABASE DATAFILE
-- add a data file - see alter tablespace


---------------- ALTER INDEX
-- alter storage definition for an index
ALTER INDEX ADMIN.IX1 STORAGE ( NEXT 2M PCTINCREASE 20);

-- rebuild an index
alter index ADMIN.IX1
rebuild
tablespace IX_TS
storage (initial 25153536 next 5033984 maxextents unlimited)
unrecoverable;


---------------- ALTER DATABASE
-- change the global name of a database
alter database rename global_name to ;
alter database rename global_name to DEVDB.WORLD;

-- to query the global_name
select * from global_name;

-- set autoextend on for datafile with maxsize unlimited
ALTER DATABASE DATAFILE '/db02/oradata/instance/instance_file.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

-- set autoextend on for datafile and next extent and maxsize
alter database datafile '/db02/oradata/DBSTAR/DBSTAR_IX_TS01.dbf'
      autoextend on next 100M maxsize 2000M;

-- set autoextend off for a datafile
ALTER DATABASE DATAFILE '/db02/oradata/instance/instance_file.dbf' AUTOEXTEND OFF;

-- resize a datafile
alter database datafile '/db02/oradata/instance/instance_file.dbf' resize 50000K;
ALTER DATABASE DATAFILE '/db11/oradata/instance/instance_file.dbf' RESIZE 1K;


---------------- ALTER package or procedure
alter PACKAGE ADMIN.PK_CWT_BAK compile;
alter PACKAGE ADMIN.PK_CNV_BANKS compile BODY;
alter procedure ADMIN.P_client compile;


---------------- ALTER SESSION
-- To start tracing for a session
alter session set sql_trace = true;

-- To stop tracing for a session
alter session set sql_trace = false;


---------------- ALTER SYSTEM
-- kill a session
alter system kill session 'SID,SERIAL#';

-- enable/disable restrict mode
alter system disable restricted session;
alter system enable restricted session;

-- flush the shared pool
alter system flush shared_pool;

-------
-- alter system set commands

-- change (enable/disable) the check for exceeded resources
alter system set resource_limit = true;
alter system set resource_limit = false;

-- change the system sort_area_size dynamically
alter system set sort_area_size = 1572864 deferred;

-- set timed_statistics to false/true
alter system set timed_statistics to false;

-- set the maximum dump file size (in kilobytes)
alter system set max_dump_file_size = '10000';


---------------- ALTER TABLESPACE
-- change default max extents of tablespace
ALTER TABLESPACE "DB_CUSTOMER_RECORD_DATA" DEFAULT STORAGE ( INITIAL 100K NEXT 100K MAXEXTENTS 10000 PCTINCREASE 20 );

-- add a datafile to a tablespace
ALTER TABLESPACE USER_DATA
   ADD DATAFILE 'D:\ORADATA\DBSTAR\DBSTAR_USR02.DBF' size 100M
   AUTOEXTEND ON NEXT 30M MAXSIZE 1000M;

ALTER TABLESPACE CNV_DATA
   ADD DATAFILE '/db13/oradata/DBSTAR/DBSTAR_cnv_data02.dbf' size 500M
   AUTOEXTEND ON NEXT 100M MAXSIZE 2000M;

alter database datafile 'E:\ORADATA\DBSTAR\DBSTAR_TEMP01.ORA' autoextend on maxsize 1024M;

---------------- ALTER USER
-- lock account of a user
alter user codetables account lock;
alter user ADMIN QUOTA 10M ON cases_ts;

-- by default the quota is set to none