Chapter 8 – Managing Tablespaces

–          If the database has multiple tablespaces you can separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline. You can store different datafiles of different tablespaces on different disk drives to reduce I/O contention and backup individual tablespaces

–          Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles

–          SYSAUX tablespace, which acts as an auxiliary tablespace to the SYSTEM tablespace, is also always created when you create a database. It contains information about and the schemas used by various Oracle products and features, so that those products do not require their own tablespaces.

–          Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps. It provides enhanced performance, and user reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks

–          Create a locally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT vlause of the CREATE TABLESPACE statement. This is the default for new permanent tbalespaces, but you must specify the EXTENT MANAGEMENET LOCAL clause if you want to specify either the AUTOALLOCATE clause or the UNIFORM clause. You can have the database manage extents for you automatically with the AUTOALLOCATE clause (the default) or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM)

–          AUTOALLOCATE means that the extent size are managed by Oracle. Oracle will choose the next optimal size for the extents starting with 64KB. As the segments grow and more extents are needed, oracle will start allocating larger and larger sizes ranging from 1Mb to eventually 64Mb extents.

–          UNIFORM T option tells the database to allocate and deallocate extents in the tablespace with the same unvarying size that you can specify or let extents default to 1Mb.

–          In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment spaceL AUTOMATIC and MANUAL. Manual segment space management uses linked lists called “freelists” to manage free space in the segment, while automatic segment management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.

–          To create a  bigfile tablespace, run CREATE BIGFILE TABLESPACE command. Oracle automatically creates a locally managed tablespace with automatic segment space management.

–          If default tablespace type was set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE SMALLFILE TABLESPACE statement to override the default tablespace that you are creating

–          Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown.

–          You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view

–          When you create a temporary table, its rows are stored in your default temporary tablespace

–          Use CREATE BIGFILE TEMPORARY TABLESPACE statement to create a single tempfile tablespace

–          To rename a tempfile, you take the tempfile offline, use operating system commands to rename or relocate the tempfile, and then use the ALTER DATABASE RENAME FILE command to update the database controlfiles

–          Using temporary tablespace group, rather than a single remporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions.

–          You create at tablespace group implicitily when you include the TABLESPACE GORUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist.

ALTER TABLESPACE tmp_tbs TABLESPACE GROUP group2;

–          The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces

–          To assign tablespace group as a default temporary tablespace, use the following statement:

ALTER DATABASE db_name DEFAULT TEMPORARAY TABLESPACE group2;

–          You can create tablespaces with block sizes different from the standard database block size, which is specified by the DB_BLOCK_SIZE parameter.

–          Use the BLOCKSIZE clause of the CREATE TABLESPACE statement to create a tablespace with a block size different from the database standard block size. In order for the BLOCKSIZE clause to succeed, you must have already set the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter. Further, and the integer you specify in the BLOCKSIZE clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter.

CREATE TABLESPACE tbs DATAFILE ‘/datafile/’ SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 8K;

–          You can’t take SYSTEM, UNDO and TEMPORARY tablespaces offline

–          When you take tablespace to OFFLINE usint IMMEDIATE clause, media recovery for the tablespace is required before the tablespace can be brought online.

–          If you must take tablespace offline, use the NORMAL clause (the default) if possible. This setting guarantees that the tablespace will not require recovery to come back online, even if after incomplete recovery you reset the redo log sequence using an ALTER DATABASE OPEN RESETLOGS statement .

–          Specify TEMPORARY only when you cannot take tablespace offline normally.  In this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. Specify IMMEDIATE only after trying both the normal and temporary settings

–          You can drop items, such as tables or indexes, from a read-only tablespace, but you cannot create or alter objects in a read-only tablespace.

–          Before you can make a tablespace read-only, the tablespace must be online, can’t be active undo tablespace or SYSTEM tablespace

–          When there’re transactions waiting to commit, the ALTER TABLESPACE .. READ ONLY statement does not return immediately. It waits for all transactions started before you issued the ALTER TABLESPACE statement to either commit or rollback.

–          When substantial portions of a very large database are stored in read-only tablespaces that are located on slow-access devices or hierarchical storage you should consider setting the READ_ONLY_OPEN_DELAYED parameter to TRUE. This speed certain operations, primary opening the databsae, by causing datafiles in readonly tablespaces to be accessed for the first time only when an attempt is made to read data stored within them

