← All posts tagged Oracle

pc
pc
locks parus blocking Oracle select dbs.sid holding_sid, dbs.serial# holding_serial#, dbs.username holding_user,
(select count(sid) from v$session where blocking_session = dbs.sid) sessions_blocked,
dbs.sql_id holding_sql_id, dbs.wait_class holding_class, dbs.event holding_event, dbs.seconds_in_wait holding_secs,
dws.sid waiting_sid, dws.serial# waiting_serial#, dws.username waiting_user, dws.sql_id waiting_sql_id, dws.wait_class waiting_class, dws.event waiting_event, dws.seconds_in_wait waiting_secs
from v$session dbs, v$session dws
where dws.blocking_session = dbs.sid
order by dbs.sid, dws.seconds_in_wait desc
/
pc
pc
users parus SQL create Oracle *import
set pagesize 0
set linesize 200
spool import_users.sql
SELECT 'CREATE USER '||u.username||' PROFILE "DEFAULT"'
||' IDENTIFIED BY VALUES '''||PASSWORD||''''
||' DEFAULT TABLESPACE "PARUS" TEMPORARY TABLESPACE "PRS_TMP" ACCOUNT '
||(case
when u.account_status='OPEN' then 'UNLOCK'
when u.account_status='LOCKED' then 'LOCK'
end)
||chr(13)||chr(10)||'/'||chr(13)||chr(10)
||'GRANT "CONNECT" TO "'||username||'"'
||chr(13)||chr(10)||'/'||chr(13)||chr(10)
||(case
when u.account_status='OPEN' then 'ALTER USER '||username||' ACCOUNT UNLOCK'
when u.account_status='LOCKED' then 'ALTER USER '||username||' ACCOUNT LOCK'
end)
||chr(13)||chr(10)||'/'||chr(13)||chr(10)
||'ALTER USER '||username
||' IDENTIFIED BY VALUES '''||PASSWORD||''''
||chr(13)||chr(10)||'/'||chr(13)||chr(10) "SQL"
FROM dba_users u
WHERE username NOT IN ('OUTLN','XDB','ANONYMOUS','DBSNMP','ORDPLUGINS','PARUS')
and username NOT LIKE '%SYS%'
union
select 'exit' "--"
from dual
order by 1
;
exit
pc
pc
processes Oracle SQL> startup nomount
ORACLE instance started.

Total System Global Area 168430948 bytes
Fixed Size 453988 bytes
Variable Size 83886080 bytes
Database Buffers 83886080 bytes
Redo Buffers 204800 bytes
SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
job_queue_processes integer 0
log_archive_max_processes integer 1
processes integer 30
SQL> alter system set processes=5000 scope=spfile;

System altered.

SQL> startup nomount force
ORACLE instance started.

Total System Global Area 285871740 bytes
Fixed Size 454268 bytes
Variable Size 201326592 bytes
Database Buffers 83886080 bytes
Redo Buffers 204800 bytes
SQL> show parameter db_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 64
SQL> alter system set db_files=5000 scope=spfile;

System altered.

SQL> startup nomount force
ORACLE instance started.

Total System Global Area 780800796 bytes
Fixed Size 455452 bytes
Variable Size 696254464 bytes
Database Buffers 83886080 bytes
Redo Buffers 204800 bytes
SQL>
pc
pc
10.2.0.5 ora Oracle patch STARTUP UPGRADE
@ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlu102i.sql


STARTUP UPGRADE
SPOOL patch.log
@ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql
SPOOL OFF

SHUTDOWN IMMEDIATE
STARTUP

Run the utlrp.sql script to recompile all invalid PL/SQL packages
now instead of when the packages are accessed for the first time. This step is optional but recommended.

@ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

Run the following command to check the status of all the components after the upgrade:

SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
pc
pc
nls ora Oracle SELECT * FROM nls_database_parameters


SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE <ваша кодировка>;
SHUTDOWN;
STARTUP;
QUIT