March 2024
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031

Categories

March 2024
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031

oracle databases server

oracle databases server

create database TEST
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘C:\oraclexe\app\oracle\oradata\TEST\REDO01.LOG’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘C:\oraclexe\app\oracle\oradata\TEST\REDO02.LOG’ SIZE 50M BLOCKSIZE 512
DATAFILE’C:\oraclexe\app\oracle\oradata\TEST\SYSTEM.DBF’ size 100m autoextend on
sysaux datafile ‘C:\oraclexe\app\oracle\oradata\TEST\SYSAUX.DBF’ size 100m autoextend on
undo tablespace undotbs1 datafile ‘C:\oraclexe\app\oracle\oradata\TEST\UNDOTBS1.DBF’ size 100m autoextend on
CHARACTER SET AL32UTF8
;

CREATE DATABASE axisdevdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
GROUP 1 (‘/u01/app/oracle/oradata/axisdevdb/Disk1/redo01_a.log’) SIZE 100M
GROUP 2 (‘/u01/app/oracle/oradata/axisdevdb/Disk1/redo02_a.log’) SIZE 100M
GROUP 3 (‘/u01/app/oracle/oradata/axisdevdb/Disk1/redo03_a.log’) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/u01/app/oracle/oradata/axisdevdb/Disk1/system01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/u01/app/oracle/oradata/axisdevdb/Disk1/sysaux01.dbf’ SIZE 325M REUSE
SIZE 20M REUSE
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Log into the database server as a user belonging to ‘dba’ [unix ] or ‘ora_dba’ [windows ] group , typically ‘oracle’, or an administrator on your windos machine. You are able to log into Oracle as SYS user, and change the SYSTEM password by doing the following:

$ sqlplus “/ as sysdba”
SQL*Plus: Release 9.2.0.1.0 – Production on Mon Apr 5 15:32:09 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production

SQL> show user

USER is “SYS”

SQL> passw system
Changing password for system
New password:
Retype new password:
Password changed
SQL> quit

Next, we need to change the password of SYS:

$ sqlplus “/ as system”
SQL*Plus: Release 9.2.0.1.0 – Production on Mon Apr 5 15:36:45 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
Enter user-name: system
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production

SQL> passw sys
Changing password for sys
New password:
Retype new password:
Password changed
SQL> quit
You should now be able to log on the SYS and SYSTEM users, with the passwords you just typed in.

Method 2: Creating pwd file (Tested on Windows Oracle 8.1.7)

Stop the Oracle service of the instance you want to change the passwords of.
Find the PWD###.ora file for this instance, this is usuallly located atC:\oracle\ora81\database\, where ### is the SID of your database.
rename the PWD###.ora file to PWD###.ora.bak for obvious safety reasons.
Create a new pwd file by issuing the command:
orapwd file=C:\oracle\ora81\database\PWD###.ora password=XXXXX
where ### is the SID and XXXXX is the password you would like to use for the SYS and INTERNAL accounts.
Start the Oracle service for the instance you just fixed. You should be able to get in with the SYS user and change other passwords from there.

 

 

 

Oracle default user and password

sqlplus /nolog
conn /as sysdba
alter user system identified by manager

OK, you can now use the Oracle database service normally. Specific instructions and points of attention are given in the next section.

Extra operation
1, modify the instance name

select instance from v$thread;

echo $ORACLE_SID

First, create a user
create user test identified by password
alter user test identified by password ;
Second, the authorization role
grant connect, resource to test;
revoke connect, resource from test;
Second, delete the user
drop user test;
drop user test cascade;
Fourth, create / authorize / delete the role
create role testRole;
grant select on class to testRole;
drop role testRole;

test:
grant resource to nova; //
Role related

Fifth, create wm_concat function on 12C
11g2 and 12C have abandoned the wm_concat function

Create wm_concat function on 12C Oracle default user name and password cheat sheet

create user ums_dev identified by ums_dev?
grant session, connect, resource to ums_dev;
ALTER USER ums_dev ACCOUNT UNLOCK;
alter user ums_dev identified by ums_dev;
grant unlimited tablespace to ums_dev;
alter table table_user add (overdate TIMESTAMP(6));

View Oracle Role Users and Permissions

select user_id, username, DEFAULT_TABLESPACE, ACCOUNT_STATUS,PROFILE from dba_users;

select username,default_tablespace from user_users;

select * from user_role_privs;

select * from user_sys_privs

select * from user_tab_privs

Check the table related information

select sum(bytes)/(1024*1024) tablesize from user_segments where segment_name=’ZW_YINGYEZ’;

select index_name,index_type,table_name from user_indexes order by table_name

select * from user_ind_columns where table_name=’CB_CHAOBIAOSJ201004′

select sum(bytes)/(1024*1024) as indexsize from user_segments
where segment_name=upper(‘AS_MENUINFO’)

select * from v$version

Import and Export

exp dbserver/dbserver1234@ORCL file=/opt/dbserver.dmp owner=dbserver

imp dbserver/dbserver1234@XE file=c:\orabackup\full.dmp log=c:\orabackup\imp.log full=y

Export

exp dbserver/dbserver1234@ORCL file=dbserver.dmp log=dbserver.log owner=ums rows=n

exp dbserver/dbserver1234 jdbc:oracle:thin:@10.10.73.206:1521:umpay file=dbserver.dmp log=dbserver.log owner=ums rows=n

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>