–          You can’t rename the tablespace if

• The tablespace is SYSTEM or SYSAUX tablespace
• If any datafile in the tablespacea is offline or if the tablespace is offline

–          Use the CASCADE CONSTRAINTS clause to drop all referential integrity constraints from tables outside the tablespace that refer to primary and unique keys of tables inside the tablespace

–          Use INCLUDING CONTENTS AND DATAFILES to delete physical datafiles from OS. It an OS error prevents the deletion of a file, the DROP TABLESPACE statement still succeeds, but a message describing the error is written to the alert log

–          If SYSAUX tablesapce becomes unavailable, core database functionality will remain operational.

–          You can monitor the occupants of the SYSAUX tablespace using the V$SYSAUX_OCCUPANTS view.

–          Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the datafiles containing all of the actual data are just copied to the destination location, an you use an export/import utility to transfer only the metadata of the tablespace objects to the new database

–          Starting with Oracle Database 10g, you can transport tablespaces across platforms.

–          You can query the V$TRANSPORTABLE_PLATFORM view to see the platfors that are supported, and to determine each platform’s endian format(byte ordering)

–          If the source platform and the target platform are of different endianness, then an additional step must be done on either the source or target platform to convert the tablespace being transported to the target format.  If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.

To transport tablespace between database follow this procedure:

• Check the endian format of both platforms by querying the V$TRANSPORTABLE_PLATFORM view
• Pick a self containted set of tablespaces
• Generate a transportable tablespace set. It consists of datafiles for the set of tablespaces being transported and an export file containing structural information (metadata) for the set of tablespaces.
• Transport the tablespace set
• Import the tablespace set

 

–          There may be logical or physical dependiencies between objects in the transportable set and those outside of the set. You can only transport a set of tablespaces that is self-contained. In this context “self-containted” means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. To check whether the tablespace is self-containted, you can invoke the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS. After invoking this procedure, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view.

–          To convert tablespaces from one endian format to another, login to RMAN and run the following command:

RMAN> CONVERT TABLESPACE sales_1, sales_2 TO PLATFORM ‘Microsoft Windows NT’ FORMAT ‘/tmp/%U’;

–          Then transport both the datgafiles and the export file of the tablespaces to a place that is accessible to the target database

–          Use RMAN to convert the necessary transported datafiles to the endian format of the destination host format and deposit the results in /orahome/dbs as shown in the following examples

RMAN>CONVERT DATAFILE

‘/h1/finance/tbs_21.f’,

‘/h1/finance/tbs_22.f’

TO PLATFORM =”Solaris[tm] OE (32-bit)”

FROM PLATFORM=”HP Tru64 UNIX”

DB_FILE_NAME_CONVERT=’/h1/finance/work/try/”,’/h1/finance/dbs/tru’

PARALLELISM=5;

Chapter 7 – Managing archived redo logs

–          The LOG_ARCHIVE_MAX_PROCESSES parameter specifies the number of ARCn processes that the database initially invokes. The default is two processes. You can specify this parameter up to ten processes to be started at instance startup. It is dynamic parameter and can be changed with ALTER SYSTEM command. The database must be mounted, but not opened.

–          If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST parameter. If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n parameters) or to archive to a primary and secondary destinations (LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST)

–          You specify the location for LOG_ARCHIVE_DEST_n using the keywords LOCATION (for a local file system) and SERVICE (remote archival through Oracle Net service name)

LOG_ARCHIVE_DEST_1=’LOCATION=/disk1/archive’;

LOG_ARCHIVE_DEST_2=’SERVICE=standby’;

–          The optional parameter LOG_ARCHIVE_MIN_SUCCEED_DEST=n determines the minimum number of destinations to which the database must successfully archive a redo log group before it can reuse online log files. the default value is 1. Valid values for n are 1 to 2 if you are using dupliexing, or 1 to 10 if you are multiplexing.

–          MANDATORY option specifies that the transmission of the redo data to the destination must succeed before the local online redo log file can be made available for reuse. The default is OPTIONAL.

–          REOPEN option specifies the minimum number of seconds before the archived process (ARC) or the LGWR should try again to access a previously failed destination. Default is 300 seconds.

