alter profile DEFAULT limit password_life_time unlimited;
http://nimishgarg.blogspot.com/2012/07/ora-28002-password-will-expire-within-7.html
-- show connected users
select sid, serial#, username, status, type, to_char( logon_time, 'dd.mm hh24:mi') logon_time, osuser, machine, program from v$session ;
select sid, serial#, username, status, type, to_char( logon_time, 'dd.mm hh24:mi') logon_time, osuser, machine, program from v$session where type='USER';
select sid, serial#, username, status, type, to_char( logon_time, 'dd.mm hh24:mi') logon_time, osuser, machine, program from v$session where type='USER' and status='ACTIVE';
-- list of all users
select username, account_status, lock_date, default_tablespace, temporary_tablespace from dba_users where account_status='OPEN' order by username;
select username from dba_users where account_status='OPEN' order by username;
-- unlock user
alter user system account unlock;
-- user tables
select TABLE_NAME from ALL_ALL_TABLES where owner='ASK';
select FULLNAME from ASK.USERS where rownum<=20;
-- invalid objects
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
-- user privileges
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('%&enter_username%')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
-- create user
create user andrey identified by password account unlock default tablespace users temporary tablespace temp;
grant create session, alter session to andrey;
-- export users
set linesize 256;
spool c:\oracleUsers.sql;
select 'create user '||username||' identified by values '||''''||password||''''||'
default tablespace '||default_tablespace||'
temporary tablespace '||temporary_tablespace||'
quota unlimited on '||default_tablespace||'
quota unlimited on '||temporary_tablespace||';' as "-- create_users"
from sys.dba_users
where default_tablespace not in('SYSAUX','SYSTEM')
order by username;
select concat(concat('alter user ',du.username),' account lock;') as "--lock_users"
from sys.dba_users du
where du.account_status<>'OPEN'
and du.default_tablespace not in('SYSAUX','SYSTEM')
order by du.username;
spool off;
-- used space
SELECT
owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10
ORDER BY SUM(bytes) desc;
select blocks, empty_blocks, num_freelist_blocks
from all_tables
where owner = 'SPACE'
and table_name = 'USERACTHISTORY';
set head off
set pages 0
set long 9999999
spool user_script.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS;
select 'alter user ' || username || ' identified by values ''' || password || ''';'
from dba_users;
spool off;
select table_name, blocks, avg_space, avg_row_len, num_rows from all_tables where owner='SPACEWALK' order by num_rows;
SELECT SUM(bytes)/1024/1024 "Meg" FROM dba_segments;
-- statistics & shrink
analyze table RHNCHECKSUM compute statistics;
select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='RHNCHECKSUM';
alter table SPACEWALK.RHNCHECKSUM enable row movement;
alter table SPACEWALK.RHNCHECKSUM shrink space compact;
ALTER INDEX … SHRINK SPACE COMPACT
ALTER INDEX … REBUILD