Document ID: 28123.1
Subject: PERSONAL ORACLE7: COMMONLY ASKED QUESTIONS
Last Modified: 25 September 1996
Author: PSHOME
QUESTIONS:
---------
1. How do we import a table that already exists in the database ?
2. Is it alright to install Personal Oracle7 on top of the beta version
V7.0.16?
3. Is it possible to avoid using the database password altogether?
4. How do we carry over data from a V6 or V7.0 non-ASCII database
into Personal Oracle7 ?
5. Does Personal Oracle7 allow database links to other Oracle7
servers (over TCP/IP) ?
6. Why can't we see users created with double quotes using SQL*DBA,
e.g. "newuser" , from the Database Administration tools ?
7. How do we create a new database using Database Manager/Object
Manager ?
8. Does installing Personal Oracle7 without the PL/SQL option,
to reduce the memory requirements from 16MB RAM to 8MB RAM,
work?
9. Can we grant the SYSDBA and SYSOPER roles to ordinary database userids to
allow them to perform some DBA tasks, without their knowing
the 'INTERNAL' (database) password ?
10.Why shouldn't we use the above mechanism ?
11.Is it possible to start an already running database with a
different configuration of initialization parameters ?
12.How do we keep track of different sets of initialization
parameters for starting up the database(s) ?
13.How do we start up a newly 'handcreated' database using
Database Manager ?
14.Can we use the Password Manager to change the 'INTERNAL' (database)
password while the database is up and running ?
15.Is the standard database utility TKPROF missing from Personal Oracle7 ?
16.After increasing the size of a tablespace using Database Expander, why
does the new datafile appear in \ORAWIN\BIN ?
17.The install process failed during the step of 'Starting up the
starter database'. What needs to be done ?
18.After changing the database password to contain spaces, e.g. 'good one'
the database cannot be started, but Password Manager verifies the
password. Why?
19.Why doesn't setting the initialization parameter AUDIT_TRAIL=TRUE work
by generating an Audit trail ?
20.How does the user INTERNAL differ from any other database user ?
21.Why does the MONITOR function not work from SQL*DBA ?
ANSWERS:
-------
1. How do we import a table that already exists in the database ?
Answer: Select the check box 'Ignore error on creation' in Import
which is blank by default. This is specific to Windows. In general,
IGNORE=Y is the default.
If creation errors are ignored: even if the table emp exists,
importing the emp table would add the new rows to the existing
table.
The Windows Import fails if this check box isn't chosen and the
table already exists.
2. Is it alright to install Personal Oracle7 on top of the beta version
V7.0.16?
Answer: No, it is not supported to install Personal Oracle7 on top
of the beta V7.0.16. One needs to delete the \ORAWIN directory,
rename ORACLE.INI, and edit out the [Oracle] section in WIN.INI
completely before doing a fresh install of Personal Oracle7 and
any other Oracle software that was installed before.
3. Is it possible to avoid using the database password altogether?
Answer: Yes. This also means that one can connect as 'INTERNAL'
without a password, since the password for 'INTERNAL' and the
database password are synonymous.
In ORACLE.INI include the line:
DBA_AUTHORIZATION=BYPASS
Please note that this takes effect only on restarting each
tool. To ensure this, exit Windows completely once, and restart
everything.
4. How do we carry over data from a V6 or V7.0 non-ASCII database
into Personal Oracle7 ?
Answer: For non-ASCII charactersets (like EBCDIC), the Tables mode
and Users mode of Import don't work. But full database mode works
successfully. This can be done by SYSTEM by default. Ordinary
users like SCOTT need the IMP_FULL_DATABASE role granted to them in
order to be able to use the Full database mode of Import.
5. Does Personal Oracle7 allow database links to other Oracle7
servers (over TCP/IP) ?
Answer: This release (V7.1.4.1.0) does not have that capability.
Personal Oracle7 Enterprise Edition (V7.1.4.1.0C) allows database links.
6. Why can't we see users created with double quotes using SQL*DBA,
e.g. "newuser" , from the Database Administration tools ?
Answer: From SQL*DBA:
grant connect to "newuser" identified by newpass ;
works, but creates a userid wihtout the double quotes. The GUI
database administration tools do not strip away the double quotes,
so references to "newuser" within the GUI tools need to change to
references to newuser (without the double quotes).
7. How do we create a new database using Database Manager/Object
Manager ?
Answer: We don't. Database Manager, Object Manager, and the other
database administration tools are meant to help users accomplish
the day-to-day tasks. For an entirely new database to be created,
we still need to use the SQL*DBA tool. For details of the CREATE
DATABASE command, see the Oracle7 Server Administrator's Guide and
the SQL Language Reference manual, both available online under the
'Oracle7 Documention' icon.
A brief outline:
Create a new initialization parameter file called myinit.ora which
has correct settings for db_name (the name of the new database) and
control_files (the complete paths of the new control files) and
REMOTE_LOGIN_PASSWORDFILE=SHARED. The last is to avoid ORA-01991 while
creating the database, which complains about the password file being
shared. This is because the default setting of REMOTE_LOGIN_PASSWORDFILE
is EXCLUSIVE, which means that it can't be shared by two or more
databases.
In SQL*DBA:
connect internal/oracle
startup nomount pfile=myinit.ora
create database ... ;
8. Does installing Personal Oracle7 without the PL/SQL option,
to reduce the memory requirements from 16MB RAM to 8MB RAM,
work?
Answer: Yes, to a great extent. One of the problems is that full
database exports fail with 'ORA-00900: Invalid SQL', since the
export utility itself uses PL/SQL. This is fixed in a later
version (7.2). For this release one has to fall back on USER mode
exports, and export user by user.
9. Can I grant the SYSDBA and SYSOPER roles to ordinary database userids to
allow them to perform some DBA tasks, without their knowing
the 'INTERNAL' (database) password ?
Answer: Yes, this has to be done via SQL*DBA:
connect internal/oracle
grant SYSDBA to scott;
This will allow scott to startup/shutdown database WITHOUT
knowing the database password. In SQL*DBA:
connect scott/tiger as SYSDBA ;
startup ;
...
10.Why shouldn't we use the above mechanism ?
Answer: Since the database tools are designed for workgroups,
they don't support the 'CONNECT ... AS SYSDBA' / 'CONNECT ... AS SYSOPER'
method. There is a product called Server Manager for Windows that
supports it. So, the mechanism can be used in this release only via
SQL*DBA.
Also, there is a limitation with the Personal Oracle7 password
file. It gets updated for each such user granted SYSDBA/SYSOPER
role(s), and has a fixed limit on the number of such users. But
it does not check when the limit is reached. It is possible to
corrupt the password file and make all the database tools that need
the database password dysfucntional, by granting SYSDBA/SYSOPER to
too many users. In which case, see answer(3) to bypass the
corrupted password file.
Rule-of-thumb: Limit the granting of SYSDBA/SYSOPER to 5 or less
database userids. Monitor this by selecting from
V$PWFILE_USERS while connected as INTERNAL.
11.Is it possible to start an already running database with a
different configuration of initialization parameters ?
Answer: Database Manager 'lets' you choose a different
configuration and 'start' it up wihout asking for the database
password. But in actuality the database is not disturbed. Database
Manager fails to start the database up a second time, ignores the
failure, and then reports the status of the database: Running!
12.How do we keep track of different sets of initialization
parameters for starting up the database(s) ?
Answer: Personal Oracle7 provides two different tools for starting
up the database: the GUI Database Manager and the character/line
mode SQL*DBA.
SQL*DBA uses init.ora files to keep track of the initialization
parameters to use for starting up a database. It looks by default
at \ORAWIN\RDBMS71\INIT.ORA. Any other parameter fiel can be
specified as part of the STARTUP process in SQL*DBA:
connect internal/oracle
startup pfile=c:\mydir\myinit.ora
...
Database Manager uses configurations of initialization
parameters, of two types:
built in configurations - which are grouped in \ORAWIN\VSP10.INI
by default
user-defined configurations - which are grouped in \ORAWIN\VS10.INI
by default, but can be saved as
seperate files.
We need to be careful that the SQL*DBA init.ora files and
Database Manager configurations match exactly (parameter by parameter).
Any mismatch can lead to very different behaviour from the same
database, including failure to start up.
13.How do we start up a newly 'handcreated' database using
Database Manager ?
Answer: Presumably, this has a totally different set of control, data and
log files from the seed/sample database. This has to have been created
from SQL*DBA using a customized init.ora file. (See answer (7) above for a
brief outline of the process).
We go into Database Manager and create a new configuration by choosing
the Configuration button on the initial screen, choosing from the list
of configurations, choosing the Advanced button, and setting each parameter
set in the init.ora file for the new database, and saving this
configuration.
Then, choose this new configuration, and hit the Startup button in
Database Manager.
14.Can we use the Password Manager to change the 'INTERNAL' (database)
password while the database is up and running ?
Answer: That is possibe only if one of the initialization parameters used
for starting the database is set like this:
REMOTE_LOGIN_PASSWORDFILE=SHARED
By default, it's set to EXCLUSIVE, which makes it perform a timestamp
check for each connection of INTERNAL to ensure that the database was
started up AFTER the last editing of the password file.
If you change the password by mistake while the database is up and
running, all further connections as INTERNAL, explicit(via SQL*DBA/
SQL*PLUS) and implicit (database password for Database Manager,User
Manager,...) will fail.
You need to force a shutdown by exiting Windows. Then, before
restarting the database, make sure that the parameter given above is set
as shown.
Restarting with that setting will be successful, and from then on,
will allow you to change the database password using Password Manager even
while the database is up and running.
15.Is the standard database utility TKPROF missing from Personal Oracle7 ?
Answer: It's not missing. No icon is created for it during the installation.
It is installed as \ORAWIN\BIN\TKPROF.EXE. Create a program Item for it in
the program group of your choice using File->New option from the Windows
Program Manager pulldown menu.
16.After increasing the size of a tablespace using Database Expander, why
does the new datafile appear in \ORAWIN\BIN ?
Answer: The default working directory for Database Expander (as with all
other Database Administration tools) is \ORAWIN\BIN, and that's where
the new datafiles get created. In order to make sure that the new data
files are getting created in the same directory as the original data files,
e.g. \ORAWIN\DBS, change the working directory of Database Expander by
choosing that icon, and using the Windows Program Manager:File->Properties
option.
17.The install process failed during the step of 'Starting up the
starter database'. What needs to be done ?
Answer: This usually happens due to inadequate conventional memory.
At this point the sample database has already been completely built. We
just need to exit windows, start afresh, and use Database Manager to
start up the database.
18.After changing the database password to contain spaces, e.g. 'good one'
the database cannot be started, but Password Manager verifies the
password. Why?
Answer: The database actually does not allow any spaces in the password,
and this is enforced during the installation when a new password is being
set for the sample/seed database.
But, it is possible to later use Password Manager to change the password
to include spaces: e.g. 'good one' , 'bad one', 'all right'. The database
refuses to recognize this password. We need to use the Password Manager to
change the 'INTERNAL' (database) password to not contain spaces or any
special characters, e.g. 'oracle'.
19.Why doesn't setting the initialization parameter AUDIT_TRAIL=TRUE work
by generating an Audit trail ?
Answer: This is specific to Personal Oracle7.
In V6.0 the valid values for AUDIT_TRAIL are:
AUDIT_TRAIL=TRUE # auditing turned on,
and
AUDIT_TRAIL=FALSE # auditing turned off. These are both not valid for
Personal Oracle7.
From V7.0 onwards the valid values are:
NONE -- auditing disabled
OS -- audit records written to the Operating System Audit trail
(NOTE: This is NOT implemented in Personal Oracle7.)
DB -- auditing is done at the database level, and audit records are
written to SYS.AUD$ table.
In short, for Personal Oracle7:
AUDIT_TRAIL=NONE for turning audit off
AUDIT_TRAIL=DB for turing audit on.
Other settings of AUDIT_TRAIL are not supported.
20.How does the user INTERNAL differ from any other database user ?
Answer: INTERNAL is not recorded in the data dictionary tables as being a
database user. It is a special userid that's used to perform certain
database operations before the database is up and running (e.g. starting
it up!).So, the validation is not doen at all, or done against a (hidden)
password file \ORAWIN\RDBMS71\PASSWORD.ORA.
It is therefore not possible to change the password for INTERNAL using
the 'ALTER USER ...' command - it comaplains that this user doesn't exist.
The special Password Manager utility needs to be used for this purpose.
21.Why does the MONITOR function not work from SQL*DBA ?
Answer: SQL*DBA for Windows (both 16- and 32-bit) is restricted to line
mode. In order to monitor the database, one needs to use a different
product: Server Manager for Windows.