–          VERIFY option indicates if an archived (ARC) process should scan and verify the correctness of the contents of the completed archived redo log file, either local or remote, after successfully completing the archival operation. By default, archived redo log file are not verified.

–          When you specify REOPEN for an OPTIONAL destination, the database can overwrite online logs if ther is an error. If you specify REOPEN for a MANDATORY destination, the database stalls the production database when it cannot successfully archive.

–          Use LOG_ARCHIVE_TRACE parameter to specify a trace level

–          To get information about archived redo logs use the following views:

V$ARCHIVED_LOG

V$ARCHIVE_DEST

V$ARCHIVE_PROCESSES

V$BACKUP_REDOLOG

V$LOG

V$LOG_HISTORY

Chapter 5,6 – Managing Control Files, Redo Log files

– Every oracle database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

–          The database name

–          Names and locations of associated datafiles and redo log files

–          The timestamp of the database creation

–          The current log sequence number

–          Checkpoint information

-The initial control files of an Oracle Database are created when you issue the CREATE DATABASE statement.

– The CREATE CONTROLFILE statement can potentially damage specified datafiles and redo log files. omitting a filename can cause loss of the data in that file, or loss of access to the entire database.

If a datafile exists in the data dictionary but not in the new controlfile, the database creates a placeholder entry in the controlfile under the name MISSINGnnnnn, where nnnn is the file number in decimal. MISSINGnnnn is flagged in the controlfile as being offline and requiring media recovery. If the actual datafile correspoinding to MISSINGnnnn is read-only or offline normal, then you can make the datafile accessible by renaming MISSINGnnnn to the name of the actual file. If MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then you cannot use the rename operation to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, you must drop the tablespace containing the datafile

Use V$CONTROLFILE_RECORD_SECTION view to display information about controlfile record sections

 Managing the Redo Log

–          The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

–          The redo log for each database instance is also referred to as a redo thread.

–          Redo log files are filled with redo records. A redo record, also called a redo entry is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

–          Redo entreis record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks

–          Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

–          Redo records can also be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes

–          The database requires a minimum of two files to guarantee that one is always available for writing while the other is being archived.

–          The redo log file that LGWR is actively writing to is called the current redo log file. Redo log files that are required for instance recovery are called active redo log files. It means that the content has not been flushed to datafiles from database buffer cache, because CKPT has not been started.  Redo log files that are no longer required for instance recovery are called inactive redo log files

–          Log switch is the point at which the database stops writing to one redo log file and begins writing to another.

–           Oracle Database assigns each redo log file a new log sequence number bevery time a log switch occurs. During crash, instance or media recovery the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files

–          Datafiles should be placed on different disks from redo log files to reduce contention in writing data blocks and redo records

–          All members of the same multiplexed redo log group must be the same size. Members of different groups can have different sizes. However, there’s no advantage in varying file size between groups.  

–          The minimum size permitted for a redo log file is 4MB

–          If messages in alert.log file indicate that LGWR frequently has to wait for a group because a checkpoint has not completed or a group has not been archived, add groups.

–          The MAXLOGFILES parameter used in the CREATE DATABASE statement determines the maximum number of groups of redo log files for each database.

–          The MAXLOGMEMBERS parameter used in the CREATE DATABASE statement determines the maximum number of members for each group.

–          The ARCHIVE_LAG_TARGET parameter can be set specifically to force logs to be switched and archived. If it is set to a very low value, there can be a negative impact on performance. This can force frequent log switches.

–          To create log groups use one of the following commands:

• ALTER DATABASE ADD LOGFILE (‘/home/oracle/…./redo1c.log’,’/home/oracle2/redo2c.log’) size 10M
• ALTER DATABASE ADD LOGFILE GROUP 5  (‘file1.log’,’file2.log’) size 10M;

–          To create a new member for a redo log group use one of these commands:

• ALTER DATABASE ADD LOGFILE MEMBER ‘/file_name.log’ TO GROUP 5;
• ALTER DATABASE ADD LOGFILE MEMBER ‘/file_name.log’ TO (‘/file1.log’,’file2.log’);

–          An instance requires at least two groups of redo log files

–          If you can drop a redo log group only if it is inactive. If you need to drop the current group, first force a log switch to occur and make sure it is archived before dropping it. Check V$LOG view to see whether it has been archived or not. Drop the redo log group using ALTER DATABASE DROP LOGFILE GROUP 4; command  

–          To drop specific inactive redo log members use ALTER DATABASE DROP LOGFILE MEMBER ‘/home/….’; command

–          When redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure.

–          A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABSAE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database. ALTER DATABSAE CLEAR LOGFILE GROUP 3;

–          If the corrupted redo log file has not been archived, use the UNARCHIVED keyword in the statement

–          Use V$LOG, V$LOGFILE and V$LOG_HISTORY views for more information

Chapter 4 – Managing Oracle Database Processes

–          Oracle database creates server processes to handle the requests of user processes connected to an instance.

–          Your database is always enabled to allow dedicated server processes, but you must specifically configure and enable shared server by setting one or more initialization parameters

–          Shared server can be configured for session multiplexing, which combines multiple sessions for transmission over a single network connection in order to conserve the operating system’s resources.

–          DBW – The database writer writes modified blocks from the database buffer cache to the datafiles. Oracle database allows a maximum of 20 database writer processes. The DB_WRITER_PROCESSES initialization parameter specifies the number of DBW processes.

–          LGWR – the log writer process writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the SGA. LGWR writes the redo log entries sequentially into a redo log file. If the database has a multiplexed redo log, then LGWR writes the redo log entries to a group of redo log files.

–          CKPT – At specific times, all modified database buffers in the system global area are written to the datafiles by DBW. This event is called a checkpoint. The checkpoint process is responsible for signaling DBW at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.

–          SMON – the system monitor performs recovery when a failed instance starts up again. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during system failure and instance recovery because of file-read or offline erros.

–          PMON – the process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using.

–          RECO – the recoverer process is used to resolve distributed transactions that are pending because of a network or system failure in a distributed database.

The following views help you to monitor the Oracle instance

V$PROCESS – Contains information about the currently active processes

V$LOCKED_OBJECT – Lists all locks acquired by every transaction on the system

V$SESSION – lists session information for each current session

V$SESS_IO – contains I/O statistics for each user session

V$SESSION_WAIT – lists the resources or events for which active sessions are waiting

V$SYSSTAT – Contains session statistics

V$SQLAREA – Contains statistics about shared SQL area and contains one row for each SQL string

Chapter 3 – Starting Up and Shutting Down

–          If you know that media recovery is required, you can start an instance, mount a database to the instance, and have the recovery process automatically start by using the STARTUP command with the RECOVER clause: STARTUP OPEN RECOVER

–          When SHUTDOWN IMMEDIATE command used, any uncommitted transactions are rolled back. If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name

–          Occasionally you might want to put a database in a state that allows only DBA transactions. Such a state is referred to as s quiesced state. Without ability to quiesce the database, you would need to shut down the database and reopen it in restricted mode. Use ALTER SYSTEM QUIESCE RESTRICTED; command. Moreover, refer to the Tom Kyte’s answer on this issue from the following link:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:902149038276

–          To restore the database to normal operation use ALTER SYSTEM UNQUIESCE;

Chapter 2 – Creating an Oracle Database

Before starting this chapter, I’d like to direct you to my step by step guide on “Create database manually”

–          If you omit to assign a password at CREATE DATABASE command, the SYS and SYSOPER users are assigned the default passwords change_on_install and manager, respectively. Oracle strongly recommends that you specify these clauses, even though they are optional

–          Specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement to create a locally managed SYSTEM tablespace. If you do not specify the EXTENT MANAGEMENT LOCAL clause, by default the database creates a dictionary managed SYSTEM tablespace.

–          The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. BECAUSE IT IS THE DEFAULT TABLESPACE FOR MANY Oracle Database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database and that you must maintain.

–          You can’t drop or rename the SYSAUX tablespace

–          The SYSAUX tablespace needs to be at least 240MB at the time of database creation

–          The maximum number of datafiles in an Oracle Databsae is limited (usually to 64K files – 65536 files)

–          The maximum number of blocks in a datafile is 4GB (4 million blocks).  From the following link you can find a nice article written about it http://www.dba-oracle.com/t_bigfile_tablespace_tips.htm

–          Bigfile tablespace can contain only one file, but that file can have up to 4G blocks

–          A smallfile tablespace can contain up to 1022 files with up to 4M block each

–          To dynamically change the default tablespace type after database creation, use alter DATABASE SET DEFAULT BIGFILE TABLESPACE;  command

–          It is recommended that you use a server parameter file

–          DB_NAME must be set to a text string of no more than eight characters. During database creation, the name provided for DB_NAME is recorded in the datafiles, redo log files, and contro file of the database.

–          You can’t change the block size after database creation except by recreating the database.

–          The SGA_MAX_SIZE initialization parameter specifies the maximum size of the System Global Area for the lifetime of the instance. You can dynamically alter the initialization parameters affecting the size of the buffer caches, shared pool, large pool, Java pool and streams pool.

–          You enable automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value. In addition, you must set the value of all automatically sized SGA components to a zero to enable full automatic tuning of these components. When SGA_TARGET is not set, the automatic shared memory management feature is not enabled.

–           When you increase the size of a manually sized component, extra memory is taken away from one or more automatically sized components. And when you decrease the size of a manually sized component, the memory that is released is given to the automatically sized components

–           The COMPATIBLE initialization parameter enables or disables the use of features in the databsae that affect file format on disk. For example, if you create an Oracle Database 10g database, but specify  COMPATIBLE=9.2.0.2 in the initialization parameter file, then features that requires 10.0 compatibility will generate an error if you try to use them. Such a database is said to be at the 9.2.0.2. compatibility level.

–          To limit the number of users created in a database, set the LICENSE_MAX_USERS initialization parameter in the database initialization parameter file

–          The COMMENT clause at ALTER SYSTEM command lets you associate a text string with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.

–          To view initialization file’s parameter use SHOW PARAMETER command, CREATE PFILE command or V$PARAMETER view (this view displays the values of parameters currently in effect)

–          V$SPPARAMETER view displays the current contents of the server parameter file. The view returns FALSE values in the ISSPECIFIED column if a server parameter file is not being used by the instance

–          Check your current release number as follows

SQL> COL PRODUCT FORMAT A35

SQL> COL VERSION FORMAT A15

SQL> COL STATUS FORMAT A15

SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                             VERSION         STATUS

———————————– ————— —————

NLSRTL                              10.2.0.1.0      Production

Oracle Database 10g Enterprise Edit 10.2.0.1.0      Production

PL/SQL                              10.2.0.1.0      Production

TNS for Linux:                      10.2.0.1.0      Production

–          Create at least one additional administrative user and grant to that user an appropriate administrative role to use when performing daily administrative tasks. Do not use SYS and SYSTEM for these purposes.

–          When you create an Oracle Database, the users SYS and SYSTEM are automatically created and granted the DBA role

–          No one should create any tables in the schemas of users SYS and SYSTEM

–          A predefined DBA role is automatically created with every Oracle Database installation

–          When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For SYSDBA this schema is SYS, for SYSOPER the schema is PUBLIC

–           If you issue the ALTER USER statement to change the password for SYS after connecting to the database, both the password stored in the data dictionary and the password stored in the password file are updated. You can’t change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so

–          Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER system privileges for a database

According to the Oracle documentation, when you connect with SYSDBA or SYSOPER privilege, you connect with a default schema, not with the schema that is generally associated with your username. See the following example:

[sourcecode language="css"]
SQL> create user usr1 identified by test;

User created.

SQL> grant sysdba to usr1;

Grant succeeded.

SQL> conn usr1/test
ERROR:
ORA-01045: user USR1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant dba to usr1;

Grant succeeded.

SQL> conn usr1/test
Connected.
SQL> create table table1 (id number);

Table created.

SQL> conn usr1/test as sysdba
Connected.

SQL> create table table2 (id number);

Table created.

SQL> col owner format a35
SQL> col object_name format a35
SQL> select owner, object_name from dba_objects where object_name like 'TABLE_';

OWNER OBJECT_NAME
----------------------------------- -----------------------------------
SYS TABLE2
USR1 TABLE1

SQL> show user
USER is "SYS"
SQL>

SQL> drop user usr1 cascade;

User dropped.

SQL>

[/sourcecode]

As you see, although you've granted SYSDBA privilege, the user can't connect to the database and as you connected with SYSDBA privilege, the table you've created is not created under the schema of the connected user, but is created under the SYS schema, because you're acting as SYS user by connecting with SYSDBA privilege